Concatenation of Excel cell ranges
Posted on 2003-03-17
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
Range("E + iNum:E + jNum").Select
Application.CutCopyMode = False
Range("C & lNum").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
lNum = lNum + 1
iNum = iNum + 6
jNum = jNum + 6
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
Any ideas on how to concantenate the cell references?