excel macro using variable to define range of a integer failure

Question

I am making an integer myrng1 that is defined by

Set myrng1 = Range("B3 :" & Cells(LastRow, 2))`

in the code

LastRow = Cells(Rows.Count, "B").End(xlUp).Row
Set myrng1 = Range("B3 :" & Cells(LastRow, 2))
Set myrng2 = Range("D3 :" & Cells(LastRow, 4))

ActiveCell.FormulaR1C1 = "=SUMIF(myrng1,RC[-1],myrng2)"
Range("B15").Select
Selection.AutoFill Destination:=Range("B15:B30"), Type:=xlFillDefault
Range("B15:B30").Select

However what ever I do, I come across Method 'Range' of object '_Global' failed Error

Community · Accepted Answer · 2017-05-23 10:31:41Z

4

The simplest way to do it is

Set myrng1 = Range("B3:B" & LastRow)

Else if you want to follow your approach then as Bernard mentioned, use the .Address property.

Change your code to

Set myrng1 = Range("B3:" & _
                   Cells(LastRow, 2).Address( _
                                             rowabsolute:=False, _
                                             columnabsolute:=False) _
                   )

or this which does the same thing.

Set myrng1 = Range("B3:" & _
                   Replace(Cells(LastRow, 2).Address, "$", "") _
                   )

Also I hope you have declared LastRow as Long and not as Integer as you may get an overflow error in xl2007+

One last thing. Your Cells object is not fully qualified which can give you error. Fully qualify them. For example (Notice the DOTS)

Set myrng1 = ThisWorkbook.Sheets("Sheet1").Range("B3:B" & LastRow)

or

With ThisWorkbook.Sheets("Sheet1")
    Set myrng1 = .Range("B3:" & _
                       .Cells(LastRow, 2).Address( _
                                                  rowabsolute:=False, _
                                                  columnabsolute:=False) _
                        )
End With

EDIT Thanks to pnuts for pointing this out

Once you solve the above problem, you will face more problems. For example "=SUMIF(myrng1,RC[-1],myrng2)". Anything between the " will be considered a string. You may want to use this instead

"=SUMIF(" & myrng1.Address & ",RC[-1]," & myrng2.Address & ")"

Again, you may want to use .Address without the "$" as mentioned above.

One more thing. If row 30 is the lastrow then you can fill the formula in all the cells in one go instead of using AutoFill See this Example

With ThisWorkbook.Sheets("Sheet1")
    .Range("B15:B30").Formula = "=SUMIF(" & _
                                        myrng1.Address & _
                                        ",A15," & _
                                        myrng2.Address & ")"
End With

Or

With ThisWorkbook.Sheets("Sheet1")
    .Range("B15:B" & lastrow).Formula = "=SUMIF(" & _
                                        myrng1.Address & _
                                        ",A15," & _
                                        myrng2.Address & ")"
End With

Furthermore, I would like to draw your attention to this INTERESTING READ :p

edited May 23, 2017 at 10:31

CommunityBot

11 silver badge

answered Jan 29, 2014 at 5:40

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

C Jones Over a year ago

Hi there, very grateful for the help you have given me, however whenever i try to run the editted secition, it is giving me a runtime error 1004, application defines or object defined error. any help??

Siddharth Rout Over a year ago

Can you update your question with the latest code that you are using? Also let us know which line is giving you the error

Bernard Saucier · Accepted Answer · 2014-01-29 04:33:46Z

1

If the content of Cells(LastRow, 2) is not a valid cell reference (like B3 is), the method will fail.

If you want to use the address of .Cells(LastRow, 2), then replace it with .Cells(LastRow, 2).Address(rowabsolute:=False, columnabsolute:=False).

EDIT: You could also use .Address with no parameter but you would get something like $A$1 (absolute referencing).

answered Jan 29, 2014 at 4:33

Bernard Saucier

2,2601 gold badge21 silver badges29 bronze badges

Collectives™ on Stack Overflow

excel macro using variable to define range of a integer failure

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related