Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get a variable range
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 - |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get a variable range
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) "Metin" wrote in message ... 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 - |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get a variable range
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- "Bob Phillips" wrote: 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) "Metin" wrote in message ... 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 - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get a variable range
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) "Metin" wrote in message ... 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- "Bob Phillips" wrote: 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) "Metin" wrote in message ... 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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
setting a range variable equal to the value of a string variable | Excel Programming | |||
Variable in range | Excel Programming | |||
variable range: l just can't get there! | Excel Programming | |||
Variable Range | Excel Programming | |||
Problem trying to us a range variable as an array variable | Excel Programming |