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

 

[bobbythefish]

Thanks--I feel bad that you are putting so much time into this. I'm not sure what you just told me can ehlp me with current issue though..
my Strikes sheet looks like this (partial view for example):

column (A or C or E...) column (B or D or F...)
278-12345 Strike
ibm 85
amgn 60
dis 20
bax 25
dis 25 (where the strike are the
variables to be
populated/re-populated)

it's ok to copy over symbols but when I ran the macro it returned "could not find 278-12345 in sheet strikes"
To clarify, odd series columns (where A is odd and B is even as is D, F H etc.) hold the symbols and the even Columns hold the strikes---the Account number (text) will always be found immediately above the first ticker in the the odd column. Does this change the way you thought is was layed out?

 

[Zathras]

Nope. Works just fine for me...

Are the account numbers on the strikes sheet on row four?

Are the constants set up the way I said:

Const BOOK_BFOPTIONS = "BF-Auto-options.xls"
Const BOOK_ALL = "ALL-Options.xls"
Const SHEET_BFOPTIONS = "OPT-SHEET"
Const SHEET_HOLDINGS = "sheet4"
Const SHEET_STRIKES = "strikes"
Const ROW_ACCOUNTLIST = 4
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 = "$A4:$IV4"

Pay particular attention to that last one (CELLS_LOOKUP).
Also are the correct names supplied for BOOK_ALL and SHEET_STRIKES?

 

[bobbythefish]

Eureka! How can I thank you---I still had row 1 designated from your old code where row 4 should have been (from before I defined the starting row for you.

Seriously, If you are in the NY City area, I'd like to buy you a beer or something--at the very least!

Thanks again a huge time-save for me in my monthly tasks!
-Bobby

 

[Zathras]

You're welcome. I enjoyed the challenge. (That's why I do this. To me, Tek-Tips is a free source of puzzles to solve. Maybe I should make a donation!) Unfortunately, I live on the west coast and haven't been to the Big Apple for over 15 years. Tell you what... buy one for a fireman.

One parting thought. You will run into difficulty when you have more than 128 accounts because of the "hard-coded" book and sheet names. One way would be to clone both workbooks so that you use a custom BF options workbook with each ALL options workbook. That might work ok for the first couple of hundred accounts. However it would be prefereable to be able to use one BF workbook and have multiple sets of sheets in the ALL options workbook. Since you have a maximum of 256 sheets, that would give you room for over 8,000 accounts before you would need multiple books. (Assuming four sheets for each set of accounts, 128 per sheet x 64 sheet "sets" = 8,192).

I can imagine a combo box on the BF sheet that would allow you to select one of up to 64 "sets" of account sheets to work with. It's not really difficult, just tricky. And I would set up a naming convention so that the sheets can be identified by group. E.g., FolioAholdings, FoloAstrikes, FolioA..., FolioBholdings, FolioBstrikes, FolioB... and so on. Then the combo box would contain the values FolioA, FolioB, FolioC,... to allow you to select whichever one you want. Of course, if there is some natural grouping you could use, that would be even better. E.g. NorthEast, SouthEast, Central, West, etc.

 

[bobbythefish]

that sounds interesting--it also leads me to think that you know a why to dynamically (variably) lookup a workbook.
For example, if i had each account in a self-contained workbook, rather than all account in one big table, could it call up the appropriate client file on the fly (enter account suffix, concatonate, then do a lookup or whatever an workbook 278-1234.xls where 1234 was the suffix? this method would be preferable to the one we just work through for a variety of reason (in fact that was my first approach but I couldn't figure a way to do it)

what's your real first name by the way? is it Zathras?

 

[bobbythefish]

***New wrinkle accounts past column DX are not being read. When I tried to modify code in BF-auto-options
(to remind you cell b7= : =INDEX('[ALL-OPTS.XLS]SHEET4'!$A$5:$DX$30,CELL("ROW",b7)-6,$G$1) -DON'T WORRY ABOUT CASE BECAUSE I JUST CUT AND PASTED YOUR CODE--
Anyway I tried changing $DX to $IN but to no avail..

Am I helpless or what?

 

[Zathras]

Changing the reference from $DX to $IN in the formula in B7 should work just fine. Remember to copy the formula down into B8 thru whatever; also change it in cell G1.

As for separate books, that's easily done. Set up the BF worksheet this way:

G1: =MATCH(Y42,INDIRECT(H1&"$4:$4"),0)
H1: ="'["&Y42&".xls"&"]sheet1'!"
B7: =INDEX(INDIRECT($H$1&"A4:IV999"),CELL("row",B7)-5,$G$1)

Copy the formula in B7 to B8 thru B42 (or whatever)
Put the following code in the sheet code module (Right-click on the tab and select View Code or in the VBA editor, double-click on "Sheet1 (OPT-Sheet)" in the project explorer.)

Option Explicit
Const ACCOUNT_KEY = "$E$1"
Const ACCOUNT_NUMBER = "$Y$42"
Dim gsLastBook As String

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  If Target.Address = ACCOUNT_KEY Then
    Application.ScreenUpdating = False
    On Error Resume Next
    Windows(gsLastBook).Close
    gsLastBook = Range(ACCOUNT_NUMBER) & ".xls"
    Workbooks.Open gsLastBook
    Target.Worksheet.Activate
    Application.ScreenUpdating = True
  End If
End Sub

Now whenever you change the contents of cell E1, the current data book will close and the new one will open. No need to open a data book first, just open the BF book and type a number in E1.

If when first opening the BF book, it is showing 12345 and that is the data you want, just type 12345 again and hit enter to open it.

Of course, this makes a lot of the code in the other macro obsolete, but that still works as long as the account number is on row 4.

No, Zathras is not my real name. It is just the name of my favorite character from Babylon 5. If you're curious, you can look it up on the web. Here is a sample:

http://www.loyd.net/osz/wisdom.html