How to get a variable range

[Guest]

Hallo,

I have the next macro, but it gives me an error message: "Run-time error
1004: Method 'Range' of object '_Worksheet' failed". I need a variable range
in batches of 96 rows, but I think I defined it wrong. Please help me.

Sub Abs_Bkg2()

Dim rng As Range
Dim lRownum As Long
Dim i As Long
Dim j As Long
Dim exSh As Worksheet
Dim wks As Worksheet
Set exSh = Worksheets("raw data from spad it")
Set wks = Worksheets("Calculated Data")

lRownum = exSh.Range("A1").SpecialCells(xlCellTypeLastCell).Row

i = (lRownum - 1) / 96
For j = 1 To i
With wks.Range("V[j*96-94]:Y[j*96+1]")
.FormulaR1C1 = _
"='raw data from spad it'!RC[-7]-'Calculated Data'!R4C[16]"
End With
Next j

End Sub

Thanks,
- Metin -

 

[Bob Phillips]

I guess the error is in this line

With wks.Range("V[j*96-94]:Y[j*96+1]")

untested, but try

With wks.Range("V" & (j*96-94) & ":Y" & (j*96+1))

and I am certainly not claiming that I have checked the logic for 96 item
batches


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks it worked. But I discovered I have to put the logic for 96 item
batches in an other line in the macro. See the next line.

..FormulaR1C1 = _
"='raw data from spad it'!RC[-7]-'Calculated Data'!R4C[16]"

I want to put "j * 96 - 92" instead of '4' in R4C[16]

I tried a lot of ways to write but I didn't succeed. Please help.
-Metin-

I guess the error is in this line

With wks.Range("V[j*96-94]:Y[j*96+1]")

untested, but try

With wks.Range("V" & (j*96-94) & ":Y" & (j*96+1))

and I am certainly not claiming that I have checked the logic for 96 item
batches


--

HTH

RP
(remove nothere from the email address if mailing direct)

Hallo,

I have the next macro, but it gives me an error message: "Run-time error
1004: Method 'Range' of object '_Worksheet' failed". I need a variable range
in batches of 96 rows, but I think I defined it wrong. Please help me.

Sub Abs_Bkg2()

Dim rng As Range
Dim lRownum As Long
Dim i As Long
Dim j As Long
Dim exSh As Worksheet
Dim wks As Worksheet
Set exSh = Worksheets("raw data from spad it")
Set wks = Worksheets("Calculated Data")

lRownum = exSh.Range("A1").SpecialCells(xlCellTypeLastCell).Row

i = (lRownum - 1) / 96
For j = 1 To i
With wks.Range("V[j*96-94]:Y[j*96+1]")
.FormulaR1C1 = _
"='raw data from spad it'!RC[-7]-'Calculated Data'!R4C[16]"
End With
Next j

End Sub

Thanks,
- Metin -

 

[Bob Phillips]

Same principle

FormulaR1C1 = _
"='raw data from spad it'!RC[-7]-'Calculated Data'!R" & (j* 96 - 92) &
" C[16]"

You have a string and you want to use a variable within that, close the
string with a quote, concatenate the variable & var & and open the string
again with a quote.

--

HTH

RP
(remove nothere from the email address if mailing direct)

Thanks it worked. But I discovered I have to put the logic for 96 item
batches in an other line in the macro. See the next line.

.FormulaR1C1 = _
"='raw data from spad it'!RC[-7]-'Calculated Data'!R4C[16]"

I want to put "j * 96 - 92" instead of '4' in R4C[16]

I tried a lot of ways to write but I didn't succeed. Please help.
-Metin-

I guess the error is in this line

With wks.Range("V[j*96-94]:Y[j*96+1]")

untested, but try

With wks.Range("V" & (j*96-94) & ":Y" & (j*96+1))

and I am certainly not claiming that I have checked the logic for 96 item
batches


--

HTH

RP
(remove nothere from the email address if mailing direct)

Hallo,

I have the next macro, but it gives me an error message: "Run-time error
1004: Method 'Range' of object '_Worksheet' failed". I need a variable range
in batches of 96 rows, but I think I defined it wrong. Please help me.

Sub Abs_Bkg2()

Dim rng As Range
Dim lRownum As Long
Dim i As Long
Dim j As Long
Dim exSh As Worksheet
Dim wks As Worksheet
Set exSh = Worksheets("raw data from spad it")
Set wks = Worksheets("Calculated Data")

lRownum = exSh.Range("A1").SpecialCells(xlCellTypeLastCell).Row

i = (lRownum - 1) / 96
For j = 1 To i
With wks.Range("V[j*96-94]:Y[j*96+1]")
.FormulaR1C1 = _
"='raw data from spad it'!RC[-7]-'Calculated Data'!R4C[16]"
End With
Next j

End Sub

Thanks,
- Metin -