Seeking help with finding specific text in a Range.

[jagrenet]

Hello all,
I am looking to write a sub procedure that will count all rows then, search each row for the text "Event", in column B. When it finds "Event", I want it to look at column "AB" (for each Row in the Range) and if there is already a value in that cell, ignore it and move on. Conversely, if "AB" is empty for that Row, I want it to add "0.15" to the empty cell for me.
The worksheet varies in size each month so, it is dynamic. One month I might have 5 entries. The next month, there may be 372, etc.

I realize this is a fairly simple procedure but I have been running into problems trying to get it to work.
Thanks in advance,

Jeff

 

[Joe4]

Try this:

Sub MyMacro()

    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column B with data
    lr = Cells(Rows.Count, "B").End(xlUp).Row
    
'   Loop through all rows
    For r = 1 To lr
'       Check to see if word "Event" is found in column B
        If InStr(UCase(Cells(r, "B")), "EVENT") > 0 Then
'           Check to see if column AB is empty
            If Cells(r, "AB") = "" Then Cells(r, "AB").Value = 0.15
        End If
    Next r
    
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"
    
End Sub

 

[jagrenet]

Try this:

Sub MyMacro()

    Dim lr As Long
    Dim r As Long
   
    Application.ScreenUpdating = False
   
'   Find last row in column B with data
    lr = Cells(Rows.Count, "B").End(xlUp).Row
   
'   Loop through all rows
    For r = 1 To lr
'       Check to see if word "Event" is found in column B
        If InStr(UCase(Cells(r, "B")), "EVENT") > 0 Then
'           Check to see if column AB is empty
            If Cells(r, "AB") = "" Then Cells(r, "AB").Value = 0.15
        End If
    Next r
   
    Application.ScreenUpdating = True
   
    MsgBox "Macro complete!"
   
End Sub

That works great Joe4 !! - Is there a way to add to that, .... such as - If "AB" is blank or empty add "x.xx" time. This would be NOT dependent on any keyword or text.

 

[Joe4]

That works great Joe4 !! - Is there a way to add to that, .... such as - If "AB" is blank or empty add "x.xx" time. This would be NOT dependent on any keyword or text.

If the you remove the line:

        If InStr(UCase(Cells(r, "B")), "EVENT") > 0 Then

and the corresponding:

        End If

then it will just look at the value in column AB and not check the contents of column B first.

 

[jagrenet]

If the you remove the line:

        If InStr(UCase(Cells(r, "B")), "EVENT") > 0 Then

and the corresponding:

        End If

then it will just look at the value in column AB and not check the contents of column B first.

I understand. However, the "Event" keyword is somewhat critical to the operation also, I have a different value that goes in column "AB" if it is not an event. This is a report of Closed Tickets worked each month by our technicians. Some of the techs do not add their time worked, into the ticket. Not all tickets are Events. Some are "Incidents" others are "Service Requests" etc. For those tickets that are not Events I have differing values.

 

[Joe4]

I understand. However, the "Event" keyword is somewhat critical to the operation also, I have a different value that goes in column "AB" if it is not an event. This is a report of Closed Tickets worked each month by our technicians. Some of the techs do not add their time worked, into the ticket. Not all tickets are Events. Some are "Incidents" others are "Service Requests" etc. For those tickets that are not Events I have differing values.

Then I guess I don't understand what your follow-up question is.

Can you lay out ALL the conditions you have, and order of precedence, that needs to be followed?

Alternatively, you could provide a small example that covers all your different scenarios, and show us what should happen in each one.

 

[jagrenet]

Then I guess I don't understand what your follow-up question is.

Can you lay out ALL the conditions you have, and order of precedence, that needs to be followed?

Alternatively, you could provide a small example that covers all your different scenarios, and show us what should happen in each one.

I really like the way it is laid out currently, I just want to be able to add and identify, If Column "B" is NOT an Event .... and Column "AB" already has a value - skip it and move on,. But, if Column "B" is NOT an Event and column "AB" is empty ........ add "x.xx"

 

[Joe4]

I really like the way it is laid out currently, I just want to be able to add and identify, If Column "B" is NOT an Event .... and Column "AB" already has a value - skip it and move on,. But, if Column "B" is NOT an Event and column "AB" is empty ........ add "x.xx"

OK, that clarifies it. You want to do both things at once.

Just add an ELSE clause with another IF, i.e.

Sub MyMacro()

    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column B with data
    lr = Cells(Rows.Count, "B").End(xlUp).Row
    
'   Loop through all rows
    For r = 1 To lr
'       Check to see if word "Event" is found in column B
        If InStr(UCase(Cells(r, "B")), "EVENT") > 0 Then
'           Check to see if column AB is empty
            If Cells(r, "AB") = "" Then Cells(r, "AB").Value = 0.15
        Else
'           What to do if column B does NOT contain "Event" but column AB is empty
            If Cells(r, "AB") = "" Then Cells(r, "AB").Value = "x.xx"
        End If
    Next r
    
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"
    
End Sub

 

[jagrenet]

OK, that clarifies it. You want to do both things at once.

Just add an ELSE clause with another IF, i.e.

Sub MyMacro()

    Dim lr As Long
    Dim r As Long
   
    Application.ScreenUpdating = False
   
'   Find last row in column B with data
    lr = Cells(Rows.Count, "B").End(xlUp).Row
   
'   Loop through all rows
    For r = 1 To lr
'       Check to see if word "Event" is found in column B
        If InStr(UCase(Cells(r, "B")), "EVENT") > 0 Then
'           Check to see if column AB is empty
            If Cells(r, "AB") = "" Then Cells(r, "AB").Value = 0.15
        Else
'           What to do if column B does NOT contain "Event" but column AB is empty
            If Cells(r, "AB") = "" Then Cells(r, "AB").Value = "x.xx"
        End If
    Next r
   
    Application.ScreenUpdating = True
   
    MsgBox "Macro complete!"
   
End Sub

YES !!! - That is exactly what I was after. Bravo Joe4 !!! - Thank you for your help, it is MUCH appreciated.

 

[Joe4]

You are welcome.
Glad I was able to help!