Convert clunky nested If statement to macro

[westexp]

I've known for a long time there must be a better way, but just haven't had the time to learn vba. I need to add a column "G" that calculates a rate based on column "F". What I currently have in column "G":

=IF(F2="Recycle",+((H2*7*0.25)+5),IF(F2="BS",130,IF(F2="Rock",40,IF(F2="Lime",205,IF(F2="Concrete",80,IF(F2="Sand",30,"???"))))))


In searching the forum, I think I need to create a rate sheet and accomplish this through vlookup, then loop. Am I on the right track? Any help is much appreciated. Thanks so much, Laurie

[Tom Ogilvy]

Set up a table like this on Sheet2 (BS in A1, 80 in B4)

BS 130
Rock 40
Lime 205
Concrete 80


=IF(F2="Recycle",+((H2*7*0.25)+5),If(iserror(Vlookup(F2,Sheet2!$A$1:$B$4,2,False)),"???",Vlookup(F2,Sheet2!$A$1:$B$4,2,False))



In code you would do

if range("F2") = "Recycle" then
Range("G2") = Range("H2").Value * 7 * 0.25 + 5
else
res = Vlookup(range("F2"),Worksheets("Sheet2").Range("A1:B4"),2,False)
if iserror(res) then
Range("G2") = "???"
else
Range("G2") = res
end if
end if

Or were you looking for a UDF?

--
Regards,
Tom Ogilvy



"westexp" wrote:

>
> I've known for a long time there must be a better way, but just haven't
> had the time to learn vba. I need to add a column "G" that calculates
> a rate based on column "F". What I currently have in column "G":
>
> =IF(F2="Recycle",+((H2*7*0.25)+5),IF(F2="BS",130,IF(F2="Rock",40,IF(F2="Lime",205,IF(F2="Concrete",80,IF(F2="Sand",30,"???"))))))
>
>
> In searching the forum, I think I need to create a rate sheet and
> accomplish this through vlookup, then loop. Am I on the right track?
> Any help is much appreciated. Thanks so much, Laurie
>
>
> --
> westexp
> ------------------------------------------------------------------------
> westexp's Profile: http://www.excelforum.com/member.php...o&userid=33831
> View this thread: http://www.excelforum.com/showthread...hreadid=536084
>
>

[westexp]

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

[westexp]

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

[westexp]

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