We help IT Professionals succeed at work.

How do you use Set VBA Ranges in formulas?

bearblack
bearblack asked
on
With Sum_Freight_Calc
            .Value = Application.SumIf(Vendor, RC1, Freight_Cost)
        End With

I have defined and set the ranges "Vendor" and "Freight_Cost" in VBA. How is the proper syntax use these defined ranges to input values.
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Application.SumIf("Vendor", "RC1", "Freight_Cost")

Is RC1 also a range name?
bearblackGlobal Program Manager

Author

Commented:
no it is not it is the cell directly to the left
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
Hello,

if you have named ranges, regardless whether they were defined with VBA or via the user interface, you can reference them like a cell range, i.e.

[Vendor] or Range("Vendor")

@ssaqibh, your suggestion just contains strings, not the range references.

cheers, teylyn
CERTIFIED EXPERT

Commented:
Oops.

no it is not it is the cell directly to the left

To the left of "What"?
Analyst Assistant
CERTIFIED EXPERT
Commented:
If you want the cell to the left of Sum_Freight_Calc, assuming that refers to a range use Offset.

So with teylyn's Range, and assuming Vendor and Freight_Cost are named ranges,  the whole thing would look like this.

With Sum_Freight_Calc
            .Value = Application.SumIf(Range("Vendor",.Offset(, -1), Range("Freight_Cost"))
End With

Open in new window

If Vendor and Freight_Cost aren't named ranges but declared and defined in the code.
With Sum_Freight_Calc
            .Value = Application.SumIf(Vendor,.Offset(, -1), Freight_Cost)
End With

Open in new window

bearblackGlobal Program Manager

Author

Commented:
Thanks for your help

Explore More ContentExplore courses, solutions, and other research materials related to this topic.