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

CAN'T reference Value on another sheet!
 

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

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