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!

 

[jmthompson]

Try replacing "=" with "Like"

 

[Rick Rothstein]

Try replacing "=" with "Like"

I'm not 100% sure because the sample layout is not well spaced, but I think he may also have to move the asterisk to the end. It looks like the cell value is, as but one example, "Right side door", so instead of using Like against "*Right", it should be against "Right*".

 

[Nevadarain72]

Try replacing "=" with "Like"

Just tried that out, with no luck. All that results is the new column being created, with cell B1 containing "FALSE" and all the other cells blank.

 

[Nevadarain72]

I'm not 100% sure because the sample layout is not well spaced, but I think he may also have to move the asterisk to the end. It looks like the cell value is, as but one example, "Right side door", so instead of using Like against "*Right", it should be against "Right*".

My data may have the key words appear anywhere within the cell, so I believe that means I should have it listed as "*Right*", yes?

As for the spacing, I'm not skilled enough to know how the lines should be spaced/indented/etc, so I apologize for the poor layout.

 

[Rick Rothstein]

My data may have the key words appear anywhere within the cell, so I believe that means I should have it listed as "*Right*", yes?

Yes, correct. You should be aware, though, that the Like operator is case sensitive in its testing, so if you put "*Right*", it will miss the word "right in something like "the right side" because the "r" in "right" is not upper case. I would wrap the item being tested in a UCase function call and then test against "*RIGHT*" instead. Another possibility is to use the InStr function which allows for a case insensitive test... just test to see if the return value is greater than 0. For example...

If InStr(1, c.Value, "right", vbTextCompare) > 0 Then

As for the spacing, I'm not skilled enough to know how the lines should be spaced/indented/etc, so I apologize for the poor layout.

Not really your fault... I deal participate in several forums and it seems each handles spacing issues differently. My first inclination is to put several space (10 or so) between column data to try and make them stand apart; however, so forum comment processor "eat" the redundant spaces, so it is not a universal solution. If the forum provides for code tags, usually putting the data inside those tags will preserve spacing.

 

[Nevadarain72]

Yes, correct. You should be aware, though, that the Like operator is case sensitive in its testing, so if you put "*Right*", it will miss the word "right in something like "the right side" because the "r" in "right" is not upper case. I would wrap the item being tested in a UCase function call and then test against "*RIGHT*" instead. Another possibility is to use the InStr function which allows for a case insensitive test... just test to see if the return value is greater than 0. For example...

If InStr(1, c.Value, "right", vbTextCompare) > 0 Then

Not really your fault... I deal participate in several forums and it seems each handles spacing issues differently. My first inclination is to put several space (10 or so) between column data to try and make them stand apart; however, so forum comment processor "eat" the redundant spaces, so it is not a universal solution. If the forum provides for code tags, usually putting the data inside those tags will preserve spacing.

Got it. I'll try the InStr and see if that works in a moment, and report back.

 

[Nevadarain72]

Got it. I'll try the InStr and see if that works in a moment, and report back.

No luck here, but maybe I misunderstood. Here's what it looks like now:

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
       
    For Each c In Crng
       If InStr(1, c.Value, "right", vbTextCompare) > 0 Then
            c.Offset(0, -1).Value = c.Offset(0, -2).Value & ActiveCell.Value = "-002"
       ElseIf InStr(1, c.Value, "left", vbTextCompare) > 0 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

 

[Rick Rothstein]

The InStr part is correct, but I see problems with other parts of your code...

    Range(Crng, Crng.End(xlDown)).Select
 
    For Each c In Crng

You select a range (which includes the header by the way), but you do not iterate that selection, rather, you "iterate" the single cell containing the header word "Description". I think you want to change the Crng in the For statement to Selection, but first I would change the line that does the selecting to this...

Range(Crng.Offset(1), Crng.End(xlDown)).Select

so that you do not include the header in what you are iterating. By the way, you do not have to select the range in order to work with the selection.. you can use the range directly in the For statement if you want (it is considered better programming practice to do that).

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

This line of code is not clear to me. The ="-002" establishes a Boolean expression which ends up forcing the assignment of FALSE to the cell in the column you added. As I said, I'm not sure what your intent was here, so I don't really have a suggestion as to how to change it.

 

[Nevadarain72]

By the way, you do not have to select the range in order to work with the selection.. you can use the range directly in the For statement if you want (it is considered better programming practice to do that).

Hmm, interesting. Good to know, and I'm making a future note.

This line of code is not clear to me. The ="-002" establishes a Boolean expression which ends up forcing the assignment of FALSE to the cell in the column you added. As I said, I'm not sure what your intent was here, so I don't really have a suggestion as to how to change it.

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?