How to check the cell is valid or not in VBA?

Question

If

• r = 1, and
• c = 1

the intended code below is invalid (it tries to return a cell two columns to the left of Column A)

Cells(r, c).Offset(0, -2)

How do I check whether the intended cell is valid or not in vba?

Answer 1

1. Use a Range object to test whether it is valid (preferred for versatility)
2. Test whether the column is valid (assumes hard-code of your OFFSET as (0,2)

(1) code

Sub Test1()
Dim r As Long
Dim c As Long
r = 1
c = 1
Dim rng1 As Range
On Error Resume Next
Set rng1 = Cells(r, c).Offset(0, -2)
On Error GoTo 0
If Not rng1 Is Nothing Then
'proceed with your code - range exists
Else
MsgBox "Range Error", vbCritical
End If
End Sub

(2) code

Sub Test2()
Dim rng1 As Range
Dim r As Long
Dim c As Long
c = 3
r = 1
If c - 2 <= 0 Then
MsgBox "Error", vbCritical
Else
Set rng1 = Cells(r, c).Offset(0, -2)
End If
End Sub

Answer 2

Here is example for you.

Sub sample()

    Dim r As Integer
    Dim c As Integer
    r = 1
    c = 1


    Dim validRng As Boolean
    validRng = isValidRng(r, c, 0, -2)
    Debug.Print validRng

    validRng = isValidRng(r, c + 5, 0, 2)
    Debug.Print validRng

     validRng = isValidRng(r, c, -1, 0)
    Debug.Print validRng

     validRng = isValidRng(r, c + 2, 0, -1)
    Debug.Print validRng

End Sub

Function isValidRng(row As Integer, col As Integer, offsetrow As Integer, offsetcol As Integer) As Boolean
'Returns if its valid range
    If ((row + offsetrow) > 0) And ((col + offsetcol) > 0) Then
        isValidRng = True
    Else
        isValidRng = False
    End If
End Function