PDA

View Full Version : Search in every column for a value in a specific row, then insert a column before IF



manueslapera
05-18-2012, 12:12 PM
Hi!, I am pretty new to vba, and I am trying to write a macro that performs the following:

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 a word, then Insert two columns before the selected column

4. Perform the same for every column in the sheet. (i mean, those columns where there is something, I know that there are an infinite amount of potential columns).

Any help or direction toward an answer would be more than helpful. I could also upload a sample file if that helps.

Tinbendr
05-18-2012, 01:06 PM
Try this.
Sub Test()
Dim WB As Workbook
Dim eWS As Worksheet
Dim Lastcol As Long
Dim MyWord As String
MyWord = "gold"
Set WB = ActiveWorkbook
For Each eWS In WB.Worksheets
With eWS
Lastcol = .Range("IV5").End(xlToLeft).Column
For A = Lastcol To 1 Step -1
If eWS.Cells(5, A).Value = MyWord Then
eWS.Columns(A).EntireColumn.Insert
eWS.Columns(A).EntireColumn.Insert
End If
Next
End With

Next
End Sub