dynamic range selection 2

[bobbythefish]

I am trying to copy/paste special (to overwrite embedded conditional) a range of cell in one worksheet, based upon a variable in a sheet from another book. Any suggestions?

thanks
bobby

 

[Zathras]

It can be done with VBA, but you need to give some more details. What and where is the variable? Where are you copying from? Where are you copying to? One cell? More than one? If more than one, how are you going to specify the source rows and columns and the destination range with only one variable?

 

[bobbythefish]

Thanks to Zathras for a prompt reply. The full scenario is as follows.
A variable (account number) is enter via user input in sheet one of workbook A. This variable retrieves a range of data via lookup from Sheet 1 of workbook B, and fills in data in sheet 1 workbook A (for example, Column A rows 1 thru 26).
Then in sheet 1 workbook A, a new range of cells (say Column B, rows 1 thru 26) fills in data created by a pre-embedded formula.

Now in workbook B sheet 2, a corresponding range, also with embedded formulae, mimcs the data from Sheet 2 Workbook A.
This data now needs to be copied and special pasted as values, but ONLY THIS RANGE, with was selected based upon a lookup from the variable which was origanlly user-entered.
Is this possible? ( I believe it can be done if there was some was to parse out the row and column coordinates and replace them as variables)
thanks for any clues
Bobby

 

[Zathras]

I'm a little lost here. When you say ...This variable retrieves a range of data via lookup... what exactly is happening? Do you have VLOOKUP formulas in column B of workbook A? Are you using a macro? Do you have on VLOOKUP formula that finds a string with a range name? Cell coordinates? Are you using INDIRECT? There are so many possibilities from the little you have posted.

It would help if you could give an example of the table in workbook B that you are looking up in. For example, I can imagine a table that looks like this:

   Account    Range
   -------   -------
     123     'A1:A26
     124     'B1:B37
     125     'C1:C18

or a table that looks like this

   Account   Range Name
   -------   ----------
     123     'XYZ_DATA
     124     'ABC_DATA
     125     'FOO_DATA

I need more to go on.

 

[bobbythefish]

okay --thanks again--I'll try to be more specific.

Consider 2 workbooks, A and B

Workbook B has 2 sheets full of Table data with each column heading an Account number.

