CAN'T reference Value on another sheet!

R

Roba

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...
 
J

JE McGimpsey

The unqualified Cells() method defaults to the ActiveSheet, so your
statement

AD = Sheets(3).Range(Cells(c.Row, 4), Cells(c.Row, 4)).Value

Is equivalent to

AD = Sheets(3).Range(ActiveSheet.Cells(c.Row, 4), _
ActiveSheet.Cells(c.Row, 4)).Value

which, if the ActiveSheet isn't Sheets(3), will fail since ranges have
to be confined to a single sheet. You need to qualify the Cells method.

Try:

Dim c As Range
For Each c In Range("Currencies")
If c.Value = cur Then
With Sheets(3)
AD = .Range(.Cells(c.Row, 4), .Cells(c.Row, 4)).Value
End With
Exit For
End If
Next c
 
R

Roba

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
 
Top