Excel VBA Find Column, search text

[Nevadarain72]

Hi all! I've been a "lurker" for a while now, reading other threads and using their information, but this is my first actual post. I don't know enough about Excel VBA to write code from memory, but I'm usually pretty good about reading someone else's code and figuring out how to modify it to suit my needs. Except here...

I have a spreadsheet that I want to do the following:
-Find the column that has the header "Description"
-Insert a column before it
-Analyze each cell in the "Description" column for certain text, and then do the following:
-If cell C2 (for example) contains the word "Right", then take the value in cell A2, add some trailing digits, and paste the entire new number in B2
-Repeat above, but search for different text:

Model _blank_ Description
12345 12345-002 Right side door
98765 98765-001 Left side door

Here's what I've pieced together so far:

Sub MaaxModelNumber()
'
' MaaxModelNumber Macro
'

Cells.Find(What:="Description", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.EntireColumn.Insert
Dim c As Range
Dim Crng As Range

Set Crng = Range("A1:Z1").Find("Description")

If Crng Is Nothing Then _
MsgBox "Description column was not found."
Range(Crng, Crng.End(xlDown)).Select

Set Crng = Range("A1:Z1").Find("Description")

For Each c In Crng
If c.Value = "*Right" Then
c.Offset(0, -1).Value = c.Offset(0, -2).Value & ActiveCell.Value = "-002"
ElseIf c.Value = "*Left" Then
c.Offset(0, -1).Value = c.Offset(0, -2).Value & ActiveCell.Value = "-001"
Else
c.Offset(0, -1).Value = c.Offset(0, -2).Value & ActiveCell.Value = "-000"
End If
Next c

End Sub

I've gotten it to do the first part, but the second half (the text search) doesn't seem to do anything when put together. When I run just the text search as a separate macro, it fills column B with "FALSE" in all cells. I'm running Office 2003 on Win7.

Any assistance provided would be hugely appreciated!

 

[Rick Rothstein]

My intent is that the macro searches a cell for specific text, and if it finds it, to perform the equivalent of =Concatenate(A2&"-002"). So if 12345 was in cell A2, and if cell C2 contained "Right", then in the new column it would put "12345-002". Basically a text-sensitive append function. Does that make more sense?

In that case, this line of code...

c.Offset(0, -1).Value = c.Offset(0, -2).Value & ActiveCell.Value = "-002"

should be this instead...

c.Offset(0, -1).Value = c.Offset(0, -2).Value & "-002"

You would change the other lines similarly.

 

[Nevadarain72]

In that case, this line of code...

c.Offset(0, -1).Value = c.Offset(0, -2).Value & ActiveCell.Value = "-002"

should be this instead...

c.Offset(0, -1).Value = c.Offset(0, -2).Value & "-002"

You would change the other lines similarly.

And just like that, the heavens opened up and light shone through. It works! It might be a little clunky, but it works. Rick, you are AWESOME. ray:

Thank you everyone for the insights!