I am moving some excel formula to visual basic to save some file space. One aspect I am struggling with is adding some error-checking.
I have a formula (in column 23/W): your text=IF(ISERROR(VLOOKUP(W2, auditdata!A:D, 1, FALSE)),"room not audited", "room audited")
That I have re-written as visual basic code, but I am struggling to add the "On Error" statement. I am using these instructions for reference: https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/on-error-statement
Actually, the formula is a little more complicated - I need to check the values of two columns and if both exist in the Table Array I am checking, then return "audited" otherwise "not audited". I am simplifying the problem so I can better understand.
There will be two situations where an error can occur: if the cell checked is empty or it has a value that does not exist in the other data table. The VBA error is Run-time error '1004':
The code so far is:
Sub CheckRoomAudited_Method()
Dim AuditCheckCell As Range 'cell where the vlookup results are returned
Dim TotaltoCheck As Long 'last row in the column to check
TotaltoCheck = Worksheets("RoomUse").Range("A2").End(xlDown).Row 'using first column as some data missing in lookup range column
Dim EndofAuditRange As Long
EndofAuditRange = Worksheets("auditdata").Range("A1").End(xlDown).Row 'this in the TableArray that will be checked
Dim AuditCheckColumn As Range 'column where the vlookup results are returned
Set AuditCheckColumn = Worksheets("RoomUse").Range(Worksheets("RoomUse").Cells(2, 23), Worksheets("RoomUse").Cells(TotaltoCheck, 23)) 'seemed long-winded to specify but could not get it to work otherwise
Dim VlookupValueColumn As Range 'need to check values in this column to see if they exist in other sheet
Set VlookupValueColumn = Worksheets("RoomUse").Range(Worksheets("RoomUse").Cells(2, 37), Worksheets("RoomUse").Cells(TotaltoCheck, 37))
Dim TableArrayRange As Range 'TableArray that will be checked
Set TableArrayRange = Worksheets("auditdata").Range(Worksheets("auditdata").Cells(1, 1), Worksheets("auditdata").Cells(EndofAuditRange, 4))
For Each AuditCheckCell In AuditCheckColumn
'loop through column number 37 ("AK:AK") and check whether each value exist in the other table/sheet
'write the value if it exist or "room not audited" if it does not exist in column 23/W
AuditCheckCell.Value = WorksheetFunction.VLookup(AuditCheckCell.Offset(0, 14), TableArrayRange, 1, False)
Next AuditCheckCell
End Sub
There are basically two ways to execute VLookup from VBA: The first is the method you are using (WorksheetFunction.VLookup), the other is Application.VLookup.
Basically, they do the same, the only difference is the error-handling.
WorksheetFunction.VLookup throws a runtime error if a value cannot be found. You need to catch this error by yourself using the On Error-Statements:
Dim myValue As Variant
myValue = "(not found)" ' Or whatever value you want to put into the cell if not found
On Error Resume Next
myValue = WorksheetFunction.VLookup(AuditCheckCell.Offset(0, 14), TableArrayRange, 1, False)
On Error Goto 0
AuditCheckCell.Value = myValue
Application.VLookup doesn't throw a runtime error. Instead, if the value is not found, it returns an error value. When you write that value into a cell, the cell will display #N/A - that's exactly what is returned from the VLookup-Function in an Excel formula. In VBA, you can check this using the functions IsError, or, more specifically, IsNA.
Dim myValue As Variant
myValue = Application.VLookup(AuditCheckCell.Offset(0, 14), TableArrayRange, 1, False)
if IsNA(myValue) Then
AuditCheckCell.Value = "(not found)"
Else
AuditCheckCell.Value = myValue
End If
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments