RC Cell addressing woes

  • Thread starter Thread starter toships
  • Start date Start date
T

toships

Hi

I have a VBA script which runs fine

But when I replace

ws_temp.Range("E" & i )Value = min_mse2

with

ws_temp.Range("R" & i & "C5").Value = min_mse2

then I get an error saying that
Method "Range" of object "_Worksheet" failed

Please help. I need to address the cell in R1C1 format in order t
insert it in a loop with row number changing for each iteration

Thank you in advance
 
What makes you think you can't loop through a range of cells using "A1"
format ?

Try something like the following:

Sub Test()
Dim i As Long
For i = 1 To 5
MsgBox Range("E" & i).Address & " " & Range("E" & i).Value
Next 'i
End Sub

' or

Sub Test2()
Dim i As Long
For i = 1 To 5
MsgBox Cells(i, 5).Address & " " & Cells(i, 5).Value
Next 'i
End Sub

Regards

Trevor
 
If the worksheet is not in R1C1 reference style you will get this error. Why
not use the Cells property

With ws_temp
.Range(.Cells(i,"R"),.Cells(5,"C5")).Value = min_mse2
End With

This can use all variables

With ws_temp
.Range(.Cells(i,m),.Cells(j,n)).Value = min_mse2
End With

where i and j are row numbers, m and n are column numbers.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
The range object doesn't accept R1C1 addressing regardless of the worksheet
reference style. As you state, Cells is the appropriate equivalent.

Also, the OP appears to want Row i, column 5, not a multicell range

For i = 1 to 10
With ws_temp
.Cells(i,5).Value = min_mse2
End With
Next


or without looping
ws_Temp.Cells(1,5).Resize(10).Value = min_mse2

--
regards,
Tom Ogilvy


Bob Phillips said:
If the worksheet is not in R1C1 reference style you will get this error. Why
not use the Cells property

With ws_temp
.Range(.Cells(i,"R"),.Cells(5,"C5")).Value = min_mse2
End With

This can use all variables

With ws_temp
.Range(.Cells(i,m),.Cells(j,n)).Value = min_mse2
End With

where i and j are row numbers, m and n are column numbers.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Click to expand...
 
ws_temp.Range("R" & i & ":C5").Value = min_mse2

would produce A1 style reference. For example, if i = 1 it would be
R1:C5 rather than the rc which is R1C5 (row1, column 5 = E1)

demo'd from the immediate window:
i = 1
? Range("R" & i & ":C5").Address
$C$1:$R$5
(R1:C5 would be upper right and lower left, so the result in the immediate
windows shifts to upper left/lower right)

The range object doesn't accept R1C1 style addressing. The Cells object
would be the appropriate equivalent

ws_temp.Cells(i,5).Value = min_mse2
 
Back
Top