Convert formula to VBA Code (Loop)

M

MHamid

Active Member
Joined
Jan 31, 2013
Messages
472
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello,

I have the below formula that I need to convert into a Looped VBA code, Can anyone please assist. Column A houses the account numbers for varying rows (data row count fluctuates). Any assistance would be greatly appreciated.
Also, instead of coding a range (B:B) into VBA, would it be possible to use a named range in VBA to loop though columns B (AccountName) and column C (Region)?

Excel Formula: 
=IF(AND(SUMPRODUCT(--ISNUMBER(SEARCH({"LATAM "," LATAM "," LATAM"},B2)))>0,SUMPRODUCT(--ISNUMBER(SEARCH({"NAM","North America"},C2)))>0),"NA",IF(SUMPRODUCT(--ISNUMBER(SEARCH({" US "," NAM "," NAM","NAM,"},B2)))>0,"NA",IF(SUMPRODUCT(--ISNUMBER(SEARCH({"Mexico","MEX","México"},B2)))>0,"MEX",IF(SUMPRODUCT(--ISNUMBER(SEARCH({"LATAM "," LATAM "," LATAM"},B2)))>0,"LATAM",IF(SUMPRODUCT(--ISNUMBER(SEARCH({"NAM","North America"},C2)))>0,"NA",IF(ISNUMBER(SEARCH("Japan",C2)),"Japan",IF(SUMPRODUCT(--ISNUMBER(SEARCH({"APAC "," APAC "," APAC"},B2)))>0,"ASPAC",IF(ISNUMBER(SEARCH("Mexico",C2)),"MEX",IF(ISNUMBER(SEARCH("EMEA",C2)),"EMEA",IF(ISNUMBER(SEARCH("Asia Pacific",C2)),"ASPAC",IF(ISNUMBER(SEARCH("LATAM",C2)),"LATAM",IF(C2="",IF(SUMPRODUCT(--ISNUMBER(SEARCH({"US","Global"},B2)))>0,"NA",IF(C2="",IF(ISNUMBER(SEARCH("Mexico",B2)),"MEX","NEED TO MANUALLY ENTER")))))))))))))))

Thank you,
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
Can you describe what this formula is supposed to do? It will be easier to write the VBA from scratch based on a specification than figuring it out from the formula.

If all you have is the formula then hopefully someone will take the time to reverse engineer it to figure out what the code should do.
 