Workbook A Request the user to input 1 account number.
When the user responds, an Hlookup is done and 26 consecutive rows of data are retrieved from the Column in WB B. (example: what stocks are held in account ABC? answer
1 IBM
2 CAG
3 DIS
. ...
26 ... as retrieved from sheet 1 of WB B.

Sheet 1 of WB A then automatically looks up current mark price of each stock (via Macro of live data plug in).

the trick now is to place (and freeze) the price in the column next to each stock in Sheet 2 of WB B. (note Sheet 2 is a virtual clone of sheet 1-that is a column headed by and account number underneath follows upto 26 stock symbols--but there is an exception... the column immediately to the right of each stock symbol holds a formula as follows (word algorithm): If (account number in workbook A = Account number of adjacent column heading, current cell = cell 'x' of workbook A (where cell X is the current price of each stock) This is my method to dynamically write new data to an otherwise idle workbook.) The trick now becomes to overwrite the formula in the select range of cells and paste with only the value, because once the data is read in workbook A, the user will move enter a new Account number, and the Hlookup process will select a new range...
Complicated yes, but it works great except for this one last problem-- Note, my VB skills are limited, but I used to be a programmer of non object orient languages)
thanks again
bobby

 

[Zathras]

So... Workbook B sheet 1 looks like this?

      A         B         C
1   1234      1235      1236
2   IBM       MSFT      C
3   MRK       F         PFI
4   INTC      HUF       APC

and Workbook B sheet 2 looks like this?

      A         B         C        D
1   1234      LAST      1235     LAST
2   IBM       40.51     MSFT     80.55
3   MRK       50.24     F        10.23
4   INTC      18.11     HUF      9.75

I'm still not understanding. What formulas are over-written? How do the formulas get re-established as formulas again to be over-written again later?

I don't know enough yet to really understand, but it is beginning to sound like formulas aren't needed or wanted. It should all be done with macro code:

1. User types an account number somewhere in book A and presses enter. This fires the Worksheet_Change event which causes:
2. List of stocks from book B sheet1 is copied into book A, and
3. the macro is called to update the prices. Then,
4. Updated prices are pasted in book B sheet2 over-writing whatever prices were there before (for the account).

If that is correct, I can give you simple macros to do that. It would help if I knew what cell the account number was typed into, and which column you want the ticker list placed into (and which row is the top row). Also the real names of the workbooks and sheets would be useful.

 

[bobbythefish]

Sounds great, thanks---actually you do grasp what I'm doing, however I simplified it for example-sake.
in Workbook 'BF-options' user enter account number in cell E1. (actually the account # suffix is typed in and gets concatonated with prefix ultimately stored in cell Y42).

HLOOKUP from sheet 'holdings' of book 'ALL-Options' find column Headed by value matching BF-options cell Y42. Then cells B7 thru B32 in book BF-options are filled from lookups. Next cells H7 thru H32 formulaically put in a suggested "strike" price value as determined by current stock price and a table of values that are incremented by 2.5 dollar segments. (for example if IBM [in cell B7, say]is currently trading at 77.93, cell H7 would suggest a strike price of 85, and so on...)
These are the values I wish to capture in sheet "stikes" of book "All-options" in cells column ?+1 rows 5 thru 30.

Is it possible dto discuss this over the phone? (I will make an appropriate donation either way.) If so, my direct number is 212-284-5747 but if it is long-distance my 800 number is 888-304-5801 ask for bob--thanks

 

[Zathras]

Ok. I think we're close. But first, I have new formulas to put in your BF-Options.xls workbook.

I have made the assumption that your account "numbers" in ALL-Options.xls are really text strings. Either entered with a leading apostrophe, or have mixed alpha/numeric characters. This is based on your statement that cell Y42 contains the result of a concatenation.

Set up BF-Options.xls this way:

F1: 'Column:'
G1: =MATCH(Y42,'[ALL-Options.xls]holdings'!$A$1:$DZ$1,0)
B7: =INDEX('[ALL-Options.xls]holdings'!$A$1:$DZ$32,CELL("row",B7),$G$1)

Now copy the formula in B7 to B8 thru B32.

With that set-up you should still see the tickers appear in column B when you type a new suffix in E1.

Do whatever it is that you do to get the prices in column H. Then run this macro:

Option Explicit
Const BOOK_BFOPTIONS = "BF-Options.xls"
Const BOOK_ALL = "ALL-Options.xls"
Const SHEET_BFOPTIONS = "Sheet1"
Const SHEET_HOLDINGS = "holdings"
Const SHEET_STRIKES = "strikes"
Const ROW_ACCOUNTLIST = 1
Const ROW_FIRSTTICKER = 7
Const ROW_MAXROWS = 33
Const ROW_POSTSTRIKES = 5
Const COL_TICKERS = 2 'Column "B"
Const COL_PRICES = 8 'Column "H"
Const ROW_COPYFROM = 7
Const ROW_COPYTO = 5
Const ROWS_TO_CLEAR = 26
Const COLS_TO_CLEAR = 2
Const CELL_ACCOUNTID = "Y42"
Const CELLS_LOOKUP = "$A1:$IV1"

Sub GrabStrikePrices()
' Routine to copy holdings and prices
' from this sheet to BOOK_ALL, SHEET_STRIKES
' Copy ticker symbols from COL_TICKERS
' and strike prices from COL_PRICES
Dim rngLookup As Range
Dim rngFound As Range
Dim rngTickers As Range
Dim rngPrices As Range
Dim rngCopyFrom As Range
Dim rngCopyTo As Range
Dim rngToClear As Range
Dim sAccountID As String
Dim nLastRow As Integer
Dim nCopyToColumn As Integer
Dim nAccountColumn As Integer
Dim nRowsToClear As Integer

Windows(BOOK_BFOPTIONS).Activate
Worksheets(SHEET_BFOPTIONS).Activate

' Find last row with a ticker
Set rngLookup = Range(Cells(ROW_COPYFROM, COL_TICKERS), _
                       Cells(ROW_MAXROWS, COL_TICKERS))
Set rngFound = rngLookup.Find(What:="0", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext)
nLastRow = ROW_MAXROWS
On Error Resume Next
nLastRow = rngFound.Row - 1
On Error GoTo 0

' Set Tickers range
Set rngTickers = Range(Cells(ROW_COPYFROM, COL_TICKERS), _
                       Cells(nLastRow, COL_TICKERS))
' Set Prices range
Set rngPrices = Range(Cells(ROW_COPYFROM, COL_PRICES), _
                       Cells(nLastRow, COL_PRICES))
' Set copy-from range and copy to clipboard
Set rngCopyFrom = Union(rngTickers, rngPrices)
Application.CutCopyMode = False
rngCopyFrom.Copy
' Remember some items from this book before leaving...
sAccountID = Range(CELL_ACCOUNTID).Value
nRowsToClear = ROWS_TO_CLEAR - rngCopyFrom.Rows.Count

' Find where to copy to -- do it quietly
Application.ScreenUpdating = False
Windows(BOOK_ALL).Activate
Worksheets(SHEET_STRIKES).Activate
Set rngLookup = Range(CELLS_LOOKUP)
Set rngFound = rngLookup.Find(What:=sAccountID, LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext)
nAccountColumn = -1
On Error Resume Next
nAccountColumn = rngFound.Column
On Error GoTo 0
If nAccountColumn = -1 Then
  ' Not foundWindows(BOOK_BFOPTIONS).Activate
  Windows(BOOK_BFOPTIONS).Activate
  Worksheets(SHEET_BFOPTIONS).Activate
  Application.ScreenUpdating = True
  Application.CutCopyMode = False
  MsgBox "Couldn''t find account " + sAccountID _
                    + " in sheet " + SHEET_STRIKES + " -- job cancelled"
  Exit Sub
End If

' Paste new data
Set rngCopyTo = Cells(ROW_COPYTO, nAccountColumn)
ActiveSheet.Paste rngCopyTo
Application.CutCopyMode = False

'Clear unused rows
If nRowsToClear > 0 Then
  Set rngToClear = Range(rngCopyTo.Cells(ROWS_TO_CLEAR - nRowsToClear + 1, 1), _
           rngCopyTo.Cells(ROWS_TO_CLEAR, COLS_TO_CLEAR))
  rngToClear.Clear
End If

' Wrap it up
rngCopyTo.Select
Windows(BOOK_BFOPTIONS).Activate
Worksheets(SHEET_BFOPTIONS).Activate
Application.ScreenUpdating = True
Set rngPrices = Nothing
Set rngTickers = Nothing
Set rngCopyFrom = Nothing
Set rngCopyTo = Nothing
Set rngFound = Nothing
Set rngLookup = Nothing
Set rngToClear = Nothing
End Sub

It should put the strike prices just where you want them.

 

[bobbythefish]

okay---tried suggestion--doesnt seem to work--first problem seems that in Cell G1 match criteria returns n/a--I rechecked all variables etc, so everything was entered correctly according to your spec.

Also in the index statement I'm not sure why CELL("row",B7)
what does the B7 represent? B7 is the Cell in the Active WS, but the rest of statement is only relevant to lookup WS.

Thanks again

 

[Zathras]

Well, as I said, I had to make some assumptions. Here is the setup that works for me. The problem is most likely related to the way Excel handles numbers differently than text strings. With both worksheets open in the same instance of Excel:

In ALL-Options.xls (Sheet: holdings):

A1: '4001123
B1: '4001124
C1: '4001125
D1: '4001126

In BF-options.xls:

C1: 4001
E1: 123
Y42: =C1&E1
G1: =MATCH(Y42,'[ALL-Options.xls]holdings'!$A$1:$F$1,0)

Note that using the ampersand, the values from C1 and E1 are concatonated (your word) which results in the value being treated as a text string. Consequently, the account numbers in the ALL-Options book (both sheets) are entered with a leading apostrophe to make Excel treat them as strings.

If you care to post the exact values you have in the same cells, perhaps I can spot the reason for the "N/A"

The formula

 =CELL("row",B7)

says simply "the row number of the referenced cell" and when it is in cell B7 (or any cell on row 7) the result is simply the number 7. I used that expression instead of "hard-coding" the number 7 because it allows the formula to be copied to other rows and Excel automatically adjusts the cell reference to continue providing the correct row number. Had I simply hard-coded a 7, each formula would have had to be edited manually to change the number to 8 in B8, 9 in B9, etc. Hand-editing formulas often results in errors. I prefer to use formulas that can be simply copied.

 

[bobbythefish]

Actually your assumption was correct, sorry for not mentioing that---in all cases, we are only dealing with text---(except of course price value which are not part of this issue). Reading through your code again, I also can't reconcile the instruction for cell F1: 'Column:'
Does this have any bearing on your posts to G1 and or B7(I assume not)? In addition, I made one adjustment to your suggested range--instead of DZ my sheet actually extends to IN.
To respond to your request for posting values:
in cell E1 I enter 05M02, which gets concatonated in cellY42 resulting in 278-05M02. In the holdings sheet of All options book, 278-05M02 is found in cell D4 and the values to be added in Cells b7 thru b32 in book bf-opts
reside in book all-opts cells D5 thru D30 (ie, D5 holds
AMGN, D6 holds BK, then BA, DIS, etc.) Presumably you are not allowed to telephone even a toll free number (or you simply don't wish to, which is okay too) I really appreciate your time and effort, this application has been a fun and challenging endeavor, which I hope to begin to oput to use next Tuesday.
Regards,
Bobby

 

[Zathras]

Ok, no problem. Now that you have told me where your account numbers are (I had assumed row 1). Here is what you need:

Set up BF-Options.xls this way: (F1 is just for documentation - you can move F1 and G1 to wherever you want after first typing them as indicated.)

F1: 'Column:'
G1: =MATCH(Y42,'[ALL-Options.xls]holdings'!$A$4:$DX$4,0)
B7: =INDEX('[ALL-Options.xls]holdings'!$A$5:$DX$30,CELL("row",B7)-6,$G$1)

Now copy the formula in B7 to B8 thru B32.

With that set-up you should still see the tickers appear in column B when you type a new suffix in E1.

Do whatever it is that you do to get the prices in column H. Then run the macro after making these changes (replace two lines):

Const ROW_ACCOUNTLIST = 4

'<--- changed from 1

Const CELLS_LOOKUP = "$A4:$IV4"

'<---- changed from row 1

The reason I stopped at column "DZ" (I actually should have stopped at column "DX&quot is the simple fact that you need two columns for each account on the "strikes" sheet. Since there is a maximum of 256 columns on a sheet, you can only have 128 accounts on the "strikes" sheet. Of course, if you have more than 128 accounts, the simplest way to handle it would be to have separate workbooks for each group of acounts. It would be possible to set up 2 "strikes" sheets to accommodate more than 128 accounts, but it makes the code more complex. And since you will have the same problem when you have more than 256 accounts, why not begin the multiple book library now?

BTW, since you are new to Tek-Tips, you should take a few minutes and click on the "About Us" link at the top of the screen. I don't work for Tek-Tips. I'm just one of many around the world who enjoy helping others solve technical problems in my spare (!??!) time. If you donate to Tek-Tips (and you should if I have really helped you) I won't see a dime. It has been fun. Please let me know if this has worked for you. And don't hesitate to post more details if it still doesn't do what you want.

 

[bobbythefish]

Thanks for the info---I did think you worked for Tek-tips. I think it's great what you are doing and I hope to return the favor to you or someone else one day soon.

Well, we are getting closer---now the value is cell G1 is returning "4" which is correct and the stocks in b7 thru b32 are coming up as well! so far so good (the best part is that I think i understand the logic [didn't know of functions Match or Index--very helpful].
My next task is to try the macro---I havent studied it yet---but my fingers are crossed---
thanks again, I'll let you know---

 

[bobbythefish]

ok---new snag-- when coding (think I'll cut and paste instead) your Constant declaration :
Const SHEET_BFOPTIONS ="Sheet1" creates a small problem
The Book is, as you know-BF-Options, the actual name of the Sheet that performs the look up etc. is names "OPT-Sheet"
VB doesn't like the hyphen now what??
thanks

 

[Zathras]

You must be mis-typing or mis-spelling the sheet name. I changed the sheet name to

OPT-Sheet

and changed the declaration in the macro to:

Const SHEET_BFOPTIONS = "OPT-Sheet"

'<--- changed from &quot;Sheet1&quot;

and it works ok in my test bench. Watch out for the difference between the hyphen "-" and the underscore "_"

 

[bobbythefish]

okay I guess I'm not clear on the syntax then (no typo)
I thought you were defining constants and assigning new names. So now I need to give you the fuller picture so you can help me sort out what substitutions to make. The names I gve you were not exact, I shortened or changed them just to be brief or clear, but it seems I've only confused myself.
The WB in which the user (mostly me) enters the account suffix is really BF-Auto-options (not much of a change) the name of the sheet, as you now know is OPT-SHEET.
The WB containing the tables has 3 relavent sheets.
one is Basis another is Strikes (this you know) and the one that you know as holdings, is actually simply sheet4 (at least for now). Can you tell me waht is any substitutions to you code I need to make based on this info?
Sorry for the confusion--I thought I'd be able to figure out what you were doing and interpret the changes myself---guess i was wrong...

 

[bobbythefish]

okay I guess I'm not clear on the syntax then (no typo)
I thought you were defining constants and assigning new names. So now I need to give you the fuller picture so you can help me sort out what substitutions to make. The names I gve you were not exact, I shortened or changed them just to be brief or clear, but it seems I've only confused myself.
The WB in which the user (mostly me) enters the account suffix is really BF-Auto-options (not much of a change) the name of the sheet, as you now know is OPT-SHEET.
The WB containing the tables has 3 relavent sheets.
one is Basis another is Strikes (this you know) and the one that you know as holdings, is actually simply sheet4 (at least for now). Can you tell me what, if any substitutions to you code I need to make based on this info?
Sorry for the confusion--I thought I'd be able to figure out what you were doing and interpret the changes myself---guess i was wrong...

 

[Zathras]

Generally, the way I code, all you have to do is set the correct values into the constants and the code does the rest. In this case, make the following replacements:

Const BOOK_BFOPTIONS = "BF-Auto-options.xls"

'<--- changed from &quot;BF-Options.xls&quot;

Const SHEET_BFOPTIONS = "OPT-SHEET"

'<--- changed from &quot;OPT-Sheet&quot;

Const SHEET_HOLDINGS = "sheet4"

'<--- changed from &quot;holdings&quot;

and all should be well. Of course, if you change the names of sheets or book, you may have to change the constants to match. Since you have said nothing about the third sheet "Basis" there is no code to adjust for it. It is simply ignored by the macro.

 

[bobbythefish]

Zathras, I'm sorry but may I prevail upon you one more time? Almost there!
Tried running macro and returns error - not able to find account number in sheet---I look through your code to try to understand what you did--
First thing is, I don't need to capture the ticker symbol because they already wexist in strikes sheet, as this sheet is an exact copy of sheet4 (holdings sheet)--the only difference is that , for example, if column A in holdings holds ticker symbols, column B will holds # of shares, while in Strikes sheet, same tickers in same order only column B now has to capture and hold strikes as calculated from BF-All opts book/sheet.
So, Macro must not copy ticker symbols (already there)

Not sure (in Declarations section) why "Col-Tickers = 2 'Column "B"
or why Col_Prices = 8 'Column "H"
Also--Can you explain what the decalaration OPTION EXPLICIT means?

Sorry for all this (I'm learning VB at home in spare time, but just beginning)

 

[Zathras]

First, I copied the ticker symbols because it is vital that the same list is used as from the holdings sheet. It has been my experience that when someone says that something is exactly something else, it is not always the case. My point is that if it is exactly the same, then copying does no harm, and if there is a problem then copying heals it.

Second, I put the message in to protect against exactly that problem, namely that the account number couldn't be found on the strikes sheet. It is absolutly vital that the pasting be done in the right place or you could damage some other data.

Here is a description of what the code is doing: N.B. when a const is used (by convention, declared in all capital letters), you have to look at the

const

declaration to see what is actually used.

Application.ScreenUpdating = False

Prevents the screen from flashing as the ALL-Options sheet is selected.

Windows(BOOK_ALL).Activate

Makes the ALL-Options sheet the active workbook

Worksheets(SHEET_STRIKES).Activate

Makes the strikes sheet the active sheet

Set rngLookup = Range(CELLS_LOOKUP)

Allows manipulation of range $A4:$IV4 within the next line of code

Set rngFound = rngLookup.Find(What:=sAccountID, LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext)

Tries to find the account id in the range A4:IV4 note that sAccountID was populated with the value from cell Y42 in the BF-Auto-options workbook.

nAccountColumn = -1

Initializes the nAccountColumn variable to a value that can never be returned from the lookup operation.

On Error Resume Next

Since it is possible that the lookup failed, the next line may not work, so guard against the error. There are a couple of basic techniques that can do this. I chose to do it this way, since the expected result is no error. (When the expected result is to have an error, then a different technique is used, but I don't want to confuse you any further.

nAccountColumn = rngFound.Column

Find what column the account number is in. Again, I don't assume that the sheet has the account number in exactly the same place. In fact, the way you described the holdings sheet I got the distinct impression that the columns were NOT the same. (Hence the confusion over how to write the MATCH formula.)

On Error GoTo 0

Revert error handling to normal.

If nAccountColumn = -1 Then

If nAccountColumn is still -1 that means that the lookup failed.

  ' Not foundWindows(BOOK_BFOPTIONS).Activate

A comment to describe what is happening in the next part of the code.

...Not sure (in Declarations section) why "Col-Tickers = 2 'Column "B"
or why Col_Prices = 8 'Column "H"...

COL_TICKERS and COL_PRICES are declared as constants (N.B. COL_TICKERS has an underscore, not a hyphen as in your post. This kind of alteration can break the code so that it doesn't work.) Constants are (or should be) used in the code instead of "magic numbers" so that when the environment changes, the code can be modified with reduced chance for errors. You specified that the ticker symbols were in column "B" of the OPT-Sheet sheet and that the prices were developed in column "H" of the OPT-Sheet sheet. You also specified that the name of the sheet in BF-Auto-options is OPT-Sheet sheet. It also provides a bit of documentation. I find that

  Set rngPrices = Range(Cells(ROW_COPYFROM, COL_PRICES), _
                        Cells(nLastRow, COL_PRICES))

is a little easier to read than

  Set rngPrices = Range(Cells(7, 8), Cells(nLastRow, 8))

even though it does fit on one line instead of two.

If you insist, you could code the line like this:

  Set rngPrices = Range("H7:H" & nLastRow)

which may look easier when writing the code the first time. But if the shape of your data changes such that the first row changes from 7 to 9, are you sure you will remember to change this particular formula? By using defined constants, the maintenance of your code becomes easier (especially if someone else has the job of making a change). It's a trade-off.

You should always place

  Option Explicit

as the first line in any code module. It simply tells the compiler that you promise to declare all variables and constants before using them. This is a good thing. It catches most typing mistakes. If you declare a constant as COL-TICKERS, and try to use the expression COL_TICKERS somewhere in the code, the compiler will refuse to generate code and the macro won't run.

Based on what you have now stated, the formula in G1 should indeed go all the way to column IV (i.e. use the entire row):

G1: =MATCH(Y42,'[ALL-Options.xls]sheet4'!$4:$4,0)

Note that if and when you rename "sheet4" to something else, the formulas will be updated by Excel, but you will have to update the

 const

declaration yourself.