Search Cell for no mention of a specific string

Question

I have some code that currently searches column A for the mention of 27 and copies the cells in column B, same row as 27 into Column C. Is there a way to modify this code to copy the values of cells when 27 is not in Column A. Does that make sense?

The sample worksheet contains the number 27 in Cells (A1:A30) and the number 1 in Cells (B1:B30); except in A5, where it's 28, and B5 where it's 2. I know, in this case, i could search for 28, but the main reason for this code is that there will be more than 1 value that could be different i.e. a 26 or 22 , as well as 30 etc.

When I was thinking about this, I assumed that I had to change the line If Not foundCel Is Nothing Then to something different, but I may be barking up the wrong tree here.

Option Explicit

Sub Button1_Click()

    Dim v As Long

    v = 1
    findStr = "27"
    Set foundCel = Range("A:A").Find(What:=findStr)

    If Not foundCel Is Nothing Then              'Yes'

        firstAddress = foundCel.Address

        Do

            Range("C" & v).Value = foundCel.Offset(0, 1).Value
            Set foundCel = Range("A:A").FindNext(foundCel)
            v = v + 1

        Loop While Not foundCel Is Nothing And foundCel.Address <> firstAddress

    End If

End Sub

Thanks for any help you can provide.

Your only question is `Does that make sense?`. The answer is yes. But what you probably want is to check how to use `Else` clause and how to ask better questions. There is a helpful FAQ here on SO about the later and a number of great explanations on the Internet about the former. — Victor K, Dec 08 '17 at 15:29
I'm not sure if I understand your question, but I think that you should look into the `instr` function. It searches a string for a pre-defined string. If the search string is not found, it will return a 0. You can condition on the value of `instr` being equal to 0 or not. — Jarom, Dec 08 '17 at 15:30
@VictorK i don't mean to sound rude or anything but `"Does that make sense?"` is referring to the sentence beforehand regrading a way to search for the `not` case. Has that clarified it more ? Im sorry if i come across as rude — CptGoodar, Dec 08 '17 at 15:33
@Jarom, to help you understand, I am looking for a way to search Column A for cells that DONT contain 27 then paste their respective B cell values in Column C. I hope this helps to clarify the question. — CptGoodar, Dec 08 '17 at 15:35
@CptGoodar I also didn't mean to sound rude, sorry if it came of that way. It will greatly help to state a clear question. I would suggest something like: "How can one check if a number is not present within a value of a given cell?". To that end you can either use `Instr` like Jarom suggested or try `Like` with wildcards. — Victor K, Dec 08 '17 at 15:40
@VictorK no not at all, i find it hard to write English so it's likely my poor grammatical wording. How would I go about using the `Else` function in VBA? Does the `else` function act as like an ** Exclusive** Im fairly new to vba so not 100% sure on the syntax — CptGoodar, Dec 08 '17 at 15:43
@CptGoodar I realized that `Else` won't work since you use `Findnext` . Instead set a range first (look [here](https://stackoverflow.com/a/11169920/8597922) how to find lastrow) and the use a loop and check cell values with `InStr` or `Like`. Edit: There is an answer for you already by WWC. — Victor K, Dec 08 '17 at 15:46

Wookies-Will-Code · Accepted Answer · 2017-12-08T15:54:05.987

Here you go, tests for 27 not being present in the string of column1 (A), if it is not it copies column2 (B) to column3 (C):

Private Sub Button1_Click()

'Searches all text in Column 2 on a Sheet for the string located in Column 1

Dim ThisWB As Workbook
Dim ThisWS As Worksheet
Dim i As Integer

Dim Col1 As Double
Dim Col2 As Double
DIM Col3 As Double


Dim Col1_rowSTART As Double
Dim Col1_rowEND As Double



Dim strTest As String


'Set up parameter that we know
Set ThisWB = ActiveWorkbook
Set ThisWS = ActiveSheet
Col1 = 1
Col2 = 2
Col3 = 3
strTest = "27"
'Define Starting Row for each column
Col1_rowSTART = 1

'Define ending row for each column
Col1_rowEND = ThisWS.Cells(ThisWS.Rows.Count, Col1).End(xlUp).Row


For i = Col1_rowSTART To Col1_rowEND
    'make a string out of each cell value in Col1
    'Check if 27 is NOT in the cell string being tested
    'to test if it IS change to > 0
    If InStr(CStr(ThisWS.Cells(i, Col1)), strTest) = 0 Then
            ThisWS.Cells(i, Col3).Value = ThisWS.Cells(i, Col2)
    End If

Next i

MsgBox ("27 Search Complete!")

End Sub

Quick and dirty. Cheers. -WWC

I think this is what you want, it uses InStr for the test. Returns 0 if it is not in the string. Returns > 0 if it is in the string. -WWC — Wookies-Will-Code, Dec 08 '17 at 15:53

Search Cell for no mention of a specific string

1 Answers1