基于表头和前置列设置单元格区域值的VBA实现求助

Hey there! Let's get this VBA sorted out for you. I see you want to replicate that CONCATENATE formula behavior where each cell in your green highlighted range (G19:AB21) combines the values from columns A/B of the same row plus the corresponding column header. Your existing code has started defining the right ranges, so let's build on that with the core logic you're missing.

Step-by-Step Solution Code #

Here's a revised version of your code that will do exactly what you need:

Sub Get_Keys()
    Dim Current As Workbook
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim headersRange As Range
    Dim cell As Range
    Dim rowNum As Long
    Dim colNum As Long
    Dim headerValue As String
    Dim aValue As String
    Dim bValue As String
    
    ' Turn off screen updating and alerts to speed things up
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    ' Set references to your workbook and worksheet (avoids relying on ActiveWorkbook)
    Set Current = ThisWorkbook ' Use ThisWorkbook if the code is in the same file as your data
    Set ws = Current.Worksheets("Sheet1")
    
    ' Define your header range (columns G to AB, rows 14-15) and data range (green highlighted area)
    Set headersRange = ws.Range("G14:AB15")
    Set dataRange = ws.Range("G19:AB21")
    
    ' Loop through each cell in your data range
    For Each cell In dataRange
        ' Get the row number of the current cell to match with A/B columns
        rowNum = cell.Row
        ' Get the column number to match with the header range
        colNum = cell.Column - headersRange.Column + 1 ' Adjust to map data column to header column
        
        ' Get values from A, B, and the corresponding header
        aValue = ws.Cells(rowNum, "A").Value
        bValue = ws.Cells(rowNum, "B").Value
        ' If headers are merged cells, .Cells(1, colNum) will grab the correct value
        headerValue = headersRange.Cells(1, colNum).Value
        
        ' Concatenate the values and assign to the current cell
        cell.Value = aValue & bValue & headerValue
    Next cell
    
    ' Turn screen updates and alerts back on
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

Key Explanations #

• Range References: Instead of relying on ActiveWorkbook, we use ThisWorkbook to make sure we're always working with the file containing the code (safer if you have multiple workbooks open).
• Loop Through Cells: The For Each cell In dataRange loop goes through every cell in your green area one by one.
• Matching Rows/Columns: We use cell.Row to get the same row for columns A/B, and adjust the column number to pull the right header from your headersRange.
• Concatenation: Simple string concatenation with & does the same job as your CONCATENATE formula—you can add separators (like hyphens or spaces) if needed, e.g., aValue & "-" & bValue & "-" & headerValue.

Learning Tips to Build on This #

If you want to dive deeper into VBA for Excel, focus on these basics first:

• Range Object: Learn how to define ranges, reference cells within ranges, and use properties like .Row, .Column, and .Value.
• Loops: For Each loops are perfect for iterating through cells, while For loops work great for row/column numbers.
• Application Settings: Turning off ScreenUpdating and DisplayAlerts is a common trick to make your macros run faster and avoid popups.

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