Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CAN'T reference Value on another sheet!
This is my problem...
I have a 3 sheet wb Sheet 1 is the user interface sheet 3 contains Tables of values The user enters data on a row on sheet 1 en presses a button The resulting macro gets the data and references values on sheet 3 that it uses. With one issue I have a problem... The user enters the currency of an Amount (like USD, ZAR, GBP) The relevant table to use on sheet 3 is 15 rows by 4 colums. (named: Currencies) column 1 = currency code like USD, ect.. col 2+3(merged) = The cur. name (like US Dollars), 4 = a Value This is the section of the macro I try to use to get the mathing currency's Value (in column 4 of the 'Currencies' range on sheet 3) (p.s. Col 1 of table = col 1 of sheet3) ====== 'previous code (in which 'AD' was declared as long, 'cur' as string) Dim c As Variant For Each c In Range("Currencies") If c.Value = cur Then AD = Sheets(3).Range(Cells(c.Row, 4), Cells(c.Row, 4)).Value Exit For End If Next c 'more code... I get: "Application defined or object defined error" If I loose the Sheets(3) it works, but references the cells on the active sheet wich is of course sheet 1 which is active when the macro starts... IF I use Sheets(3).select before, and Sheets(1).select after the loop, it also works but is of course very messy. Any help would be greatly appreciated... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
CAN'T reference Value on another sheet!
Thanks , that worked
This one I also picked up on another post For Each c In Range("Currencies") If c.Value = cur Then AD = Range(c.Offset(0, 3), c.Offset(0, 3)).Value Exit For End If Next c This way you keep on referencing c wich has scope on the sheet of the range that it is busy looping through (or something like that) Thanks again R |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting a sheet name reference within a cell reference??? | Excel Discussion (Misc queries) | |||
How To make a sheet reference Variable (eq: sum(sheet!D2:H2)) | Excel Discussion (Misc queries) | |||
multiple cell reference from sheet to sheet | Excel Worksheet Functions | |||
Changing sheet reference to cell reference | Excel Worksheet Functions | |||
Relative Sheet Reference (Summary Sheet) | Excel Discussion (Misc queries) |