CODEWITHSUNDEEP
excelvba
CptGoodar
CptGoodar

Reputation: 303

Search Cell for no mention of a specific string

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.

Upvotes: 0

Views: 70

Answers (1)

Wookies-Will-Code
Wookies-Will-Code

Reputation: 735

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

Upvotes: 1

Related Questions