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

score 4 · Accepted Answer · edited May 23 '17 at 10:31

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

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?? — C Jones, Jan 30 '14 at 03:41
Can you update your question with the latest code that you are using? Also let us know which line is giving you the error — Siddharth Rout, Jan 30 '14 at 06:37

score 1 · Answer 2 · answered Jan 29 '14 at 04:33

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).

excel macro using variable to define range of a integer failure

2 Answers2