How do you use Set VBA Ranges in formulas?

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.
LVL 2
bearblackGlobal Program ManagerAsked:
Who is Participating?
 
NorieVBA ExpertCommented:
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

0
 
Saqib Husain, SyedEngineerCommented:
Application.SumIf("Vendor", "RC1", "Freight_Cost")

Is RC1 also a range name?
0
 
bearblackGlobal Program ManagerAuthor Commented:
no it is not it is the cell directly to the left
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
0
 
Saqib Husain, SyedEngineerCommented:
Oops.

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

To the left of "What"?
0
 
bearblackGlobal Program ManagerAuthor Commented:
Thanks for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.