需要找到一种方法来使此宏遍历其他所有列 [英] Need to find a way to loop this macro through every other column

问题描述

我编写了一个宏，该宏基于对另外两个单元格的串联值的vlookup返回一个单元格中的特定值.该宏可能需要每隔30列在其他每列中运行一次.有没有办法让我循环播放，这样就不必键入30种变化了?

I have written a macro that to return specific values in one cell based on a vlookup of the concatenated values of two other cells. This macro may need to run in every other column for 30 columns. Is there a way that I can loop this, so that I don't have to type out 30 variations?

这是我正在引用的代码部分:

Here is the section of code that I am referencing:

Cells.Select
ActiveWorkbook.Worksheets("Vendor Request").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Vendor Request").Sort.SortFields.Add Key:=Range( _
    "F2:F" & LR2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With ActiveWorkbook.Worksheets("Vendor Request").Sort
    .SetRange Range("A1:BK" & LR2)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
If Cells(2, 6).Value <> "" Then
Range("G2").Select
ActiveCell.FormulaR1C1 = _
    "=CONCATENATE(""Example:  "",IF(ISNA(VLOOKUP(CONCATENATE(RC[-4],""; "",RC[-1]),'Sample Data'!C[-6]:C[-2],5,FALSE)),"""",VLOOKUP(CONCATENATE(RC[-4],""; "",RC[-1]),'Sample Data'!C[-6]:C[-2],5,FALSE)))"
If Cells(3, 6).Value <> "" Then
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G" & Range("F" & Rows.Count).End(xlUp).Row)
Else
End If
Columns("G:G").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Else
End If

提前谢谢！

终于使它工作了.这就是我最终得到的:

Finally got it working. Here is what I ended up with:

Cells.Select
    ActiveWorkbook.Worksheets("Vendor Request").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Vendor Request").Sort.SortFields.Add Key:=Columns( _
        j - 1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Vendor Request").Sort
        .SetRange Range("A1:BK" & LR2)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Dim LRj As Long
    With Sheets("Vendor Request")
    LRj = .Cells(.Rows.Count, j - 1).End(xlUp).Row
    End With
    If Cells(2, j - 1).Value <> "" Then
    Cells(2, j).Select
    ActiveCell.FormulaR1C1 = _
    "=CONCATENATE(""Example:  "",IF(ISNA(VLOOKUP(CONCATENATE(c3,""; "",C[-1]),'Sample Data'!c1:c6,5,FALSE)),"""",VLOOKUP(CONCATENATE(c3,""; "",C[-1]),'Sample Data'!c1:c6,5,FALSE)))"
    If Cells(3, j - 1).Value <> "" Then
    Cells(2, j).Select
    Selection.AutoFill Destination:=Range(Cells(2, j), Cells(LRj, j))
    Else
    End If
    Columns(j).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Else
    End If
    Next j

推荐答案

您可以将... Range("G2:G ...更改为Range("G2:AK，将Columns("G:G"更改为Columns("G:AK"

you can change ...Range("G2:G... to Range("G2:AK and Columns("G:G" to Columns("G:AK"

或Range(Cells(2,j),Cells(...,j))在for循环内

这篇关于需要找到一种方法来使此宏遍历其他所有列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！