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, Jul 17 '16 at 18:08

Gary's Student · Answer 1 · 2016-07-17T18:23:25.980

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 '16 at 18:23

answered Jul 17 '16 at 18:08

Gary's Student

95,722
10
59
99

I would qualify the `Ranges` as well, for the same reason. – Doug Glancy Jul 17 '16 at 18:11
@DougGlancy I do not understand. – Gary's Student Jul 17 '16 at 18:14
1

I mean put periods in front of each "Range". – Doug Glancy Jul 17 '16 at 18:15
1

@DougGlancy See my **EDIT#2** – Gary's Student Jul 17 '16 at 18:23
That's true! Thanks. Never thought of it that way. I would still do it though, for readability if nothing else. Got my vote. – Doug Glancy Jul 17 '16 at 18:27
1

re: *'If the Cells() are qualified, the Range() does not need to be'* See [Is the . in .Range necessary when defined by .Cells?](http://stackoverflow.com/questions/36368220/is-the-in-range-necessary-when-defined-by-cells) for an extended discussion on this. – Jul 17 '16 at 19:23

score 0 · Answer 2 · answered Jul 18 '16 at 18:21

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

setting dynamic range vba

2 Answers2