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.

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:

Public Sub Test()
  Range("O14").Value = GetResult(Range("F14").Value, Range("E14").Value)
End Sub

Public Function GetResult(arg1 As String, arg2 As String) As String
  If (arg1 = 700 Or arg1 = 705 Or arg1 = 707 Or arg1 = 710 Or arg1 = 720 Or arg1 = 730) And Not (arg2 = "INL" Or arg2 = "FDK" Or arg2 = "FDP" Or arg2 = "FG" Or arg2 = "RMB") Then
    GetResult = "ADV"
  ElseIf (arg1 = 732 Or arg1 = 734 Or arg1 = 750) And Not (arg2 = "INL" Or arg2 = "FDK" Or arg2 = "FDP" Or arg2 = "FG" Or arg2 = "RMB") Then
    GetResult = "DOC"
  ElseIf (arg1 = 103 Or arg1 = 199 Or arg1 = 202 Or arg1 = 742 Or arg1 = 747) And Not (arg2 = "INL" Or arg2 = "FDK" Or arg2 = "FDP" Or arg2 = "FG" Or arg2 = "RMB") Then
    GetResult = "PAY"
  ElseIf (arg1 = 740 Or arg1 = 742 Or arg1 = 747 Or arg1 = 799) And arg2 = "RMB" Then
    GetResult = "RMB"
  Else
    GetResult = vbNullString
  End If
End Function

Try this:

Public Sub Test()
  Dim r As Long, LastRow As Long
  
  LastRow = ActiveSheet.UsedRange.Rows.Row
  
  For r = 3 To LastRow Step 1
    Cells(r, 15).Value = GetResult(Cells(r, 6).Value, Cells(r, 5).Value)
  Next
End Sub

Try

Sub test()
    Range("e2", Range("e" & Rows.Count).End(xlUp)).Offset(, 10).Formula = "=Koyish(e2,f2)"
End Sub

Function Koyish(r1, r2) As String
    Dim myList
    myList = Array("INL", "FDK", "FDP", "FG", "RMB")
    If TypeName(r1) = "Range" Then r1 = r1.Value
    If TypeName(r2) = "Range" Then r2 = r2.Value
    Select Case r2
        Case 700, 705, 707, 710, 720
            If IsError(Application.Match(r1, myList, 0)) Then
                Koyish = "ADV"
            End If
        Case 732, 734, 750
            If IsError(Application.Match(r1, myList, 0)) Then
                Koyish = "DOC"
            End If
        Case 103, 199, 202, 742
            If IsError(Application.Match(r1, myList, 0)) Then
                Koyish = "PAY"
            End If
        Case 740, 742, 747, 799
            If r1 = "RMB" Then Koyish = "RMB"
    End Select
End Function

Working here,

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

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