0

I am new to coding in general and need to write a macro. The below code searches for text in a field and if so it takes a quantity from that column and duplicates it in another cell. Then repeats for the entire Row, Which does work, but the text in the cell needs to be exact.

Sub Test()

Dim variable As String
variable = "insert value or cell here"

With Sheets("Test")
    LR = .Cells(Rows.Count, "N").End(xlUp).Row
    For i = LR To 2 Step -1
        If .Cells(i, "N").Value = "Blue" Or .Cells(i, "N").Value = "Red" Or .Cells(i, "N").Value = "Green" Then
            .Cells(i, "AV").Value = .Cells(i, "P").Value
        End If
    Next i
End With

End Sub

With some looking around online I have put together the below code to instead come back positive if the value appears anywhere in that cell, but I cant get it to work. Any help is appreciated.

Sub Test()

Dim variable As String
variable = "insert value or cell here"

With Sheets("Test")
    LR = .Cells(Rows.Count, "M").End(xlUp).Row
    For i = LR To 2 Step -1
        If InStr(.Cells(i, "M"), Value = "Red" or Value = "Green" or Value = "Blue") Then
            .Cells(i, "AV").Value = .Cells(i, "P").Value

        Else: .Cells(i, "AV").Value = "0"
        End If
    Next i
End With

End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40
schittick
  • 1
  • `InStr()` accepts one argument for where to look and a second argument for what to find; it outputs long. you would need to split your `instr()` to have each, OR create a loop or switch to support different values. – Cyril Dec 20 '19 at 19:05
  • Use the Excel Find command and if it's result is 0 or greater the cell or string contains your search value. – Brian Dec 20 '19 at 19:06
  • Another similar question: https://stackoverflow.com/questions/24617278/vba-if-a-string-contains-a-certain-letter – BigBen Dec 20 '19 at 19:06
  • 1
    @Brian - just to avoid confusion with `Range.Find`, that is `WorksheetFunction.Find`. But `InStr` does the same thing and is shorter. – BigBen Dec 20 '19 at 19:07
  • @BigBen i agree that the duplicate chosen question is useful and helpful, but will explain further in my answer the pitfall (which i have personally fallen into) with `instr()`'s implicit "boolean" response (not really boolean!!!). that appears tied to not having ">0" after the instr – Cyril Dec 20 '19 at 19:14
  • 1
    @Cyril yes that is good commentary to add to your answer. The dupe target may not be the best, perhaps the 2nd one I posted is better. In any case, both of them use the `>0`. – BigBen Dec 20 '19 at 19:15
  • @BigBen yes, the second one is solid, as it links to the microsoft documentation. closed before i could vote lol – Cyril Dec 20 '19 at 19:20

1 Answers1

0

Simple splitting of your instr():

If InStr(.Cells(i, "M").Value,"Red") > 0 Or InStr(.Cells(i, "M").Value,"Green") > 0 or InStr(.Cells(i, "M").Value,"Blue") > 0 Then

From my comment, remember that:

InStr(,) accepts one argument for where to look and a second argument for what to find; it outputs long.

Improper use of the implicit response of InStr(), as you've written, can cause some confusion, so you will want to add the ">0" to your condition.

This will be a true condition:

Dim str as string
str = "cat"
If InStr(str, "a") Then

The fact that InStr returns a value is considered "true" in this case, because of an implicit response... but the next one will be false:

Dim str as string
str = "cat"
If InStr(str, "a") And InStr(str, "c") Then

The "yes, this has a result" is dropped when moving to the second criteria, meaning not both scenarios are true. To make the above actually correct, we will add the condition in respect to InStr() returning Long (not boolean):

Dim str as string
str = "cat"
If InStr(str, "a") > 0 And InStr(str, "c") > 0 Then

The first InStr() returns 2, so true, and the second InStr() returns 1, so true...

Cyril
  • 6,448
  • 1
  • 18
  • 31