Quotes in VBA Code

[Frank]

I would like to refer to

=range("A10")

similar to the following

=range(""A" & 10")

so that I can increment the number reference. I know I
have the quotes set up wrong. How do you refer to
changing letters or numbers that need to appear within
quotes? Thanks for your help.

 

[Don Guillett]

how about
cells(10,"a")
or
range("a"&10)

 

[Tom Ogilvy]

In a worksheet formula:
=Indirect("A" & 10)

or if B9 will hold the number 10

=Indirect("A" & B9)

------------------

If you mean in code

set rng = Range("A" & 10)

or

lrow = 10
set rng = Range("A" & lRow)

 

[Frank]

Tom,

How do you handle the range reference in code to allow
changing numbers like the following scenario:

With ActiveChart.Parent
.Width = Range("B2:H17").Width
End With

I want to use a loop to add 10 to the reference above to
get something like:

With ActiveChart.Parent
.Width = Range("B12:H27").Width
End With

The outside quotes have me confused. Thanks again for your
help.

 

[Bob Phillips]

With ActiveChart.Parent
.Width = Range("B2:H" & myRow + 10).Width
End With

 

[Tom Ogilvy]

for i = 0 to 3
With Activechart.Parent
.width = Range("B2:H17").Offset(10*i,0).Width
End With
Next

But since you are always looking at Columns B:H, the width should be the
same.

But just to illustrate:

Sub Tester8()
For i = 0 To 3
' With ActiveChart.Parent
Debug.Print i, Range("B2:H17") _
.Offset(10 * i, 0).Address
' End With
Next

End Sub

Produces:

0 $B$2:$H$17
1 $B$12:$H$27
2 $B$22:$H$37
3 $B$32:$H$47