CODEWITHSUNDEEP
vbaexcel
LillieG
LillieG

Reputation: 75

Excel VBA: how to apply code when it finds text in a column

I have the following code, modified by @FreeMan from one of my previous questions. I want to find the text "Hours" in any row in the worksheet. Then, apply the code to the column containing that text. This code is supposed to do that, but it does not work for me for some reason. I would really appreciate your help with this. Thank you in advance.

Sub CeldasinInfo()

Dim i As Long, r As Range, coltoSearch As String
Dim Result as String
Dim ErrCount as integer

ErrCount = 0
coltoSearch = "A"
coltoSearch = Range("1:1").find(What:="Hours", LookIn:=xlValues,     LookAt:=xlWhole).Column

Result = "No Value in:" & vbcrlf
For i = 1 To Range(coltoSearch & Rows.Count).End(xlUp).Row
Set r = Range(coltoSearch & i)
If Len(r.Value) = 0 Then
     r.Interior.ColorIndex = 3 ' Red
      r.Select
      MsgBox "No Value, in " & r.Address
      Result = Result & r.Address & vbcrlf
      ErrCount = ErrCount + 1
      if ErrCount Mod 10 = 0 then    'change to 15 or 20 or whatever works well
        MsgBox Result
        Result = "No Value in:" & vbcrlf
      End If
      Sheets("Results").Range("A" & Sheets("Results").Range("A" &   Rows.Count).End(xlUp).Row).Offset(1, 0).Formula = r.Address
End If
Next

If ErrCount > 0 then
MsgBox "There were " & ErrCount & " errors detected." & vbcrlf & result
else
MsgBox "No errors detected"
End If
End Sub

Upvotes: 0

Views: 106

Answers (1)

Mr. Mascaro
Mr. Mascaro

Reputation: 2733

You need to change these two lines of code:

For i = 1 To Range(coltoSearch & Rows.Count).End(xlUp).Row
Set r = Range(coltoSearch & i)

to:

For i = 1 To Cells(Rows.Count, coltoSearch).End(xlUp).Row
Set r = Cells(i, coltoSearch)

Remove line: coltoSearch = "A"

coltoSearch should be an integer.

Upvotes: 1

Related Questions