VBA Macro to Find Text in One Column and Replace Text in Offset Column

[truebluewoman]

I am using Excel 2013 in Windows 7 Professional and need to do the following:

In Column GM, I have a property identification #.
In Column GI, I have a company identification #.

For every instance that the value 834 applears in column GM, I need a macro to enter the word "PROP 20" in the same row of Column GI. I have another group of similar items, but if I can figure the code for the one, I can repeat the same task for the other items.

I have been working on this for days and I am at my wit's end. I have been able to get ONE instance to happen, but I can't seem to get it to repeat until it runs through the entire list. I don't know a lot about VBA, but I've been able to construct a partial solution. Here is the code I have so far:

Sub Extract_Preparation()
'
' Macro that replaces PROP with PROP 20 and Yardi for properties 834,705, and 710 then
' sorts data by company, then by expense report #.
Dim colRange As Range
Dim cl As Range
Set colRange = Sheets("Extract Data").Range(Range("GM12"), Range("GM12").End(xlDown))
Set RngFound = Range("GM:GM").Find(What:=834, LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=True)

For Each Found In colRange.Cells

RngFound.Offset(0, -4).Value = "PROP 20"

Next

Exit Sub

MsgBox "Extract Data Formatting Complete. You may now proceed to create the individual import files."

End Sub

Can anyone help me write the remaining code in order to repeat my function until it's complete? Thank you!

 

[steve the fish]

Try this:

Sub Extract_Preparation()
'
' Macro that replaces PROP with PROP 20 and Yardi for properties 834,705, and 710 then
' sorts data by company, then by expense report #.
Dim colRange As Range
Dim cl As Range
Set colRange = Sheets("Extract Data").Range(Range("GM12"), Range("GM12").End(xlDown))



For Each cell In colRange.Cells

If cell.Value = 834 Then

    cell.Offset(0, -4).Value = "PROP 20"

End If

Next


MsgBox "Extract Data Formatting Complete. You may now proceed to create the individual import files."

End Sub

 

[truebluewoman]

Try this:

Sub Extract_Preparation()
'
' Macro that replaces PROP with PROP 20 and Yardi for properties 834,705, and 710 then
' sorts data by company, then by expense report #.
Dim colRange As Range
Dim cl As Range
Set colRange = Sheets("Extract Data").Range(Range("GM12"), Range("GM12").End(xlDown))



For Each cell In colRange.Cells

If cell.Value = 834 Then

    cell.Offset(0, -4).Value = "PROP 20"

End If

Next


MsgBox "Extract Data Formatting Complete. You may now proceed to create the individual import files."

End Sub

Unfortunately, this did not work. The only item that worked was the Msg Box, but no text was changed.

 

[steve the fish]

If 834 is present in GM12 then the code will put PROP 20 in GI12. Is that not correct?

 

[truebluewoman]

That is correct; however, the 834 doesn't just appear in GM 12. It appears throughout column GM. I just tested the process again, by putting an 834 only in cell GM12 and it did work for line 12. I need it to do it for every row where 834 appears in column GM. I think this tells me that the problem is in the setColRange parameters, right?

 

[steve the fish]

Yes i presume there must be gaps? So xldown wont work.

Try:

Set colRange = Sheets("Extract Data").Range(Range("GM12"), <code>Range("GM" & .Rows.Count).End(xlUp).Row</code>)</pre>

 

[truebluewoman]

I'm getting Compile error: Invalid or unqualified reference. It's highlighting the ("GM" & .Rows.Count) portion of the code.

 

[steve the fish]

Sorry this be better

Set colRange = Sheets("Extract Data").Range("GM12:GM" & Range("GM" & Rows.Count).End(xlUp).Row)

 

[truebluewoman]

"Steve the fish", YOU are my new best friend!!!! Thank you, thank you, thank you a million times from the bottom of my heart! I will toast in your name tonight and the rest of this year for getting this to work

 

[steve the fish]

Glad it works