循环中结合VBA Offset函数遇问题，求批量提取多搜索词数据的宏

Got it, let's fix this looping + Offset issue once and for all. I’ve struggled with similar multi-column extraction tasks before, so I know exactly how to structure this to handle all your survey values in one go.

First, Let’s Define the Setup (Tweak These to Match Your Sheet!) #

Before diving into code, let’s align on assumptions (you can adjust these ranges as needed):

• Raw Indicators: C6:C50 (where your survey value labels live)
• Values to Extract: Let’s say the actual survey numbers are in column D (same row as the indicator; adjust if your values are in a different column)
• Search Terms: Let’s store all 13 survey values you need to find in F6:F18 (one per cell, 13 total for your 13 target columns)
• Target Columns: Starting at J6 (column 10), then M6 (column 13), P6 (column 16), etc. (each target column is 3 columns apart—adjust the step if your target columns are spaced differently)

The Macro Code #

Sub ExtractAllSurveyValues()
    Dim ws As Worksheet
    Dim rawIndicators As Range
    Dim searchTerms As Range
    Dim targetCol As Integer
    Dim searchTerm As Variant
    Dim indicatorCell As Range
    Dim targetRow As Integer
    
    ' Set your worksheet (change "Sheet1" to your actual sheet name)
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Define ranges
    Set rawIndicators = ws.Range("C6:C50")
    Set searchTerms = ws.Range("F6:F18") ' 13 search terms for 13 target columns
    
    ' Start at first target column (J = column 10)
    targetCol = 10
    
    ' Loop through each search term
    For Each searchTerm In searchTerms
        ' Reset target row to start at row 6 for each new search term
        targetRow = 6
        
        ' Loop through each indicator in the raw data
        For Each indicatorCell In rawIndicators
            ' Check if the indicator matches the current search term
            If indicatorCell.Value = searchTerm Then
                ' Use Offset to get the corresponding value (here, we're offsetting 1 column to the right—adjust as needed!)
                ' Then paste it into the current target column and row
                ws.Cells(targetRow, targetCol).Value = indicatorCell.Offset(0, 1).Value
                ' Move to next row in the target column
                targetRow = targetRow + 1
            End If
        Next indicatorCell
        
        ' Move to the next target column (3 columns over: J → M → P...)
        targetCol = targetCol + 3
    Next searchTerm
    
    MsgBox "All survey values extracted successfully!", vbInformation
End Sub

How This Works (And How to Tweak It) #

• Worksheet Setup: Replace "Sheet1" with your actual sheet name if needed.
• Value Extraction: The Offset(0,1) grabs the value 1 column to the right of the indicator (column D). If your values are in, say, column B, change this to Offset(0,-1).
• Target Column Spacing: The targetCol = targetCol + 3 jumps from J (10) to M (13). If your target columns are adjacent (like J, K, L...), change this to +1.
• Search Term Range: If you have more/less than 13 search terms, adjust F6:F18 to match your actual search term list.

Troubleshooting Tips #

• If no values are showing up: Double-check that your search terms match the indicators exactly (case-sensitive? Use UCase(indicatorCell.Value) = UCase(searchTerm) if you need case-insensitive matching).
• If values are going to the wrong rows: Make sure targetRow resets to 6 at the start of each search term loop.
• If you need to overwrite existing data in target columns: Add ws.Range(ws.Cells(6, targetCol), ws.Cells(50, targetCol)).ClearContents right before the For Each indicatorCell loop to clear old data first.

Hope this solves your problem—let me know if you need to adjust anything for your specific sheet layout!

内容的提问来源于stack exchange，提问作者the_ant