Searching through a non-contiguous range

I have a UserForm for updating details on a sheet by searching for a reference in a range and updating the cell to the right of that using offset. I'm using this method to elimate the dreaded human error factor.

I found the below code down the back of the internet and have adapted it to suit my workbook. It uses a string (in TextBox_Reference) and searches in column R and stamps the value from Textbox_Date to the corresponding cell (next column over).

I need to somehow adapt this so if it doesn't find the value in column R it checks V and then Z and then AD. In reality there will be many columns to check.

Could someone please help me amend this so it searches through a non-contiguous range?
Each column in the range will start from row 2 and end at the same "LastRow" which is defined earlier in the code.

Many thanks in advance.

Kerry.

Dim FindString As String
Dim Rng As Range
FindString = TextBox_IssueReference
If Trim(FindString) <> "" Then
    With Sheets("Daily Data").Range("R2:R" & LastRow) 
        Set Rng = .Find(What:=FindString, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not Rng Is Nothing Then
            Rng.Offset(0, 1) = CDate(TextBox_Date)
        Else
            MsgBox "Reference not found."
        End If
    End With
End If

Maybe:

Sub VBAClueless()
Dim i As Long, rng As Range, FindString As String
FindString = TextBox_IssueReference
If Trim(FindString) <> "" Then
    With Sheets("Daily Data")
        For i = 18 To .UsedRange.Columns.Count Step 4
            Set rng = .Range(.Cells(2, i), .Cells(LastRow, i)).Find(What:=FindString, LookIn:=xlValues, LookAt:=xlWhole)
                If Not rng Is Nothing Then
                    rng.Offset(0, 1) = CDate(TextBox_Date)
                    Exit For
                End If
                If i >= .UsedRange.Columns.Count Then MsgBox "Reference not found."
            Set rng = Nothing
        Next i
    End With
End If
End Sub

Hi there,

It's not easy to test this without access to your worksheet and UserForm, but try using the following code as a replacement for the code you've posted:

    Dim sStringToFind   As String
    Dim rFoundCell      As Range
    Dim vColumn         As Variant

    sStringToFind = TextBox_IssueReference

    If Trim(sStringToFind) <> vbNullString Then

        For Each vColumn In Array("R", "V", "Z", "AD")

            With Sheets("Daily Data").Range(vColumn & "2:" & vColumn & iLastRow)

                Set rFoundCell = .Find(What:=sStringToFind, _
                                       After:=.Cells(.Cells.Count), _
                                       LookIn:=xlValues, _
                                       LookAt:=xlWhole, _
                                       SearchOrder:=xlByRows, _
                                       SearchDirection:=xlNext, _
                                       MatchCase:=False)

                If Not rFoundCell Is Nothing Then
                      rFoundCell.Offset(0, 1) = CDate(TextBox_Date)
                Else: MsgBox "Reference not found."
                End If

            End With

        Next vColumn

    End If

The highlighted values can be changed, deleted or added to as per your requirements.


It's also likely that this statement can be simplified to:

                Set rFoundCell = .Find(What:=sStringToFind, _
                                       LookIn:=xlValues, LookAt:=xlWhole)

Hope this helps - please let me know how you get on.

Regards,

Greg M