need help with R1C1 reference and general code

[derickc]

Here's what I've written to do an edit/replace across multiple columns:

For x = 20 To 24

Range("R[7]C[x]:R[1310]C[x]").Select
Selection.Replace What:="ACAS", Replacement:=Range("R[6]C[x]").Value

x = x + 1

Next x


I'm probably not using the for/next correctly nor the R1C1 reference style. Can someone tell me where I'm going wrong and what I can change to make this work?

thank you!

-dc

 

[Russell Hauf]

On 2002-08-29 09:27, derickc wrote:
Here's what I've written to do an edit/replace across multiple columns:

For x = 20 To 24

Range("R[7]C[x]:R[1310]C[x]").Select
Selection.Replace What:="ACAS", Replacement:=Range("R[6]C[x]").Value

x = x + 1

Next x


I'm probably not using the for/next correctly nor the R1C1 reference style. Can someone tell me where I'm going wrong and what I can change to make this work?

thank you!

-dc

You are correct in that you are not using the For-Next correctly. When you use For-Next, the Next statement automatically increments your counter varaible, so you don't need the x=x+1 line. Also, instead of .Value, you can use .Text.

Also, instead of R1C1, you can use the Range object and the Cells property. Something like this should work:

<pre><font color='#000000'>
<font color='#000080'>Sub</font> rhReplace()

<font color='#000080'>Dim</font> lngCol <font color='#000080'>As</font> <font color='#000080'>Long</font>

<font color='#000080'>For</font> lngCol = 20 To 24
Range(Cells(7, lngCol), Cells(1310, lngCol)).Replace What:="ACAS", Replacement:=Cells(6, lngCol).Text
<font color='#000080'>Next</font> lngCol

<font color='#000080'>End</font> <font color='#000080'>Sub</font>

</font></pre>

Hope this helps,

Russell

 

[derickc]

Russell,
Worked perfectly. Thank you very much! Let me know if you are ever in Vero Beach, FL and I'll buy you a beer. Take care.

-derick