Transform Excel IF Function to VBA

Good day,

Can anyone help to transform this to VBA ?


=IF(AND(OR(F14=700;F14=705;F14=707;F14=710;F14=720;F14=730);AND(E14<>"INL";E14<>"FDK";E14<>"FDP";E14<>"FG";E14<>"RMB"));"ADV";
IF(AND(OR(F14=732;F14=734;F14=750);AND(E14<>"INL";E14<>"FDK";E14<>"FDP";E14<>"FG";E14<>"RMB"));"DOC";
IF(AND(OR(F14=103;F14=199;F14=202;F14=742;F14=747);AND(E14<>"INL";E14<>"FDK";E14<>"FDP";E14<>"FG";E14<>"RMB"));"PAY";
IF(AND(OR(F14=740;F14=742;F14=747;F14=799;);E14="RMB");"RMB";""))))

Thanks a lot. :)

Do you want the macro to write the equation to a cell on the worksheet or do you want the equivalent conditional if statement in VBA syntax?

I want the equivalent conditional if statement in VBA syntax.

Thanks in advance.

IMAGE.jpg

Here's the snapshoot of the excel file. The result of Column O will depends on Column E & F.

Hope to here from you soon.

Try

Please Login or Register  to view this content.

The Coding are just working on the row 14 only. How to apply on the entire Range O?

Originally Posted by mgs73

I think this is what you are asking for - should get you started at least:

Please Login or Register  to view this content.

Working here,

It is always best to upload a small sample workbook. Picture doesn't help at all.

Try this:

Please Login or Register  to view this content.

Thanks for the suggestion guys~~

At last, i complete the coding in a longer way.



Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Y = ActiveCell.Row

If Not Intersect(ActiveCell, Range("E3:E500")) Is Nothing Then
Call Team
End If

End Sub
Sub Team()

Dim wsMaster As Worksheet
Dim lastRowM As Long
Dim SR_E As Range
Dim SR_F As Range
Dim DestinationRange As Range
Dim X As Integer

Set wsMaster = ThisWorkbook.Sheets("Input SWIFT")

lastRowM = wsMaster.Range("A" & Rows.Count).End(xlUp).Row

Set SR_E = wsMaster.Range("E3:E" & lastRowM)
Set SR_F = wsMaster.Range("F3:F" & lastRowM)
Set DestinationRange = wsMaster.Range("O3:O" & lastRowM)


For X = 1 To SR_E.Count

'Manual Assign1 700 705 707 710 720 730
If SR_F(X, 1) <> 700 Or SR_F(X, 1) <> 705 Or SR_F(X, 1) <> 707 Or SR_F(X, 1) <> 710 Or SR_F(X, 1) <> 720 Or SR_F(X, 1) <> 730 Then
DestinationRange(X, 1) = ""
End If

'Manual Assign2 732 734 750
If SR_F(X, 1) <> 732 Or SR_F(X, 1) <> 734 Or SR_F(X, 1) <> 750 Then
DestinationRange(X, 1) = ""
End If

'Manual Assign3 103 199 202 299 742 747
If SR_F(X, 1) <> 103 Or SR_F(X, 1) <> 199 Or SR_F(X, 1) <> 202 Or SR_F(X, 1) <> 299 Or SR_F(X, 1) <> 742 Or SR_F(X, 1) <> 747 Then
DestinationRange(X, 1) = ""
End If

'Advising 700 705 707 710 720 730
If SR_F(X, 1) = 700 Or SR_F(X, 1) = 705 Or SR_F(X, 1) = 707 Or SR_F(X, 1) = 710 Or SR_F(X, 1) = 720 Or SR_F(X, 1) = 730 Then
DestinationRange(X, 1) = "ADV"
End If

'Doc Checking 732 734 750
If SR_F(X, 1) = 732 Or SR_F(X, 1) = 734 Or SR_F(X, 1) = 750 Then
DestinationRange(X, 1) = "DOC"
End If

'Payment 103 199 202 299 742 747
If SR_F(X, 1) = 103 Or SR_F(X, 1) = 199 Or SR_F(X, 1) = 202 Or SR_F(X, 1) = 299 Or SR_F(X, 1) = 742 Or SR_F(X, 1) = 747 Then
DestinationRange(X, 1) = "PAY"
End If

'Out of Scope INL FDK FDP FGH RMB
If SR_E(X, 1) = "INL" Or SR_E(X, 1) = "FDK" Or SR_E(X, 1) = "FDP" Or SR_E(X, 1) = "FGH" Or SR_E(X, 1) = "RMB" Then
DestinationRange(X, 1) = ""
End If

'Reimbursement
If SR_E(X, 1) = "RMB" And SR_F(X, 1) = 700 Then
DestinationRange(X, 1) = ""
ElseIf SR_E(X, 1) = "RMB" Then DestinationRange(X, 1) = "RMB"
End If

Next X

End Sub