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


In article ,
(Roba) wrote:

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