CODEWITHSUNDEEP
vbaloopsexcelerror-handling
Luke Beacon
Luke Beacon

Reputation: 154

Excel VBA: Error handling only works for one pass

I have searched many sources to try and fix my problem, however I have had no success. I am a beginner at VBA, so my code will seem very messy most likely. For the purpose of asking the question I will try and keep the info in the sheets to a minimum

I have two sheets, 'Data' and 'Raw Numbers'.
Data contains columns headed "Property" and "Phone Number", but only "Property" has data in it.
Raw Numbers contains the same columns, however it is missing some of the "Property" values, as it only contains a certain number of phone numbers associated with those properties.

DATA SHEET:
Property . . . . Number
1 Ada St
2 Ada St
3 Ada St
4 Ada St
5 Ada St
6 Ada St

RAW NUMBERS SHEET:
Property . . . . Number
1 Ada St . . . . 12345678
2 Ada St . . . . 12356322
5 Ada St . . . . 12551122
6 Ada St . . . . 12212112

I have a code that imports the numbers into the Data sheet automatically, however once it reaches 3 Ada St, it returns an Error (400) because there is no 3 Ada St on Raw Numbers. I included an error handler, however it only works once before returning the error again.
Here is my code so far:

Sub Button_Click()
Worksheets("Data").Range("B4").Select  'B4 is the Number column in the data sheet
    Do While ActiveCell.Offset(0, -1) <> ""
    On Error GoTo ErrorHandler
                ActiveCell.Value = Application.WorksheetFunction.IfError(WorksheetFunction.VLookup(ActiveCell.Offset(0, -1), ThisWorkbook.Worksheets("Raw Numbers").Range("Numbers"), 2, False), "")

ErrorHandler:
                ActiveCell.Offset(1, 0).Select
   Loop

End Sub

I wrote the code this way because I originally had it working with Functions rather than code, however that was too messy. The IfError function is in there because I want the cell to be blank if there is no number attached to the address. I tried converting this to part of the errorhandler, however it just ended up giving me an error.

Somebody please help me!! If you need any more information, I will gladly provide it. I will be watching this thread constantly!

Upvotes: 1

Views: 811

Answers (2)

SeanC
SeanC

Reputation: 15923

functions shouldn't be too messy. A combination of IFERROR and VLOOKUP should work.

the formula would look something like this:

=IFERROR(VLOOKUP(A2,RawData!A:B,2,FALSE),"")

In VBA, I would code something like this:

Option Explicit

Sub test()
Dim Result

On Error Resume Next
Result = Application.WorksheetFunction.VLookup([A3], [RawData!A:B], 2, False)
If Err.Number = 1004 Then ' data not found, or some other formula error
    Result = ""
Else
    MsgBox Err.Number & "-" & Err.Description
    Exit Sub
End If

[B3] = Result
End Sub

Upvotes: 1

glh
glh

Reputation: 4972

Use a formula in the code and paste values after.

ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],Numbers,2,FALSE),"""")"
ActiveCell.Value = ActiveCell.Value

Untested...

Upvotes: 1

Related Questions