Macro for Nested IF with over 7 variables

xsitm

New Member
Joined
Jun 16, 2011
Messages
3
I’ve been working on a Macro for quite sometime now that will automatically format a daily excel report and cannot seem to get the code right with this final part.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
I'd like to create a Macro that will insert various text in Column U depending on conditions from column’s I and O within their corresponding rows.<o:p></o:p>
<o:p></o:p>
Ex.
If column I has the text “AM” & # in Column O <0, Column U should say “Accrual”
If column I has text “AM” & # in Column O (#'s) >0, Column U should say “Chargeback”
If column I has text “A” & # in Column O(#'s) is < 0, Column U should say “Bonus”<o:p></o:p>
If column I has text “A” & # in Column O(#'s) is >0, Column U should say “Chargeback”
If column I has text “BC” & # in Column O(#'s) is < 0, Column U should say “Agent”<o:p></o:p>
If column I has text “BC” & # in Column O(#'s) is >0, Column U should say “Wire”<o:p></o:p>
<o:p></o:p>
In total there are 13 different variables which has prevented me from using a nested if and looping
<o:p></o:p>
Also because the report changes from day to day I have no way of knowing how many lines there will be so the macro should fill in as long as there is data in columns I and O
<o:p></o:p>
Any help would be greatly appreciated.<o:p></o:p>

Thanks
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Welcome to the forumn,

Take a look at this. Sheet with your variable names and values

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>AM</TD><TD style="TEXT-ALIGN: right">-10</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>BC</TD><TD style="TEXT-ALIGN: right">8</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>BC</TD><TD style="TEXT-ALIGN: right">-8</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>AM</TD><TD style="TEXT-ALIGN: right">-7</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>AM</TD><TD style="TEXT-ALIGN: right">-6</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>A</TD><TD style="TEXT-ALIGN: right">-5</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>AM</TD><TD style="TEXT-ALIGN: right">-4</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>AM</TD><TD style="TEXT-ALIGN: right">-3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>A</TD><TD style="TEXT-ALIGN: right">9</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Code to run through

Sub selCase4()
finalrow = Cells(Rows.Count, 9).End(xlUp).Row
For i = 1 To finalrow
If Cells(i, 9).Value = "AM" And Cells(i, 10).Value < 0 Then
Cells(i, 12).Value = "Accural"
End If
If Cells(i, 9).Value = "AM" And Cells(i, 10).Value > 0 Then
Cells(i, 12).Value = "Chargeback"
End If
If Cells(i, 9).Value = "A" And Cells(i, 10).Value < 0 Then
Cells(i, 12).Value = "Bonus"
End If
If Cells(i, 9).Value = "A" And Cells(i, 10).Value > 0 Then
Cells(i, 12).Value = "Chargeback"
End If
If Cells(i, 9).Value = "BC" And Cells(i, 10).Value < 0 Then
Cells(i, 12).Value = "Agent"
End If
If Cells(i, 9).Value = "BC" And Cells(i, 10).Value > 0 Then
Cells(i, 12).Value = "Wire"
End If
Next i

End Sub

Then End result, you will need to adjust the column numbers.

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 28px"><COL style="WIDTH: 26px"><COL style="WIDTH: 64px"><COL style="WIDTH: 78px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>AM</TD><TD style="TEXT-ALIGN: right">-10</TD><TD></TD><TD>Accural</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>BC</TD><TD style="TEXT-ALIGN: right">8</TD><TD></TD><TD>Wire</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>BC</TD><TD style="TEXT-ALIGN: right">-8</TD><TD></TD><TD>Agent</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>AM</TD><TD style="TEXT-ALIGN: right">-7</TD><TD></TD><TD>Accural</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>AM</TD><TD style="TEXT-ALIGN: right">-6</TD><TD></TD><TD>Accural</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>A</TD><TD style="TEXT-ALIGN: right">-5</TD><TD></TD><TD>Bonus</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>AM</TD><TD style="TEXT-ALIGN: right">-4</TD><TD></TD><TD>Accural</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>AM</TD><TD style="TEXT-ALIGN: right">-3</TD><TD></TD><TD>Accural</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>A</TD><TD style="TEXT-ALIGN: right">9</TD><TD></TD><TD>Chargeback</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
 
Upvote 0
Welcome to the board..

This can be done fairly easily without VBA.
Build a lookup table as below in E2:G7

Then you have a simple formula like

=VLOOKUP(I2,$E$3:$G$7,2+(O2<0),0)

Hope this helps.

Excel Workbook
EFGHIOPQ
2If PositiveIf NegativeA-3Bonus
3AMChargebackAccrualA3Chargeback
4AChargebackBonusBC-5Agent
5BCWireAgentBC8Wire
6HFWhatverThisAM-22Accrual
7GIUseVlookupAM33Chargeback
8HF-72This
9HF55Whatver
Sheet1
 
Upvote 0

Forum statistics

Threads
1,224,586
Messages
6,179,717
Members
452,939
Latest member
WCrawford

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top