Solved

Calculate Range area of Sheet1 and use it in a macro

Posted on 2011-02-25
13
790 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:cdebel
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
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!

 

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:cdebel
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:cdebel
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:cdebel
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 250 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

739 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