Solved

Calculate Range area of Sheet1 and use it in a macro

Posted on 2011-02-25
13
744 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
  • 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
 

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

746 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now