?
Solved

Concatenation of Excel cell ranges

Posted on 2003-03-17
5
Medium Priority
?
236 Views
Last Modified: 2010-05-03
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
Comment
Question by:pick1any1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 11

Expert Comment

by:supunr
ID: 8156227
that line should be


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

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

0
 
LVL 11

Expert Comment

by:supunr
ID: 8156247
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
 
LVL 3

Accepted Solution

by:
DocM earned 200 total points
ID: 8156629
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
 

Author Comment

by:pick1any1
ID: 8409531
Thanks for the help
0
 
LVL 3

Expert Comment

by:DocM
ID: 8409804
You are welcome.
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month12 days, 17 hours left to enroll

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question