CODEWITHSUNDEEP
vb.netvbaexcel
Louie Miranda
Louie Miranda

Reputation: 1159

How to properly set the if condition on a excel vb code Cells.Find? and 2 more issues

I have this question and answered code at On excel how to search & replace a string on a column using a library. Which I am having three (3) problems.

  1. RUN-TIME ERROR 13, HOW TO DETERMINE IF findRange HAS FOUND SOMETHING?
  2. IS THIS THE CORRECT WAY TO FIND THE findRange?
  3. HOW TO ADD A "NOT FOUND" ON THE ACTIVE SHEET PAGE?

FROM:

enter image description here

TO: -- This is what I wish to accomplish.

enter image description here

The vba code can be seen below:

Sub FindAndReplace()
' FindAndReplace Macro
' @author Louie Miranda
' Ability to find the range of ids against another worksheet
' and insert the name on the main sheet, one issue is if the value does not exist

    Dim findRange as Object

    ' Loop over the current worksheet
    For Each c In Worksheets("RECORDS").Range("A3:A7").Cells

        ' Go to Agents sheet
        Sheets("AGENTS").Select

        ' Do a search
        Columns("B:B").Select

            ' Q1: RUN-TIME ERROR 13, HOW TO DETERMINE IF findRange HAS FOUND SOMETHING?
            Set findRange = Cells.Find(What:=c, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Activate

        ' Determine if the find has something
        ' Q2: IS THIS THE CORRECT WAY TO FIND THE findRange?
        If findRange Is Nothing Then

            MsgBox "not found"

            ' Back to records sheet
            Sheets("RECORDS").Select

            'Range(c.Address).Offset(0, 1).Select
            'ActiveSheet.Paste

            ' Q3: HOW TO ADD A "NOT FOUND" ON THE ACTIVE SHEET PAGE?

        Else

            Application.CutCopyMode = False
            ' Choose beside the column to copy
            ActiveCell.Offset(rowOffSet:=0, columnOffset:=-1).Activate
            Selection.Copy

            ' Back to records sheet
            Sheets("RECORDS").Select

            ' Copy the agent name to RECORDS sheet
            Range(c.Address).Offset(0, 1).Select
            ActiveSheet.Paste

        End If
    Next c
End Sub

Any help is much appreciated.

Kind regards

Upvotes: 1

Views: 396

Answers (1)

Jur Pertin
Jur Pertin

Reputation: 564

I have updated your code. This should work now.

Sub FindAndReplace()

    Dim findRange       As Range
    Dim c               As Range

    ' Loop over the "RECORDS" worksheet
    For Each c In Worksheets("RECORDS").Range("A3:A7").Cells
        'Set findRange in "AGENTS" sheet
        With Worksheets("AGENTS")
            Set findRange = .Cells.Find(What:=c.Value, After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False)
        End With


        If Not findRange Is Nothing Then
            'if found then
            Sheets("RECORDS").Cells(c.Row, c.Column + 1) = Sheets("AGENTS").Cells(findRange.Row, findRange.Column - 1).Value
        Else
            'if not found then
            Sheets("RECORDS").Cells(c.Row, c.Column + 1) = "Not Found"
        End If

    Next c

Upvotes: 1

Related Questions