vba help - IIF function

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
976
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.
 
Upvote 0
Hi Jason,

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


Thanks
mg
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

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