Use string as cell location, need help
 

Hi Seth

Cells doesn't take a string argument (it takes row index/column index
ie numbers) hence your problem. You can use Range instead:

Dim Loc As String

For i = 3 To 29
Sheets("Sheet1").Select
TempLoc = Cells(8, i).Formula
Loc = Right(TempLoc, Len(TempLoc) - 8)
If Cells(10, i) = "N.G." Then
Sheets("Sheet2").Select
Range("Loc") = "N/A"
End If
Next

Richard


Seth wrote:
I am trying to use a truncated string as a new cell reference, but I am
getting a type mismatch error. Please help.
For this example, Loc becomes "$X$62" which was created from a cell which
contained the formula =Sheet2!$X$62

Dim Loc As String

For i = 3 To 29
Sheets("Sheet1").Select
TempLoc = Cells(8, i).Formula
Loc = Right(TempLoc, Len(TempLoc) - 8)
If Cells(10, i) = "N.G." Then
Sheets("Sheet2").Select
Cells("Loc") = "N/A"
End If
Next

Perhaps there is a way to get the cell reference directly from cell(8,i)?
Thanks for the help.