pick1any1
asked on
Concatenation of Excel cell ranges
Could someone please provide some ideas in regard to an Excel spreadsheet macro.
I am trying to get a macro to format a worksheet by copying a range of cells then transposing when pasting to the new worksheet ie columns become rows. I need the cell ranges to be able to count up, an example of what I have written so far is:
Dim iNum, jNum, kNum, lNum As Integer
iNum = 2
jNum = 7
For kNum = 1 To 1000 Step 1
Sheets("CCS.8.5").Select
Range("E + iNum:E + jNum").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("C & lNum").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
......
.....
....
lNum = lNum + 1
iNum = iNum + 6
jNum = jNum + 6
Next kNum
Error on running is "Run time error 1004 Method 'Range' of '_Global' failed"
Debugger highlights line Range("E + iNum:E + jNum").Select as the problem
I have tried using '&' operator instead of '+', also tried creating a string variable eg
cellStart = E & iNum
cellFinish = E & jNum
Range("cellStart:cellFinis
Any ideas on how to concantenate the cell references?
regards
P
I am trying to get a macro to format a worksheet by copying a range of cells then transposing when pasting to the new worksheet ie columns become rows. I need the cell ranges to be able to count up, an example of what I have written so far is:
Dim iNum, jNum, kNum, lNum As Integer
iNum = 2
jNum = 7
For kNum = 1 To 1000 Step 1
Sheets("CCS.8.5").Select
Range("E + iNum:E + jNum").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("C & lNum").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
......
.....
....
lNum = lNum + 1
iNum = iNum + 6
jNum = jNum + 6
Next kNum
Error on running is "Run time error 1004 Method 'Range' of '_Global' failed"
Debugger highlights line Range("E + iNum:E + jNum").Select as the problem
I have tried using '&' operator instead of '+', also tried creating a string variable eg
cellStart = E & iNum
cellFinish = E & jNum
Range("cellStart:cellFinis
Any ideas on how to concantenate the cell references?
regards
P
how about code like this
Dim i as long
Dim j as long
For i = 1 to 10000
Sheets("Sheet1").Range("C"
Next i
Good Luck!
Dim i as long
Dim j as long
For i = 1 to 10000
Sheets("Sheet1").Range("C"
Next i
Good Luck!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the help
You are welcome.
Range("E" & iNum & ":E" & jNum).Select
same here...
Range("C" & lNum).Select