Solved: Application-defined or object-defined error - Len(c.Offset(0,-1).value)

[mailman]

Hi all. Can someone point out to me why this isn't working?

I'm running out of Excel 2003


counter = 0

For Each c In ActiveCell.CurrentRegion.Cells
If (c.Column > 2) Then GoTo Skip

If (c.value = "") And (c.Column = "2") And (Len(c.Offset(0, -1).value) > 0) Then
c.value = userName
Debug.Print c.Column & " Adjacent value:" & Len(c.Offset(0, -1).value)
Else
If (c.Column = 2) And Len(c.value) > 0 Then
userName = c.value
End If
End If
Skip:
counter = counter + 1
Next


The loop works great up until I try to add the third condition in the second If statement (checking the length of the adjacent cell).

It prints out ok in my Debug.Print statement, so I'm confused as to why this is occurring. :wot

I've left out my variable declarations for the sake of brevity...

[Bob Phillips]

Are you sure that c is not pointing at column A, so Offset(0, -1) is invalid?

[mailman]

That must've been it...

I figured vba would follow the order of operation and fail on the second condition and then move on...:


If (c.value = "") And (c.Column = "2") And (Len(c.Offset(0, -1).value) > 0) Then

guess not.

Thanks for your help xld

My solution was creating a new condition now knowing that it has met the first set of criteria (more importantly, that I'm in the second column):



counter = 0

For Each c In ActiveCell.CurrentRegion.Cells
If (c.Column > 2) Then GoTo Skip

If c.value = "" And c.Column = "2" Then
If Len(c.Offset(0, -1).value) > 0 Then
c.value = userName
Debug.Print c.Column & " Adjacent value:" & Len(c.Offset(0, -1).value)
End If
Else
If (c.Column = 2) And Len(c.value) > 0 Then
userName = c.value
End If
End If
Skip:
counter = counter + 1
Next


If anyone can suggest a way to put this directly into the Condition, please feel free to suggest it. Otherwise, this is solved. Thanks

[Bob Phillips]

No, that is a problem in that VBA doesn't short-circuit, it resolves all condituions, and that is how it can fail even when another condition(s) have been met.