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: 837
  • Last Modified:

Calculate Range area of Sheet1 and use it in a macro

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
chaverly
Asked:
chaverly
  • 4
  • 4
  • 3
  • +1
1 Solution
 
gamarrojgqCommented:
HI,

Try using SET, so insted of

strRange = CalculateRowMajorOrder

Use

SET strRange = CalculateRowMajorOrder
0
 
Christian de BellefeuilleProgrammerCommented:
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
 
chaverlyAuthor Commented:
Do I declare it as a String or an Object?
0
Independent Software Vendors: 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!

 
chaverlyAuthor Commented:
It is still breaking at that line.
0
 
gamarrojgqCommented:
You should declare it as Range
0
 
Christian de BellefeuilleProgrammerCommented:
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
 
Christian de BellefeuilleProgrammerCommented:
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
 
Rory ArchibaldCommented:
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
 
Christian de BellefeuilleProgrammerCommented:
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
 
Rory ArchibaldCommented:
It calculates the cells, that's all. It just does them in the same order that XL2000 and prior did.
0
 
chaverlyAuthor Commented:
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
 
Rory ArchibaldCommented:
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
 
chaverlyAuthor Commented:
That is exactly what I need!! Thank you all for your assistance.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now