Excel VBA pass variable between functions

Question

I am trying to write excel VBA code that will parse column B (analyst login time), and if that column is 0 (as in they were out that day), it will update the adjacent columns to say NA. Currently, the below code errors out. If I change the rng variable in Cells(rng, i) to a number, then the code will take that number (let's say 4) and put NA in all of the fields (3 to 23, so, C to W). I believe that the issue is that the value of rng is not being passed into the inner function, but I do not know how to get arround this.

Dim rng As Range
For Each rng In Range("B4:B10")
    If rng.Value = 0 Then
        For i = 3 To 23
            Cells(rng, i).Value = NA
        Next i
    End If
Next rng

Perhaps you want #N/A which is creatable with =CVErr(2042) — S Meaden
– S Meaden, Commented Jan 4, 2017 at 12:49

Alex K. · Accepted Answer · 2017-01-04 12:50:22Z

1

rng on its own (without an explicit property name) defaults to returning .Value which when 0 tries to use row index 0 which is not valid, instead get the row number via .Row:

Cells(rng.Row, i).Value = "NA"

If NA is not a variable but a string, quote it.

Non-loop alternative:

 If rng.Value = 0 Then Range(rng.Offset(0, 1), rng.Offset(0, 21)).Value = "NA"

edited Jan 4, 2017 at 12:50

answered Jan 4, 2017 at 12:44

Alex K.

177k32 gold badges276 silver badges299 bronze badges

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

1 Comment

sbagnato Over a year ago

Yes, thank you, that worked. I had quotes around the NA, but forgot to copy them here. However, the rng.Row is what fixed it.

Gadziu · Accepted Answer · 2017-01-04 12:43:58Z

0

Problem is here:

Cells(rng, i).Value = "NA"

why you give range to place where Number (row number) must go?

You need to use

Cells(rng.Row, i).Value = "NA"

answered Jan 4, 2017 at 12:43

Gadziu

6978 silver badges22 bronze badges

Collectives™ on Stack Overflow

Excel VBA pass variable between functions

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