Convert clunky nested If statement to macro
 

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,I F(F2="Rock",40,IF(F2="Lime",205,IF(F2="Concrete",8 0,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

Convert clunky nested If statement to macro
 

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(Vlook up(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,I F(F2="Rock",40,IF(F2="Lime",205,IF(F2="Concrete",8 0,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

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


--
westexp
------------------------------------------------------------------------
westexp's Profile: http://www.excelforum.com/member.php...o&userid=33831
View this thread: http://www.excelforum.com/showthread...hreadid=536084

Convert clunky nested If statement to macro
 

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,FALS E)
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

--
westex
-----------------------------------------------------------------------
westexp's Profile: http://www.excelforum.com/member.php...fo&userid=3383
View this thread: http://www.excelforum.com/showthread.php?threadid=53608

Convert clunky nested If statement to macro
 

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


--
westexp
------------------------------------------------------------------------
westexp's Profile: http://www.excelforum.com/member.php...o&userid=33831
View this thread: http://www.excelforum.com/showthread...hreadid=536084