VBA How to Check if Cell Value is valid and exists?

0
votes

I have this cell.Offset(0, -2) cell that I need to check if it exists inside my VBA loop.

My idea is that if the cell has an .Offset(0, -2) that does not exist (e.g. say cell = Column B and cell.Offset(0, -2) = Column A-1, cell.Offset(0, -2) is supposed to be invalid), I want to be able to check that it doesn't exist in the if else statement.

Right now when it doesn't exist, I'm getting a “Runtime Error 1004: Application defined or object defined error”.

I've tried IsErr(cell.Offset(0, -2)), cell.Offset(0, -2).Value, If cell.Offset(0, -2) Is Nothing Then, cell.Offset(0, -2) = "", but none of them seem to work... can anyone point me in the right direction? I'm pretty new to VBA and it seems like different variable types have different ways of checking if the value exists.

2
vbaexcel
You can check if the column is greater than 2 in your if statement – Darrell H
@DarrellH you mean check for the column value? – Kyle Yeo
Yes. If cell.column > 2 then... – Darrell H
Just make sure the .Column is more than the offset amount. Or, Sum of .Column and the offset amount > 0, but also needs to be less than Columns.Count – PatricK
Either go with the solution of @DarrellH by checking for column directly or (if you want to be within a set range) use something like If Not Intersect(cell, [range which is ok]) Is Nothing Then... – Dirk Reichel

2 Answers

0
votes

If you can use your offset amount as a variable, or some method to evaluate against the cell's column, you don't need to defend against errors. Consider below...

Sub IsvalidARea()
    Dim OffsetAmount As Integer: OffsetAmount = -2
    Dim Cell As Range

If Cell.Column + OffsetAmount < 1 Then
MsgBox "Oh FALSE!"
Else
'EVERYTHING IS OKAY!

End If
End Sub
0
votes

A solution for this is by using the constantly misused On Error Resume Next, coupled with a If IsError. Here's an example:

On Error Resume Next
If IsError(ActiveCell.Offset(, -2).Select) = True Then
    'This is indicative of an error. Meaning you are on Column B, and you ran out of space.
Else
    'This means an error did not occur. Meaning you were at least on Column C or above.
End If

There may be a better solution, in fact I am sure there is. But what this will do is allow you to move past the actual 1004 Application error, and you can actually identify, and use the error that was really returned, instead of just forcing your macro/script to end.

To avoid using the error check, you can always check the current column index (B is 2) and make sure whatever you are offsetting by, once subtracted by your index is greater than 0. For example, column B is 2. Your offset is 2. 2 - 2 = 0, so it should not attempt it. Column C is 3, 3 - 2 = 1, which is greater than 0 so it would be valid. Both are explorable methods.