Thread: For Loop and search

Thanks. It should be corrected now. My apologies.

One correction, I should have not been explicit with the column number in the original post. I need the code to search for a specific column named "Name name". I am having additional problems finding a column with a space in the name. I will try to implement the other aspect as you've described. Thanks.

bah Mordred beat me to it. I used a loop on the column search just to show a different way. Here is mine:

Sub MainMacro()
Dim ws1 As Worksheet:   Set ws1 = Sheets("Sheet1")
Dim lastrow As Long, lastcol As Long, icol As Long, mycol As Long
Dim c As Range, iFind As Range
Dim startaddress As String

lastcol = ws1.Cells(1, Columns.Count).Column

For icol = 1 To lastcol
    If ws1.Cells(1, icol).Value = "Name name" Then
        mycol = icol
        Exit For
    End If
Next icol

lastrow = ws1.Cells(Rows.Count, mycol).End(xlUp).Row

Set iFind = ws1.Range(Cells(2, mycol), Cells(lastrow, mycol))
With iFind
    Set c = .Find(what:="text", LookAt:=xlWhole, LookIn:=xlValues, SearchDirection:=xlNext)
    If Not c Is Nothing Then
        c.Offset(0, -1).Value = "text2"
        startaddress = c.Address
        Do
            c.Offset(0, -1).Value = "text2"
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> startaddress
    End If
End With
End Sub

I was thinking more along the lines of the column search as well during my attempted implementation so I used your approach stnkynts, thanks for the help though Mordred. Anyway, I am getting and error at the definition of "lastrow". Any ideas?

I think it has something to do with mycol not getting defined properly. Here is the code now.

Dim wsA As Worksheet:   Set wsA = Sheets("Status")
Dim lastrow As Long, lastcol As Long, icol As Long, mycol As Long
Dim c As Range, iFind As Range
Dim startaddress As String

lastcol = wsA.Cells(1, Columns.Count).Column

For icol = 1 To lastcol
    If wsA.Cells(3, icol).Value = "EMS Composite Name" Then
        mycol = icol
        Exit For
    End If
Next icol

'lastrow = wsA.Cells(Rows.Count, mycol).End(xlUp).Row
lastrow = 120

Set iFind = wsA.Range(Cells(2, mycol), Cells(lastrow, mycol))
With iFind
    Set c = .find(what:="spare", LookAt:=xlWhole, LookIn:=xlValues, SearchDirection:=xlNext)
    If Not c Is Nothing Then
        c.Offset(0, -1).Value = "spare"
        startaddress = c.Address
        Do
            c.Offset(0, -1).Value = "spare"
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> startaddress
    End If
End With

ok. I found out why it was causing problems, it never found the header name I was using since I forgot i changed it on the test case. The code is running now, but it never ends and doesn't appear to be doing anything. I changed it to look at the third row for the column header name. Did I do this correctly?

Hmmm. Is your header name "EMS Composite Name" in row 3? If you could attach a copy of your workbook I could take a look at it to see where the gum up is.

I just noticed an error that i made which might be slowing things down.
Change this:

lastcol = wsA.Cells(1, Columns.Count).Column

To this:

lastcol = wsA.Cells(1, Columns.Count).End(xlToLeft).Column

I apologize for this mistake.