Convert clunky nested If statement to macro

  • Thread starter Thread starter westexp
  • Start date Start date
W

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
 
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?
 
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 i
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 200
if it matters.

Any help much appreciated. Thank you, Lauri
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top