CODEWITHSUNDEEP
excelvba
Ting Ping
Ting Ping

Reputation: 1145

How to set a default error value?

For i = 1 To UBound(CementContractNo())

On Error Resume Next
Row = Application.Match(CementContractNo(i), Range("A:A"), 0)
MsgBox Row

CementStartDate(i) = Cells(Row, ContractStartCol).Value

If Cells(Row, ContractExtCol).Value <> "" Then
    CementEndDate(i) = Cells(Row, ContractExtCol).Value
Else
    CementEndDate(i) = Cells(Row, ContractEndCol).Value
End If

Next i

I am running the above code to find the start date and end date of an excel table. However, it would return an error when the table lookup fails. In this case, I would love to assign a default error value of "Missing" or something else to follow up. Any idea how to do it?

Upvotes: 2

Views: 1911

Answers (1)

Daniel
Daniel

Reputation: 141

use a construction like this to precisely control what happens when you get an error

On Error Goto ErrHandling
'Your normal code

'at end of sub
ErrHandling:
'[Your code what happens when you get an error]
Resume Next 'will resume at previous location in code.

Read more here: http://www.cpearson.com/excel/errorhandling.htm

Upvotes: 3

Related Questions