对于工作表中的每个列搜索单元格中的值，然后选择该列，然后在该列之前插入一列 [英] For every column in a sheet search for a value in a cell, then select that column and insert a column before that one

问题描述

我正在尝试编写一个执行以下操作的宏：

I was trying to write a macro that performed the following:

对于工作簿中的每个工作表：
1.选择列
2.在所选列中搜索行编号5
中的特定值3.如果该值与另一个cel（A1）匹配，则在选定列之前插入两列

For every sheet in the workbook: 1. Select a column 2. Search in the selected column for a specific value in the Row number 5 3. If that value matches another cel (A1), then Insert two columns before the selected column

1. 对表单中的每一列执行相同操作。 （我的意思是，那些有什么东西的列，我知道有无数潜在的列）。

任何帮助或朝向一个答案的方向将会更有帮助。

Any help or direction toward an answer would be more than helpful.

推荐答案

我会想到你会得到什么。

This will get you what you are after, I think.

Dim wks As Worksheet

For Each wks In ThisWorkbook.Worksheets

    With wks
        Dim intCol As Integer, intCnt As Integer

        intCol = .UsedRange.Columns.Count

        For intCnt = intCol To 2 Step -1 'assumes you ignore col A since your match value is there
            If .Cells(5, intCnt) = .Cells(1, 1) Then
                .Range(.Cells(1, intCnt), .Cells(1, intCnt + 1)).EntireColumn.Insert Shift:=xlToLeft
            End If
        Next

    End With

Next

这篇关于对于工作表中的每个列搜索单元格中的值，然后选择该列，然后在该列之前插入一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！