Hi
your assumption regarding R[0] is correct. for example
R[-1]C[1]
would pick the cell one row above and one column to the right. More
information about this can be found in the Excel help under R1C1
reference

--
Regards
Frank Kabel
Frankfurt, Germany


Ed wrote:
Frank, thank you so much! I have no idea what time it is in Germany,
but I know you've spent some on this with me, and I am very grateful.
I will study through what you've given me so I can understand it
myself, rather than just copy from nice people! 8) One Question:
can I assume in "R[0]C3" and "R2C[0]", the [0] is automatically
picking up the index number from the active row and column?

Ed

"Frank Kabel" wrote in message
...
Hi
problem was you were using the column index (22) as variable (Excel
expected 'V'). I changed your code:
- no need for selects / activate statements
- use the formula r1C1 notation instead

Sub Recover_Data()
Dim strLink As String
Dim rngLast As Long
Dim row_index As Long
' Delete Zero values in affected columns
Range("V:W").Select
Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True
' Last cell
rngLast = Range("B65536").End(xlUp).Row

For row_index = 2 To rngLast
With Cells(row_index, "V")
If .Value = "" Then
.FormulaR1C1 =

"=IF(ISERROR(MATCH(R[0]C3,TIRs!R2C3:R15000C3,0)),0,INDEX(R2C[0]:R15000C
[0],MATCH(R[0]C3,TIRs!R2C3:R15000C3,0)))"
End If
End With
Next row_index
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany


Ed wrote:
"Frank Kabel" wrote in message
...
Hi Ed
then please your complete code (otherwise it's complicated to
identify your error)

Here it is. The loop is copied six times total - I got tired of
trying to select the next range based on the variables (couldn't
get
that either!), and decided a chainsaw was as good as a scalpel if
it
got the job done! But it doesn't get past the first time without
an
error. And the other five times will be exactly the same except
for
the starting cell.

Thanks for all your help. I know you actually have a real life out
there somewhere, and I appreciate the time you're taking on this.
Ed

Sub Recover_Data()

Dim strLink As String
Dim rngLast As String
Dim i As Long ' Column
Dim j As Long ' Row

' Delete Zero values in affected columns
Range("V:W").Select
Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole,
_
SearchOrder:=xlByRows, MatchCase:=True

' Last cell
rngLast = Range("B65536").End(xlUp).Row

' First Column

' First cell
Range("V2").Select

' Loop through cells
Do While ActiveCell.Row < rngLast

i = ActiveCell.Column
j = ActiveCell.Row

' Get value
strLink = ActiveCell.Value

' If there's nothing in the cell
If strLink = "" Then

'ActiveCell.Formula = "=IF(ISERROR(MATCH($C" & j &
"TIRs!$C$2:$C$15000,0)),0,INDEX(TIRs!" & i & "$2:" & i &
"$15000,MATCH($C" & j & "TIRs!$C$2:$C$15000,0)))"
ActiveCell.Formula = "=IF(ISERROR(MATCH($C" & j &
",TIRs!$C$2:$C$15000,0)),0,INDEX(TIRs!" & i & "$2:" & i &
"$15000,MATCH($C" & j & ",TIRs!$C$2:$C$15000,0)))"

End If

' Move down
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate

Loop

' Loop repeats five more times

End Sub