Adapt VBA code where "Find" is equal to a MID function (etc)

[GTS]

Hello

I'm trying to adapt the following to do something very similar, but struggling to get it to work.
The following code finds the word "TOTAL" (exactly) in a cell in Column A and then sets the height of the row below to 2.

1) I have a requirement where I need to find cells in Column A where the formula LEN(A1) = 9 and LEFT(A1,2) = "RI" or "RE" or "RW".

    Dim c As Range
    Dim FirstAddress As String
    
    Set c = Columns("A").Find(What:="TOTAL", LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True)
    If Not c Is Nothing Then
        FirstAddress = c.Address
        Do
            c.Offset(1, 0).RowHeight = 2
            Set c = Columns("A").FindNext(c)
        Loop While Not c Is Nothing And c.Address <> FirstAddress
    End If

Appreciate any assistance. Thank you.

 

[GTS]

Apologies... forgot to edit the title before posting (and seems that cannot be edited?). Dealing with LEFT, not MID function.

 

[dreid1011]

Hello

I'm trying to adapt the following to do something very similar, but struggling to get it to work.
The following code finds the word "TOTAL" (exactly) in a cell in Column A and then sets the height of the row below to 2.

1) I have a requirement where I need to find cells in Column A where the formula LEN(A1) = 9 and LEFT(A1,2) = "RI" or "RE" or "RW".

    Dim c As Range
    Dim FirstAddress As String
   
    Set c = Columns("A").Find(What:="TOTAL", LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True)
    If Not c Is Nothing Then
        FirstAddress = c.Address
        Do
            c.Offset(1, 0).RowHeight = 2
            Set c = Columns("A").FindNext(c)
        Loop While Not c Is Nothing And c.Address <> FirstAddress
    End If

Appreciate any assistance. Thank you.

Where does the new requirement go in relation to the rest of the code?

 

[dreid1011]

Also, can you show the rest of the code? This little bit doesn't seem to do anything when I test it.

Edit: Nevermind, FIND is case sensitive.

 

[GTS]

This is part of a much larger macro. The adaptation I'm looking for would be a new segment of code. It would not 'fit in' to what I'm showing.
The snippet of code shown goes down Col A and wherever it finds the word "TOTAL" exactly in a cell, it will set the height of the row below to 2.

What I'm trying to do is to go down Col A and find any entry that starts with RI (or RE or RW) and if that entry is exactly 9 characters in length, then the row below is to be set to a height of 2.

Thanks.

 

[dreid1011]

Ok, this should do it. Just make sure you change the variable "i" if you are already using it somewhere else in your code.

Dim i As Long
For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
    If Left(Range("A" & i).Value, 2) = "RI" Or Left(Range("A" & i).Value, 2) = "RE" Or Left(Range("A" & i).Value, 2) = "RW" Then
        If Len(Range("A" & i).Value) = 9 Then
            Range("A" & i).Offset(1, 0).RowHeight = 2
        End If
    End If
Next i

 

[GTS]

Thanks dreid1011. Looks like that should work nicely. I have to shut down now and won't get to try this until next week. I'll return if I have any issues.
I was able to finally modify the code I posted (using Find instead of Left) but your solution should be more robust and faster I suspect. Thanks again.