setting dynamic range vba

Question

I have been searching the web for a reason why my range won't work when referred to later on, but am a loss still. Any help is greatly appreciated!

What I am attempting to do is set a range based on a potentially moving last row (I import data and each week it grows). However, when I get to the Set ExpenseNameRange, for some reason it is not considering it to be a range.

I need this to use later for a range in a sumproduct formula.

Below is my code. Any help is greatly appreciated!

Dim Profitability As Worksheet
Dim Time As Worksheet
Dim Expense As Worksheet
Dim ExpenseValueRange As Range
Dim ExpenseNameRange As Range
Dim ExpenseDateRange As Range
Dim e As Integer
Dim d As Integer


Set Carryover = ThisWorkbook.Worksheets("2016 Carryover Forecast")
Set Profitability = ThisWorkbook.Worksheets("Profitability")
Set Time = ThisWorkbook.Worksheets("SYNC Time")
Set Expense = ThisWorkbook.Worksheets("SYNC Expense")


finalrowexpense = Expense.Range("A100000").End(xlUp).Row
**Set ExpenseNameRange = Expense.Range(Cells(2, 12), Cells(finalrowexpense, 12))**
Set ExpenseDateRange = Expense.Range(Cells(2, 19), Cells(finalrowexpense, 19))
Set ExpenseValueRange = Expense.Range(Cells(2, 23), Cells(finalrowexpense, 23))
For e = 37 To 63

            employeename = Carryover.Cells(e, 33).Value

            For d = 34 To 41

            If employeename <> "" Then

            ExpenseSum = Application.WorksheetFunction.SumProduct(Month(ExpenseDateRange) = Month(Cells(35, d)), ExpenseNameRange = employeename)

            ExpenseSum = employeename.Offset(0, d).Value

try replacing that line with Set ExpenseNameRange = Expense.Range(Expense.Cells(2, 12), Expense.Cells(finalrowexpense, 12)) — Shai Rado
– Shai Rado, Commented Jul 17, 2016 at 18:08

Gary's Student · Accepted Answer · 2016-07-17 18:23:25Z

2

Consider:

With Expense
  Set ExpenseNameRange = Range(.Cells(2, 12), .Cells(finalrowexpense, 12))
  Set ExpenseDateRange = Range(.Cells(2, 19), .Cells(finalrowexpense, 19))
  Set ExpenseValueRange =Range(.Cells(2, 23), .Cells(finalrowexpense, 23))
End With

because, by itself, Cells() refers to the ActiveSheet.

EDIT#1:

Also you must fix the Cells() in the line with SUMPRODUCT().

EDIT#2:

If the Cells() are qualified, the Range() does not need to be:

Sub dural()
    Dim r As Range

    Sheets("Sheet1").Activate
    Sheets("Sheet1").Select

    With Sheets("Sheet2")
        Set r = Range(.Cells(1, 1), .Cells(2, 2))
        MsgBox r.Parent.Name
    End With
End Sub

edited Jul 17, 2016 at 18:23

answered Jul 17, 2016 at 18:08

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

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

6 Comments

Doug Glancy Over a year ago

I would qualify the Ranges as well, for the same reason.

Gary's Student Over a year ago

@DougGlancy I do not understand.

Doug Glancy Over a year ago

I mean put periods in front of each "Range".

Gary's Student Over a year ago

@DougGlancy See my EDIT#2

user4039065 Over a year ago

re: 'If the Cells() are qualified, the Range() does not need to be' See Is the . in .Range necessary when defined by .Cells? for an extended discussion on this.

|

mollum · Accepted Answer · 2016-07-18 18:21:18Z

Thanks for the awesome answers! I managed to follow the rabbit hole far enough and came up with something else that worked. It also looked like Sumifs was a much better option over sumproduct based on the reading I did. Sumifs don't call for arrays or any other special factors.

This is my looping code if anyone is interested.

Sub Organize_Expenses()

Dim Carryover As Worksheet
Dim Profitability As Worksheet
Dim Time As Worksheet
Dim Expense As Worksheet
Dim ExpenseValueRange As Range
Dim ExpenseNameRange As Range
Dim ExpenseMonthRange As Range
Dim ExpenseYearRange As Range
Dim VendorNameRange As Range
Dim e As Integer
Dim d As Integer
Dim a As Integer
Dim b As Integer


Set Carryover = ThisWorkbook.Worksheets("2016 Carryover Forecast")
Set Profitability = ThisWorkbook.Worksheets("Profitability")
Set Time = ThisWorkbook.Worksheets("SYNC Time")
Set Expense = ThisWorkbook.Worksheets("SYNC Expense")
finalrowexpense = Expense.Range("A100000").End(xlUp).Row

Set ExpenseNameRange = Expense.Range("L2:L" & finalrowexpense)
Set ExpenseMonthRange = Expense.Range("AC2:AC" & finalrowexpense)
Set ExpenseValueRange = Expense.Range("w2:w" & finalrowexpense)
Set ExpenseYearRange = Expense.Range("AD2:AD" & finalrowexpense)


            For d = 1 To 8
            For e = 37 To 56

            Employeename = Carryover.Cells(e, 33).Value

            If Employeename <> "" Then

            ExpenseSum = Application.WorksheetFunction.SumIfs(ExpenseValueRange, ExpenseNameRange, Employeename, ExpenseMonthRange, d, ExpenseYearRange, "2016")



Carryover.Cells(e, 33).Offset(0, d).Value = ExpenseSum

            End If

            Next e

            Next d

 End Sub

Collectives™ on Stack Overflow

setting dynamic range vba

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related