IF AND OR statements in VBA

[Azar]

Hi All,
I have the following IF statement as a formula in Excel:

=IF(OR(K2="JPY",I2="JPY"),"USDJPY",IF(OR(K2="EUR",I2="EUR"),"EURUSD",IF(OR(K2="CHF",I2="CHF"),"USDCHF",IF(OR(K2="CAD",I2="CAD"),"USDCAD",IF(OR(I2="GBP",K2="GBP"),"GBPUSD",IF(OR(I2="AUD",K2="AUD"),"AUDUSD"))))))

I want to be able to write something like this in VBA so that is is easier to use and so i can use more than 7 IF statments.

I dont know the proper syntax for composing this formula in a specific cell.

My goal is to build this into a preexisting code that i have

many thanks in advance
-Az

 

[kpark91]

You can use IF statements like this

If Range("K2").Value = "JPY" Or Range("I2").Value = "JPY" Then
   Range("A1").Value = "USDJPY"
ElseIf Range("K2").Value = "EUR" Or Range("I2").Value = "EUR" Then
   Range("A1").Value = "EURUSD"
'Keep going <- add ' to comment
Else
   Range("A1").Value = "OTHERS"
End If

Just several things to keep in mind,
You have to specify what you're comparing against after AND or OR operators
For example you CANNOT

If Range("A1").Value = "USD" OR "EUR" Then

you would have to explicitly state

If Range("A1").Value = "USD" Or Range("A1").Value = "EUR" Then

Moreover, you don't need Else statement or ElseIf statement to be able to use If statement. These Else statements are optional but you must use End If statement to indicate the end of the statement.

Anyways, might as well finish what I have started

Sub IfStatements()
Dim strRangeFormula As String
strRangeFormula = "A1" 'Replace this cell with the cell which has your formula in..
 
If Range("K2").Value = "JPY" Or Range("I2").Value = "JPY" Then
   Range(strRangeFormula).Value = "USDJPY"
ElseIf Range("K2").Value = "EUR" Or Range("I2").Value = "EUR" Then
   Range(strRangeFormula).Value = "EURUSD"
'Keep going <- add ' to comment
ElseIf Range("K2").Value = "CHF" Or Range("I2").Value = "CHF" Then
   Range(strRangeFormula).Value = "USDCHF"
ElseIf Range("K2").Value = "CAD" Or Range("I2").Value = "CAD" Then
   Range(strRangeFormula).Value = "USDCAD"
ElseIf Range("K2").Value = "GBP" Or Range("I2").Value = "GBP" Then
   Range(strRangeFormula).Value = "GBPUSD"
ElseIf Range("K2").Value = "AUD" Or Range("I2").Value = "AUD" Then
   Range(strRangeFormula).Value = "AUDUSD"
End If
End Sub

 

[Azar]

thanks much!
do you know if it is possible for an IF statement to output various results in different places?
so if one one of my conditions is met then can I have 3 different sets of data output into 3 different cells?

Also- I dont understand this part of the code:
Dim strRangeFormula As String
strRangeFormula = "A1" 'Replace this cell with the cell which has your formula in..

what does that mean?

thanks! I'm fairly new to VBA.

-Az

 

[Scott Huish]

See original post here:
http://www.mrexcel.com/forum/showthread.php?t=562104

I showed you how to use VLOOKUP.

Please post all clarifications in the original thread.