Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Calculate Range area of Sheet1 and use it in a macro

Posted on 2011-02-25
13
Medium Priority
?
817 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Technology Partners: 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!

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

610 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