Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

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:cellFinish").Select
Any ideas on how to concantenate the cell references?
regards
P
0
pick1any1
Asked:
pick1any1
  • 2
  • 2
1 Solution
 
supunrCommented:
that line should be


Range("E" & iNum & ":E" & jNum).Select  

same here...
Range("C" & lNum).Select

0
 
supunrCommented:
how about code like this

Dim i as long
Dim j as long

For i = 1 to 10000
    Sheets("Sheet1").Range("C" & i).Value = Sheets("CCS.8.5").Range("E" & i).Value
Next i

Good Luck!
0
 
DocMCommented:
Use this syntax instead:

Always put the Sheet name before the range:
Example: Sheets("Sheet2").Range("C" & lNum).Select

Do not put any variables inside the quotes:
This is bad :  Range("E + iNum:E + jNum").Select  
This is good : Range("E" & iNum & ":E" & jNum).Select
   
Dim iNum, jNum, kNum, lNum As Integer
   iNum = 2
   jNum = 7
   lNum = 1
   For kNum = 1 To 1000 Step 1
   Sheets("Sheet1").Select
   Sheets("Sheet1").Range("E" & iNum & ":E" & jNum).Select
   Selection.Copy
   Sheets("Sheet2").Select
   Sheets("Sheet2").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
   Stop
   Application.CutCopyMode = False

0
 
pick1any1Author Commented:
Thanks for the help
0
 
DocMCommented:
You are welcome.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now