Convert clunky nested If statement to macro

Tom, thanks for your response. In my attempt to fit your solution to the range that has data, I get a "Sub or Function not defined" error at Vlookup. Any help is much appreciated. It will probably make you laugh, but this is what I have so far on my test macro:

Sub Test()
Dim srcerange As range
Dim range As range
Dim tons As range

Set srcerange = range("F2").End(xlDown)
Set range = srcerange.Offset(0, 4)
Set tons = srcerange.Offset(0, 2)

For Each cell In range
If srcerange = "Recycle" Then
range = tons.Value * 7 * 0.25 + 5
Else
res = VLookup(range, Worksheets("Rates").range("A1:B5"), 2, False)
If IsError(res) Then
range = "???"
Else
range = res
End If
End If

Next cell

End Sub

I have tried many versions, stripped down to the basics: one column in sheet2, looking for value from sheet1:

Sub test()
Do
If IsEmpty(ActiveCell) Then
If IsEmpty(ActiveCell.Offset(0, -1)) Then
ActiveCell.Value = ""
Else
ActiveCell.Value = VLOOKUP(R1C1,Worksheets("Sheet1"),R1C1:R3C2,2,FALSE)
End If
End If

ActiveCell.Offset(1, 0).Select

Loop Until IsEmpty(ActiveCell.Offset(0, -1))

End Sub


I keep getting compile errors and "expected : list seperator" messages...I can't figure out what I'm doing wrong. Using Excel 2003 if it matters.

Any help much appreciated. Thank you, Laurie

Just in case this thread comes up in a search, thought I would post what works. This may not be the most efficient, but it works:

Sub Test2()
Do
If IsEmpty(ActiveCell) Then
If IsEmpty(ActiveCell.Offset(0, -4)) Then
ActiveCell.Value = ""
Else
If ActiveCell.Offset(0, -4) = "Recycle" Then
ActiveCell.Value = ActiveCell.Offset(0, -2) * 7 * 0.25 + 5
Else
ActiveCell.Value = Application.VLookup(ActiveCell.Offset(0, -4), range("Rates!A1:B5"), 2, False)
End If
End If
End If

ActiveCell.Offset(1, 0).Select

Loop Until IsEmpty(ActiveCell.Offset(0, -4))

End Sub