asked on

Macro to return result name in column B

Hi Experts

the following vba code works 100% but i need to amend the current code so it returns the text name in column b" onwards which match the account number in column A2 onwards...

As opposed to saying MsgBox "You Entered Customer Account Number " & num & " which matches " & Range("B" & r).Value

i want the macro to say account number: XXXXXXX relates to organistion name: BNY etc...

Sub sValidate()
    
    Dim num As String, chK As Long, r As Long, e As Long
    
    num = Application.InputBox(prompt:="Enter: Customer Account Number:", Type:=1)
    
    If num = 0 Then
        MsgBox "Incorrect Customer Account Number!. Please Re-Enter Next Customer Account Number!"
        Exit Sub
    End If
    
    On Error Resume Next
    r = Sheets("Source data").Range("A2:A500000").Find(num, , , xlWhole).Row
    e = Err.Number
    On Error GoTo 0
    
    If e <> 0 Then
        MsgBox "Customer Account Number Not Found!"
        Exit Sub
    End If
    
    MsgBox "You Entered Customer Account Number " & num & " which matches " & Range("B" & r).Value
End Sub

Open in new window

Steve

you have not added the Sheets("Source data"). to the Range portion of the Message box:

Try:

MsgBox "Customer Account Number " & num & " relates to organistion name: " & Sheets("Source data").Range("B" & r).Value

Open in new window

route217

ASKER

apologies my fault i did not see that...

i need one more adjustment please to the current code...

here is the problem:

If the end user does not enter in a account number and presses the cancel button then the macro error with type mismatch run time error 13...

on line If num = 0 Then

So if the end user hit the cancel button macro should not error but exit.....

ASKER CERTIFIED SOLUTION

Steve

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

route217

ASKER

fixed....

thanks appreciated..