# Seeking help with finding specific text in a Range.



## jagrenet (Dec 27, 2022)

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 (Dec 27, 2022)

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 (Dec 27, 2022)

Joe4 said:


> Try this:
> 
> ```
> Sub MyMacro()
> ...


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 (Dec 27, 2022)

jagrenet said:


> 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 (Dec 27, 2022)

Joe4 said:


> If the you remove the line:
> 
> ```
> If InStr(UCase(Cells(r, "B")), "EVENT") > 0 Then
> ...


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 (Dec 27, 2022)

jagrenet said:


> 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 (Dec 27, 2022)

Joe4 said:


> 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 (Dec 27, 2022)

jagrenet said:


> 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 (Dec 27, 2022)

Joe4 said:


> OK, that clarifies it.  You want to do both things at once.
> 
> Just add an ELSE clause with another IF, i.e.
> 
> ...


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


----------



## Joe4 (Dec 27, 2022)

You are welcome.
Glad I was able to help!


----------

