Search for text, then insert value in previous cell

[solidENM]

Hello,
I need some vba that will search sheet1 for specific text in col C, then insert value of 1 into that rows B column.

I tried the manual recording, but that would not step over to previous column. It defines the exact cell i clicked on.


example: search col C (header is description) for word "wood," then insert value 1 into col b (header is qty). this must continue on to find all matches like this. Most files will have 5-10 lines with wood, but it varies.

 

[solidENM]

I was able to progress on this one a bit. I still need some help with it. The code below will search column D for "wood," and then update qty to 1 in column B.

I now need to create an alternate that will search for multiple words so i do not have to create a dozen lines of this same code.

working code below:

' set wood qty to 1
lRow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 3) Like "*" & "Wood" & "*" Then
Cells(i, 2) = Cells(i, 1)
End If
Next



----------------------------------------

work in progress below (does not work. I need the modifier that allows multiple if or options. I tried Or, comma, &, and no luck yet. )
Next
' set Panels qty to 1
lRow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
For i = 2 To lRow
strArray = Array("*1/8*", "*1/4*", "*3/4*", "*3/8*", "*3/16*", "1/2")
Cells(i, 2) = Cells(i, 1)

 

[Fluff]

How about

Sub FndValu()
'solidENM

    Dim lrow As Long
    Dim Cnt As Long
    Dim StrArray As Variant
    Dim Arr As Variant
    
    lrow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
    StrArray = Array("[COLOR=#ff0000]FOXG1[/COLOR]", "[COLOR=#ff0000]TCF4[/COLOR]", "[COLOR=#ff0000]SCN8A[/COLOR]")
    
    For Cnt = 2 To lrow
        For Each Arr In StrArray
            If Cells(Cnt, 5) Like Arr Then
                Cells(Cnt, 2) = Cells(Cnt, 1)
            End If
        Next Arr
    Next Cnt

End Sub

Changing the values in red to suit

 

[solidENM]

Hey Fluff,
Im not having and luck with your code.

this is my header
A item # B: Qty. C: Description D: Part Number E-H: random items not relevant

With my original code that was working, i found out it only worked for one line item, and would not loop through the remaining lines. I took mine out and tried yours, and am now getting no resulting changes at all.


** udpate:: I didnt include the ** before and after the search variables. its back to where I was though. This is not looping through the entire sheet. Its stopping after the first instance. How would i get this to alter all column b matches to qty 1?

 

[Fluff]

Apologies, when testing I was running on col E & forgot to change it to col C.
Try this, I've also changed how it sets the lrow

Sub FndValu()
'solidENM
    Dim lrow As Long
    Dim Cnt As Long
    Dim StrArray As Variant
    Dim Arr As Variant
    
    lrow = Cells.Find("*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    StrArray = Array("FOXG1", "TCF4", "SCN8A")
    For Cnt = 2 To lrow
        For Each Arr In StrArray
            If Cells(Cnt, 3) Like Arr Then
                Cells(Cnt, 2) = Cells(Cnt, 1)
            End If
        Next Arr
    Next Cnt

End Sub

 

[solidENM]

Thanks Fluff, but its still stopping at the first instance. Heres a sample of my excel sheet. the code is changing b2 to qty 1, but none of the rest. I have the 3/16 and 1/4 modifiers in the next column-- i had copied and increased the column count by 1 and still couldn't get those to change to qty 1. It looks like it may be numbering then in ascending order.


[TABLE="width: 360"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD]ITEM NO.[/TD]
[TD]QTY.[/TD]
[TD]Description[/TD]
[TD]Part Number[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[TD]1.5x1.5 Profile[/TD]
[TD]ka040[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]6[/TD]
[TD]2x2 Profile[/TD]
[TD]ka055[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]901.50 x 709.50[/TD]
[TD]3/16 clear[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[TD]1004 x 305[/TD]
[TD]1/4 grey [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]0[/TD]
[TD]1.5x1.5 end cap[/TD]
[TD]256992[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]0[/TD]
[TD]8x25 screw[/TD]
[TD]266548[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]0[/TD]
[TD]cover strip[/TD]
[TD]25856[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Try this

Sub FndValu()
'solidENM
    Dim lrow As Long
    Dim Cnt As Long
    Dim StrArray As Variant
    Dim Arr As Variant
    
    lrow = Cells.Find("*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    StrArray = Array("3/16", "1/4")
    For Cnt = 2 To lrow
        For Each Arr In StrArray
            If Cells(Cnt, 4) Like "*" & Arr & "*" Then
                Cells(Cnt, 2) = Cells(Cnt, 1)
            End If
        Next Arr
    Next Cnt

End Sub

 

[solidENM]

I appreciate the help Fluff. This is renaming the col B (Qty) cell to the rows line #. Im trying to read through this code to figure out what would be causing it to run a numerical list instead of a repeating qty 1, but im not having any luck.

 

[Fluff]

I was simply using the code you said worked. If you simply want col B to have 1 then use this

If Cells(Cnt, 4) Like "*" & Arr & "*" Then
     Cells(Cnt, 2) = 1
End If

 

[solidENM]

that worked like a Charm! Thanks for all the help Fluff!

I had found the original code on another post, modified it as best I could before posting. Did not know the latter half of that end segment was messing with the end result. Thanks for the lesson.