Upvote 0
Basically, the formula is running through the following scenarios:
1. If audit Name (Column B) has a variation of the word LATAM {"LATAM "," LATAM "," LATAM"} and Region (Column C) has a variation of NA {"NAM","North America"} then the result should be "NA", if not then
2. If Audit Name (Column B) has a variation of NA {" US "," NAM "," NAM","NAM,"} then the result should be "NA", if not then
3. If Audit Name (Column B) has a variation of MEXICO {"Mexico","MEX","México"} then the result should be "MEX", if not then
4. If Audit Name (Column B) has a variation of LATAM {"LATAM "," LATAM "," LATAM"} then the result should be "LATAM", if not then
5. If Region (Column C) has a variation of NA {"NAM","North America"} then the result should be "NA", if not then
6. If Region (Column C) has Japan then the result should be "Japan", if not then
7. If Audit Name (Column B) has a variation of ASPAC {"APAC "," APAC "," APAC"} then the result should be "ASPAC", if not then
8. If Region (Column C) has Mexico then the result should be "MEX", if not then
9. If Region (Column C) has EMEA then the result should be "EMEA", if not then
10. If Region (Column C) has Asia Pacific then the result should be "ASPAC", if not then
11. If Region (Column C) has LATAM then the result should be "LATAM", if not then
12. If Audit Name (Column B) has a variation of {"US","Global"} then the result should be "NA", if not then
13. If Region (Column C is blank, then if Audit Name (Column B) has Mexico then the result should be "MEX", if not then
remaining results should state "NEED TO MANUALLY ENTER"


Hope that helps.
 
Upvote 0
Do you actually want to replace the formula with code or do you want code to copy the formula down?
 
Upvote 0
Without sample data in a sample worksheet, its hard to test the results.
Anyway, try below code. If it did not work, try to upload a screenshot/image or XL2BB, with manual output
Assume your have name range "AccountName" for column B and "Region" for column C
VBA Code: 
Option Explicit
Sub test()
Dim i&, rngB, rngC, B As String, C As String, result()
rngB = Range("AccountName").Value: rngC = Range("Region").Value
ReDim result(1 To UBound(rngB), 1 To 1)
    For i = 1 To UBound(rngB)
        B = " " & UCase(rngB(i, 1)) & " ": C = UCase(rngC(i, 1))
        Select Case True
            Case B Like "* LATAM *" And (C Like "NAM" Or C Like "NORTH AMERICA")
                result(i, 1) = "NA": GoTo z
            Case B Like "* US *" Or B Like "* NAM *" Or B Like "* NAM,*"
                result(i, 1) = "NA": GoTo z
            Case B Like "* MEXICO *" Or B Like "* MEX *" Or B Like "* MÉXICO *"
                result(i, 1) = "MEX": GoTo z
            Case B Like "* LATAM *"
                result(i, 1) = "NA": GoTo z
            Case C Like "NAM" Or C Like "NORTH AMERICA"
                result(i, 1) = "NAM": GoTo z
            Case C Like "JAPAN"
                result(i, 1) = C: GoTo z
            Case B Like "* APAC *"
                result(i, 1) = "ASPAC": GoTo z
            Case C Like "MEXICO" Or C Like "EMEA"
                result(i, 1) = C: GoTo z
            Case C Like "ASIA PACIFIC" Or C Like "LATAM"
                result(i, 1) = C: GoTo z
            Case (B Like "* US *" Or B Like "* GLOBAL *") And Len(C) = 0
                result(i, 1) = "NA": GoTo z
            Case Else
                result(i, 1) = "NEED TO MANUALLY ENTER": GoTo z
z:
        End Select
    Next
With Range("D2").Resize(UBound(rngB), 1)
    .ClearContents
    .Value = result
End With
End Sub
 
Last edited:
Upvote 0
Solution
bebo021999 said:
Without sample data in a sample worksheet, its hard to test the results.
Anyway, try below code. If it did not work, try to upload a screenshot/image or XL2BB, with manual output
Assume your have name range "AccountName" for column B and "Region" for column C
VBA Code: 
Option Explicit
Sub test()
Dim i&, rngB, rngC, B As String, C As String, result()
rngB = Range("AccountName").Value: rngC = Range("Region").Value
ReDim result(1 To UBound(rngB), 1 To 1)
    For i = 1 To UBound(rngB)
        B = " " & UCase(rngB(i, 1)) & " ": C = UCase(rngC(i, 1))
        Select Case True
            Case B Like "* LATAM *" And (C Like "NAM" Or C Like "NORTH AMERICA")
                result(i, 1) = "NA": GoTo z
            Case B Like "* US *" Or B Like "* NAM *" Or B Like "* NAM,*"
                result(i, 1) = "NA": GoTo z
            Case B Like "* MEXICO *" Or B Like "* MEX *" Or B Like "* MÉXICO *"
                result(i, 1) = "MEX": GoTo z
            Case B Like "* LATAM *"
                result(i, 1) = "NA": GoTo z
            Case C Like "NAM" Or C Like "NORTH AMERICA"
                result(i, 1) = "NAM": GoTo z
            Case C Like "JAPAN"
                result(i, 1) = C: GoTo z
            Case B Like "* APAC *"
                result(i, 1) = "ASPAC": GoTo z
            Case C Like "MEXICO" Or C Like "EMEA"
                result(i, 1) = C: GoTo z
            Case C Like "ASIA PACIFIC" Or C Like "LATAM"
                result(i, 1) = C: GoTo z
            Case (B Like "* US *" Or B Like "* GLOBAL *") And Len(C) = 0
                result(i, 1) = "NA": GoTo z
            Case Else
                result(i, 1) = "NEED TO MANUALLY ENTER": GoTo z
z:
        End Select
    Next
With Range("D2").Resize(UBound(rngB), 1)
    .ClearContents
    .Value = result
End With
End Sub
Click to expand...
The named range are as follows:
- "AA_AP_Account_Name" ... this is in column K
- "AA_AP_Region" ... this is in column L
- "AA_AP_Region_Revised" ... this is in column AS
The result of the code should go into column AS ("AA_AP_Region_Revised"). See table and screenshot below.
Let me know if you have an updated code.

Audit_NumberAudit_NameRegionClusterCountryBusiness_ImpactedHierarchy_NoteRegion_Revised
A470538A470538 - Payment Services Directive 2 AttestationNAMUS(US)NA
A498925A498925 - ICG - Markets - US Primary Dealer Compliance and US Treasury AuctionsNorth AmericaUS(US)NA
A468646A468646 - ICG - Securities Services - Fund Accounting and Administration - NAMAsia Pacific;North AmericaAll India;Mumbai CSC(X68_R04);US(US)NA
A169094A169094 - ICG - Markets - Equities - Equity Derivatives - Flow and Convertibles Sales and Trading - NAM and EMEAEMEA;North AmericaUK(GB);US(US)NA
A466954A466954 -Horizontal/Thematic Audit - ICG Markets and Securities Services - FRB Controls Review - GlobalAsia Pacific;EMEA;Japan;LATAM;LATMex;Mexico;North AmericaAustralia(AU);Brazil(BR);Germany(DE);Hong Kong(HK);Japan(JP);Mexico(MX);Philippines(PH);Russia(RU);Singapore(SG);South Africa(ZA);UK(GB);US(US)NA
A536260A536260 - Continuous Audit - Segregation of Duties - Markets CBNA IndiaAsia PacificAll India;India(IN)ASPAC
A353166-20A353166 - GCB - Continuous Auditing - APACAsia Pacific;EMEABahrain(BH);UAE(AE)ASPAC
A492663-2A492663 – GCB- Continuous Auditing – Credit Bureau Access – EMEA - BahrainAsia Pacific;EMEABahrain(BH);UAE(AE)EMEA
A527129RAUP - Ireland DACEMEAIreland(IE)EMEA
A523638A523638 - ICG - Markets - Equities - FCX Operations - APACAsia Pacific;JapanIndia(IN);Japan(JP);Korea(KR);Singapore CSC(X68_R05);Singapore(SG);Taiwan(TW)Japan
A410181GSP APAC Trading and OperationsAsia Pacific;JapanAustralia(AU);China(CN);Hong Kong(HK);India(IN);Japan(JP);Singapore(SG)Japan
A336338ICG - BCMA - Capital Markets and Advisory - JapanJapanJapan(JP)Japan
A453721Treasury- LATAM- Mexico RAUPLATAMMexico(MX)MEX
A414340Honduras Liquidity Risk - Semi-Annual Regulatory Audit - 1H22LATAM;LATMexCCAHonduras(HN)LATAM
A533774Treasury & Capital Plan-Treasury - LATAM-Caribbean ClusterLATAMDominican Republic(DO);Trinidad & Tobago(TT)LATAM
A342753A342753 - Foreign Brokers Documentation – AMIB – MéxicoLATMex;MexicoMexicoMexico(MX)MEX
A472215Horizontal thematic audit - AML and Sanctions Training and AML and Sanctions Teams Qualifications - APAC, EMEA, LATAMAsia Pacific;EMEA;LATAM;LATMexAsean;CCA;CE5;EMEA EM;Europe;MENA;MENAPT;Middle East and Africa;SSA;Turkey, Russia, Ukraine, and Kazakhstan (TRUK);Western EuropeAlgeria(DZ);Czech(CZ);Egypt(EG);Greece(GR);Hungary(HU);Kazakhstan(KZ);Kenya(KE);Pakistan(PK);Panama(PA);Puerto Rico(PR);Romania(RO);Spain(ES);Sweden(SE);Trinidad & Tobago(TT);Viet Nam(VN)LATAM



1654193307169.png



Thank you,
 
Upvote 0
bebo021999 said:
Without sample data in a sample worksheet, its hard to test the results.
Anyway, try below code. If it did not work, try to upload a screenshot/image or XL2BB, with manual output
Assume your have name range "AccountName" for column B and "Region" for column C
VBA Code: 
Option Explicit
Sub test()
Dim i&, rngB, rngC, B As String, C As String, result()
rngB = Range("AccountName").Value: rngC = Range("Region").Value
ReDim result(1 To UBound(rngB), 1 To 1)
    For i = 1 To UBound(rngB)
        B = " " & UCase(rngB(i, 1)) & " ": C = UCase(rngC(i, 1))
        Select Case True
            Case B Like "* LATAM *" And (C Like "NAM" Or C Like "NORTH AMERICA")
                result(i, 1) = "NA": GoTo z
            Case B Like "* US *" Or B Like "* NAM *" Or B Like "* NAM,*"
                result(i, 1) = "NA": GoTo z
            Case B Like "* MEXICO *" Or B Like "* MEX *" Or B Like "* MÉXICO *"
                result(i, 1) = "MEX": GoTo z
            Case B Like "* LATAM *"
                result(i, 1) = "NA": GoTo z
            Case C Like "NAM" Or C Like "NORTH AMERICA"
                result(i, 1) = "NAM": GoTo z
            Case C Like "JAPAN"
                result(i, 1) = C: GoTo z
            Case B Like "* APAC *"
                result(i, 1) = "ASPAC": GoTo z
            Case C Like "MEXICO" Or C Like "EMEA"
                result(i, 1) = C: GoTo z
            Case C Like "ASIA PACIFIC" Or C Like "LATAM"
                result(i, 1) = C: GoTo z
            Case (B Like "* US *" Or B Like "* GLOBAL *") And Len(C) = 0
                result(i, 1) = "NA": GoTo z
            Case Else
                result(i, 1) = "NEED TO MANUALLY ENTER": GoTo z
z:
        End Select
    Next
With Range("D2").Resize(UBound(rngB), 1)
    .ClearContents
    .Value = result
End With
End Sub
Click to expand...
Hello,

I wasn't able to get the code to work. Are you able to test your code with the data I provided yesterday?


Thank you,
 
Upvote 0
bebo021999 said:
Without sample data in a sample worksheet, its hard to test the results.
Anyway, try below code. If it did not work, try to upload a screenshot/image or XL2BB, with manual output
Assume your have name range "AccountName" for column B and "Region" for column C
VBA Code: 
Option Explicit
Sub test()
Dim i&, rngB, rngC, B As String, C As String, result()
rngB = Range("AccountName").Value: rngC = Range("Region").Value
ReDim result(1 To UBound(rngB), 1 To 1)
    For i = 1 To UBound(rngB)
        B = " " & UCase(rngB(i, 1)) & " ": C = UCase(rngC(i, 1))
        Select Case True
            Case B Like "* LATAM *" And (C Like "NAM" Or C Like "NORTH AMERICA")
                result(i, 1) = "NA": GoTo z
            Case B Like "* US *" Or B Like "* NAM *" Or B Like "* NAM,*"
                result(i, 1) = "NA": GoTo z
            Case B Like "* MEXICO *" Or B Like "* MEX *" Or B Like "* MÉXICO *"
                result(i, 1) = "MEX": GoTo z
            Case B Like "* LATAM *"
                result(i, 1) = "NA": GoTo z
            Case C Like "NAM" Or C Like "NORTH AMERICA"
                result(i, 1) = "NAM": GoTo z
            Case C Like "JAPAN"
                result(i, 1) = C: GoTo z
            Case B Like "* APAC *"
                result(i, 1) = "ASPAC": GoTo z
            Case C Like "MEXICO" Or C Like "EMEA"
                result(i, 1) = C: GoTo z
            Case C Like "ASIA PACIFIC" Or C Like "LATAM"
                result(i, 1) = C: GoTo z
            Case (B Like "* US *" Or B Like "* GLOBAL *") And Len(C) = 0
                result(i, 1) = "NA": GoTo z
            Case Else
                result(i, 1) = "NEED TO MANUALLY ENTER": GoTo z
z:
        End Select
    Next
With Range("D2").Resize(UBound(rngB), 1)
    .ClearContents
    .Value = result
End With
End Sub
Click to expand...
Hello Bebo,

I was able to tweak your vba code to work like I need. It was close though. Thank you!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
VBA Loop to bring over data from multiple sheets using header and column data
Replies
4
Views
349
MHamid
M
Sufiyan97
  • Solved
Convert Formula to VBA
2
Replies
11
Views
877
Alex Blakenburg
A
J
  • Question
Work around script for when VBA array formula gives error 1004 Unable to set the FormulaArray property of the Range class
Replies
0
Views
568
johnnyL
J
M
  • Question
Macro Freezes
Replies
3
Views
1K
6StringJazzer
6StringJazzer
D
  • Question
VBA Loop to create Excel Sheet, save excel sheet to specific folder on computer and generate Outlook 365 Email with attached excel sheet.
Replies
1
Views
444
Fluff
Fluff

Forum statistics

Threads
1,214,798
Messages
6,121,630
Members
449,041
Latest member
Postman24

Share this page

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