• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 171
  • Last Modified:

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.
0
bearblack
Asked:
bearblack
1 Solution
 
Saqib Husain, SyedEngineerCommented:
Application.SumIf("Vendor", "RC1", "Freight_Cost")

Is RC1 also a range name?
0
 
bearblackAuthor Commented:
no it is not it is the cell directly to the left
0
 
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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
Saqib Husain, SyedEngineerCommented:
Oops.

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

To the left of "What"?
0
 
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
 
bearblackAuthor Commented:
Thanks for your help
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now