Find a word in a cell and fill in another cell.

[viggie79]

I have a sheet with a column that will contain 1 of 3 words sales, spot, demo. I want to search through this column and depending on which word I find enter another value in another cell.

I'm using a do while loop to go down through the cell I want to write in, and searching the column 3 back by offsetting. ( I understand this is not ideal but my sheets aren't that big and I can't make sense of some of the more efficient methods for searching because I am just starting out and don't really understand this language at all.)

This is what I have so far.

Range("G2").Select    Do Until Selection.Offset(0, -6).Value = ""
        If Selection.Offset(0, -3).Value = "move" Then
        Selection.Value = "mover
        ElseIf Selection.Offset(0,-3).Value= "demo" Then
        Selection.Value = "demo"
        Else 
        Selection.Value="sale""

This kind of worked at first. If the cell had the word 'move' by itself it would find it. I tried using *, that didnt work. I tried changing .value to .find but it wouldnt let me do that. I also don't understand why it wont accept 'elseif'. (it highlights it in red) All the Q&As Ive found have been either way too basic and I cant apply the offsets or get 'then' to do anything, or they are using some other method and syntax that I can't figure out.

 

[Dad_x6]

I may be totally misunderstanding, so if that is the case, I'm sorry.

But... Assuming your column containing your 3 words was column A, and you wanted your new values to end up on the same row in another column (say G), then could you use something like this as the formula in G2?

=IF(A2="move","mover",IF(A2="demo",A2,IF(A2="sale",A2,"")))

Then you could just drag that formula down the row.

Would that work for you?

 

[viggie79]

When I tried to plug this in through vba it gave me an error on "move". Then I tried to copy/paste it manually but it would only find the words if they were separated, which wont always be the case in my sheets. I tried adding * but that didnt seem to work either.
Not sure whats going on in either of these cases.

 

[Dad_x6]

I was not actually thinking to use VBA in this case. The code I sent was actually not code at all, but a formula that you could just plug into a cell on your spreadsheet. If you wanted to use VBA, you would need it to be a bit different.

Try pasting that (and modifying the A2 as necessary) into your spreadsheet to see if it works. If not, and you want to try VBA, just let me know.

 

[viggie79]

This would work fine, Im sure I can paste it in using vba. The problem is that sometimes my cells have something like 01move or nosale or sales11, and this doesnt pick those up. I need to use a search formula. Your formula idea has given me enough direction that I should be able to find a way to make it work that way.
Thanks!

 

[proficient]

Could you share a precise 3 lines sample data with desire result for our better understanding...?

 

[viggie79]

This is what I am working with

D
2 sales11
3 1Demo
4 xxQuote xx

What I would like is to check to see if 'sale', 'demo', or 'quote' appear in column D. If I find them I would like to fill in the corresponding cell in column G with some text. The end result would look like this

D E F G
2 sales11 sold
3 2demo etc demo version
4 somequote quoted

 

[Dad_x6]

This is what I am working with

D
2 sales11
3 1Demo
4 xxQuote xx

What I would like is to check to see if 'sale', 'demo', or 'quote' appear in column D. If I find them I would like to fill in the corresponding cell in column G with some text. The end result would look like this

D E F G
2 sales11 sold
3 2demo etc demo version
4 somequote quoted

I'm really embarrassed that I couldn't figure this one out in a reasonable amount of time using formulas, but here is something that would work in VBA. Just put this in a loop, and make adjustments for your words, and cell locations.

        If Sheet1.Cells(x, 1) Like "*move*" Then
            Sheet1.Cells(x, 2) = "Mover"
        ElseIf Sheet1.Cells(x, 1) Like "*demo*" Then
            Sheet1.Cells(x, 2) = "Demo"
        ElseIf Sheet1.Cells(x, 1) Like "*sale*" Then
            Sheet1.Cells(x, 2) = "Sale"
        Else
            Sheet1.Cells(x, 2) = "Other"
        End If

 

[Partho]

Viggie79

Another one to try

Sub FindTextMacro()
    Dim LastCell As Range
    Dim FoundCell As Range
    Dim FirstAddr As String
    Range("G:G").Clear
    With Range("D1:D" & Range("D1").End(xlDown).Row)
        Set LastCell = .Cells(.Cells.Count)
        Set FoundCell = .Find(What:="sale", after:=LastCell, Lookat:=xlPart)
        '
        If Not FoundCell Is Nothing Then
            FirstAddr = FoundCell.Address
        End If
        Do Until FoundCell Is Nothing
            '
            FoundCell.Offset(0, 3) = "SomeText1"
            Set FoundCell = .Find(What:="sale", after:=FoundCell, Lookat:=xlPart)
            If FoundCell.Address = FirstAddr Then
                Exit Do
            End If
        Loop
    End With
    'Repeat above code for 'demo'
    'Repeat above code for 'quote'
End Sub

Hope it helps!

 

[viggie79]

Thank you again, this is a clean and simple example of exactly what I was trying to do. I messed up at first and it wasnt working so I googled a bit and decided I needed to add .value after the (x,1). This is what I ended up with eventually for anyone who was looking for the same thing I was.

Sub stuff()Range("B1").Select
Do Until Selection.Offset(0, -1).Value = ""
If Selection.Offset(0, -1).Value Like "*move*" Then
            Selection.Value = "Mover"
        ElseIf Selection.Offset(0, -1).Value Like "*sale*" Then
            Selection.Value = "sales"
        ElseIf Selection.Offset(0, -1).Value Like "*demo*" Then
            Selection.Value = "demo"
        Else
            Selection.Value = "Other"
        End If
        Selection.Offset(1, 0).Select
        Loop
        
End Sub

I select the cell I want to change as the active cell, then check the cell I want to look for my words to appear. If they appear the active cell gets its value changed and it loops until I run out of cells with data in them.


So I keep seeing posts about how using Selection is noob stuff and shouldnt really be used because its clunky but anytime I try to stray from looping like this it just doesnt work. If any one is bored and wants to tighten this up and explain how and why it works Id appreciate it!

Otherwise a final thanks to Dad_x6 and Partho for chipping in and making my life a few thousand clicks easier!