CODEWITHSUNDEEP
excelvba
sbagnato
sbagnato

Reputation: 496

Excel VBA pass variable between functions

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

Upvotes: 2

Views: 457

Answers (2)

Alex K.
Alex K.

Reputation: 175748

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"

Upvotes: 1

Gadziu
Gadziu

Reputation: 687

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"

Upvotes: 0

Related Questions