?
Solved

Calculate Range area of Sheet1 and use it in a macro

Posted on 2011-02-25
13
Medium Priority
?
806 Views
Last Modified: 2012-05-11
The macro I have created copies the rows and columns in Sheet1 to Sheet3. However, my selection range varies.  Sometimes the area is Range("A1:D94").Select . Sometimes the area is Range("A1:D125").Select .  I need to calculate the CalculateRowMajorOrder and replace the Range("A5:G94").Select with Range(strRange).Select.  I have tried declaring strRange as a String and as an Object.  It always blows up at the 'strRange= CalculateRowMajorOrder line.
Am I using the method wrong?

Sheets("Sheet1").Select
    'strRange = CalculateRowMajorOrder
    Range("A5:G94").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("B1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("A1").Select
0
Comment
Question by:chaverly
[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
  • 4
  • 4
  • 3
  • +1
13 Comments
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 34984064
HI,

Try using SET, so insted of

strRange = CalculateRowMajorOrder

Use

SET strRange = CalculateRowMajorOrder
0
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34984184
I don't know what's the usage of this method, but it belong to a range.

So you got to call it this way:

Set MyVar = MyRange.CalculateRowMajorOrder
(http://msdn.microsoft.com/en-us/library/bb226058(v=office.12).aspx)
0
 

Author Comment

by:chaverly
ID: 34984189
Do I declare it as a String or an Object?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:chaverly
ID: 34984203
It is still breaking at that line.
0
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 34984206
You should declare it as Range
0
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34984439
gamarrojgg: I've tried it myself.  Either this method doesn't work, or is very badly documented on MSDN.  Even if you declare it as range it won't work.  

' This fail
    Dim MyVar As Variant
    Set MyVar = Sheet1.Range("C3:D4").CalculateRowMajorOrder

According to MSDN, CalculateRowMajorOrder return a variant... so you could just declare it as "Dim MyVar" or like i did.  But it still doesn't work.

@CHAVERLY:
Can you tell us what you are trying to do?  Copy a Range of cells to another location?
Or you just want to SUM all the cells from a range, and return the value?

0
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34984508
Usually only this line would do the job to calculate the sum of a specific range and display it...

MsgBox Application.WorksheetFunction.Sum(Range("Sheet1!A5:G94"))
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34984546
CalculateRowMajorOrder doesn't return anything - it calculates the specified range cell by cell from top left to bottom right, so I don't know what you are trying to achieve with it.
0
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34984606
I think that he is trying to sum the range and return the value.

Rorya, i've tried this and it doesn't give any error message:
Sheet1.Range("C3:D4").CalculateRowMajorOrder

Open in new window


But what's the usage of this method? I don't see any result in status bar of excel or anything.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34984629
It calculates the cells, that's all. It just does them in the same order that XL2000 and prior did.
0
 

Author Comment

by:chaverly
ID: 34984905
Sheet1 is a spreadsheet created in Excel of items that were returned to our company.  It has 4 columns: Item Id, price, quantity returned, and Price times Quantity.   Sheet2 is a connection to the database that supplies all the results of a Query that provides the Item Id and the location where the item should be returned to inventory. Sheet3 has a copy of everything in Sheet1, but starting in column B instead of column A. Column A uses VLOOKUP to provide the Location from Sheet2.  

Sometimes there are 20 items (or rows) in Sheet1.  Sometimes there are 94, sometimes 55. my Range is Sheet1.(A1:D20).select or Sheet1.(A1:D94).select or Sheet1.(A1:D55).select respectively to the previous statement.

 I need to calculate the number of rows in my macro, so it doesn't miss any items and doesn't add any rows where there are no items.  I thought the Range method would calculate that for me, but it isn't working.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 1000 total points
ID: 34985000
I think what you want is:

With Sheets("Sheet1")
    .Range("A5:G" & .Cells(.rows.count, "A").End(xlUp).Row).Copy
End With
    Sheets("Sheet3").Range("B1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False

Open in new window

0
 

Author Closing Comment

by:chaverly
ID: 34988053
That is exactly what I need!! Thank you all for your assistance.
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 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