Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested IF statement not need help | Excel Discussion (Misc queries) | |||
IF OR NESTED STATEMENT | Excel Discussion (Misc queries) | |||
Nested IF/OR/AND Statement Help | Excel Worksheet Functions | |||
NESTED IF STATEMENT | Excel Worksheet Functions | |||
Nested If statement | Excel Worksheet Functions |