vba help - IIF function

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
843
Office Version
  1. 2010
Platform
  1. Windows
Hi Team,

Below is a table, I am looking answer in using IIF vba function , trying to learn IIF

Criteria IF Range("A2") = Purchase
Amount B2 should be in Column E2.

OR

Criteria IF Range("A2") = Sales
Amount B2 should be in Column D2.

Below is a table , expected output is in Column D and E.
Book2
ABCDE
1Transaction TypeAmountCreditDebit
2Purchase6000-6000
3Sales80008000-
Sheet1



Thanks
mg
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,992
Office Version
  1. 365
Platform
  1. Windows
In simple form to work with one single row.
VBA Code:
Range("B2").Value = IIf(Range("A2").Value ="Purchase", Range("E2").Value, Range("D2").Value)
If you were looping through the cells in column A, with an individual cell in column A referred to by the variable c. in the line below.
VBA Code:
c.Offset(0, 1) .Value = c.Offset(0, IIf(c.Value ="Purchase", 4, 3)).Value
As you can see there is a big difference in the way that the two lines are written. These are just the first two that came to mind with very little thought, there are probably many more.
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
843
Office Version
  1. 2010
Platform
  1. Windows
Hi Jason,

Thanks for your help , I liked the code and it will sufficient. thanks 🕺


Thanks
mg
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
Another solution could be

VBA Code:
Sub PurchaseOrSales()
Dim cell As Range
For Each cell In Range("A2:A5")
If cell.Text = "Purchase" Then
    cell.Offset(0, 4).Value = cell.Offset(0, 1).Value
    End If
If cell.Text = "Sales" Then
    cell.Offset(0, 3).Value = cell.Offset(0, 1).Value
    
    End If
Next cell

End Sub
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
843
Office Version
  1. 2010
Platform
  1. Windows
Hi Trevor,

Right! thanks for it. (y)


Thanks
mg
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,992
Office Version
  1. 365
Platform
  1. Windows
I thought that you just wanted examples of IIf so that you could see how it worked, but another method without it would be
VBA Code:
With Range("B2:B3")
    .Formula = "=MAX(D2:E2)"
    .Value = .Value
End With
 

Watch MrExcel Video

Forum statistics

Threads
1,123,380
Messages
5,601,282
Members
414,440
Latest member
Kim0204

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
Top