How to define variable in range

Question

I just started VBA coding and I am struck here:

For one cell this program works:

Dim score As Integer, result As String

score = Range("A1").Value

If score >= 60 Then

    result = "pass"

Else
    result = "fail"

End If

Range("B1").Value = result

And how about a column of cells? Can loop works for this? My code using loop - But How to define variable in range?

Dim score As Integer, result As String, I As Integer

score = Range("AI").Value

For I = 1 To 6

If score >= 60 Then

result = "pass"

Else

    result = "fail"

End If
Range("BI").Value = result

Next I

Thanks in advance!

SierraOscar · Accepted Answer · 2016-05-13 10:31:15Z

3

Almost, you just need to use string concatenation (&)

Dim score As Integer, result As String, I As Integer

'score = Range("AI").Value

For I = 1 To 6

    score = Range("A" & I).Value '// Needs to be inside the loop to update.

    If score >= 60 Then
        result = "pass"
    Else
        result = "fail"
    End If

    Range("B" & I).Value = result

Next I

This can also be written as:

For i = 1 To 6
    Range("B" & i).Value = IIf(Range("A" & i).Value >= 60, "pass", "fail")
Next

edited May 13, 2016 at 10:31

answered May 13, 2016 at 10:23

SierraOscar

17.7k6 gold badges44 silver badges71 bronze badges

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

Comments

user3598756 · Accepted Answer · 2016-05-13 10:38:08Z

2

you can also go with a "formula" approach:

Range("B1:B6").FormulaR1C1 = "=If(RC1 >= 60, ""pass"", ""fail"")"

thus maintaining that check active for any possible subsequent change in columns A cells values

or, should you want to have "static" values only:

With Range("B1:B100")
    .FormulaR1C1 = "=If(RC1 >= 60, ""pass"", ""fail"")"
    .Value = .Value
End With

answered May 13, 2016 at 10:38

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

1 Comment

user3598756 Over a year ago

it's the same, since values to check are in column "A" which is column 1

Collectives™ on Stack Overflow

How to define variable in range

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related