CODEWITHSUNDEEP
excelvba
Vikram Gajjala
Vikram Gajjala

Reputation: 11

Concatenate For Loop Counter in Range variable

I would need help to concatenate myV1 = p1.Value with variable 'j' from the for loop like myV1= "p" & j & ".value".

Its giving type mismatch error

please help

Sub MyPrg()

Dim myVol As Range
Dim p1 As Range
Dim p2 As Range
Dim p3 As Range
Dim myV1 As Long
Dim i, j As Integer

Set myVol = Range("G3")
    
Set p1 = myVol.Offset(1, -1)
Set p2 = myVol.Offset(1, 0)
Set p3 = myVol.Offset(1, 1)

If myVol = 1000000 Then
  
p1.Value = myVol.Value * 40 / 100
p2.Value = myVol.Value * 40 / 100
p3.Value = myVol.Value * 20 / 100

For j = 1 To 3

myV1 = p1.Value

Upvotes: 1

Views: 39

Answers (1)

Rory
Rory

Reputation: 34055

Use an array:

Sub MyPrg()

Dim myVol As Range
Dim p(1 to 3) As Range
Dim myV1 As Long
Dim I As Integer, j As Integer

Set myVol = Range("G3")
    
Set p(1) = myVol.Offset(1, -1)
Set p(2) = myVol.Offset(1, 0)
Set p(3) = myVol.Offset(1, 1)

If myVol = 1000000 Then
  
p(1).Value = myVol.Value * 40 / 100
p(2).Value = myVol.Value * 40 / 100
p(3).Value = myVol.Value * 20 / 100

For j = 1 To 3

myV1 = p(j).Value

Upvotes: 2

Related Questions