Using an offset and range inside a loop

Question

I know this will be a stupid simple answer, but it's killing me right now...

For Each cell In rng2
    If cell.Offset(0, -13) And cell.Offset(0, -12).Value <> "" Then
        cell.Value = 1
    Else
        cell.Value = 0
    End If
Next cell

Where the .offset and .offset is, how can i just say

If cell.offset(0,12:13)

I know that's not it... This one is simple however, getting this piece right will save me so much headache throughout the vba code all over the workbook.

Thanks for helping this rookie in advance!

Creating a range from offset is possible but, I believe, more difficult than this: If cell.Offset(0, -13) <> "" And cell.Offset(0, -12).Value <> "" Then. Your expression means: cell.Offset(0, -13) = True And cell.Offset(0, -12).Value <> "" which I doubt is what you meant. — Tony Dallimore
– Tony Dallimore, Commented Aug 8, 2020 at 16:07

Tim Williams · Accepted Answer · 2020-08-08 17:02:45Z

1

Maybe something like this:

For Each cell In rng2
    If Application.CountA(cell.Offset(0, -13).Resize(1, 2)) = 2 Then
        cell.Value = 1
    Else
        cell.Value = 0
    End If
Next cell

answered Aug 8, 2020 at 17:02

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DRoySSS Over a year ago

Yup, that was the ticket! See I knew it was something stupid simple. I always forget to use some of the application processes. face palm Thank you Mr. Williams!

T.M. · Accepted Answer · 2020-08-09 14:01:29Z

0

Alternative using variant array

You can combine logical assumptions by multiplication (True * True corresponding to -1 * -1 = 1 in case of both reference cells greater"") and try the following:

'a) assign both offsets to variant 2-dim array
    Dim v: v = rng2.Offset(0, -13).Resize(, 2)
'b) get values
    Dim i As Long
    For i = 1 To UBound(v)     ' check each row
        v(i, 1) = (v(i, 1) > 0) * (v(i, 2) > 0)
    Next i
'c) write values
    rng2.Resize(, 1) = v

answered Aug 9, 2020 at 14:01

T.M.

10k3 gold badges40 silver badges61 bronze badges

Collectives™ on Stack Overflow

Using an offset and range inside a loop

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related