Looking for help with VBA or macro

Dear @James,

Thank you for posting your question in the Microsoft Q&A forum.

Thank you for sharing your requirements regarding the address-based data entry and analysis workflow.

To streamline the process, VBA-based automation within Excel is a right solution since using VBA (Visual Basic for Applications) is a solid approach for automating this kind of task in Excel. This solution will allow you to:

• Loop through each address in your input list (Column A),
• Inject each address into your internal modeling workbook one at a time,
• Capture the resulting outputs generated by your analysis system,
• And publish those results directly next to the corresponding address for easy evaluation.

This approach ensures that your modeling logic remains intact while enabling efficient batch processing of multiple addresses.

Before giving you the best solution, could you please confirm these questions below to help me diagnose the issue more effectively: 

• Where in the workbook do the results appear after an address is processed? Are they in fixed cells (e.g., always in Column B–E), or do they vary?
• What kind of results does the model produce? (e.g., numerical scores, categories, flags, multiple fields?)
• Please kindly provide me an example file or example screenshot for your description for better understanding and then I will try my best to give you the best solution.

In the meantime, since I do not have enough information that can exactly provide you an instruction, here are some steps I recommend you look through:

Step 1: Prepare Your Files

Data Entry Workbook: Create an Excel file (e.g., Addresses.xlsx) with a sheet named Addresses. In column A, list your addresses starting from cell A2 (A1 is the header "Address").

• Analysis Workbook: Create another Excel file (e.g., AnalysisModel.xlsx) with:
  • An input cell for the address (e.g., InputData!B2)
  • Output cells for results (e.g., OutputData!D2:F2)

Step 2. Open the VBA Editor

In your Data Entry Workbook, press Alt + F11 to open the VBA editor.

Insert a new module (Insert > Module).

Step 3. Paste the Macro

• Copy and paste the following code into the module. Adjust sheet names, cell references, and file paths as needed.

Sub BatchProcessAddresses()
    Dim analysisWB As Workbook
    Dim wsInput As Worksheet, wsOutput As Worksheet
    Dim lastRow As Long, r As Long
    Dim addr As String
    Dim resultsRange As Range

    Set wsInput = ThisWorkbook.Sheets("Addresses") ' Change as needed
    Set wsOutput = wsInput
    lastRow = wsInput.Cells(wsInput.Rows.Count, "A").End(xlUp).Row

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim calcMode As XlCalculation
    calcMode = Application.Calculation
    Application.Calculation = xlCalculationManual

    Set analysisWB = Workbooks.Open("C:\Path\To\AnalysisModel.xlsx", ReadOnly:=True)

    For r = 2 To lastRow
        addr = wsInput.Range("A" & r).Value
        If addr = "" Then Exit For
        analysisWB.Sheets("InputData").Range("B2").Value = addr
        Application.Calculate
        Set resultsRange = analysisWB.Sheets("OutputData").Range("D2:F2")
        wsOutput.Range("B" & r & ":D" & r).Value = resultsRange.Value
    Next r

    analysisWB.Close SaveChanges:=False
    Application.Calculation = calcMode
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    MsgBox "Completed processing " & (lastRow - 1) & " addresses.", vbInformation
End Sub

Step 4: Save Your Workbook

Save your Data Entry Workbook as a Macro-Enabled Workbook (.xlsm).

Step 5. Run the Macro:

• Press Alt + F8, select BatchProcessAddresses, and click Run.

You can now inject addresses one by one into your analysis workbook and have the results published next to each address for easy evaluation.

I hope this information is helpful. Please follow these steps and let me know if it works for you. If not, we can work together to resolve this.

Thank you for your patience and your understanding. If you have any questions, please feel free to reach out.

I'm looking forward for your reply.

If my answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".   

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.