Solved

How do you use Set VBA Ranges in formulas?

Posted on 2012-03-12
6
154 Views
Last Modified: 2012-03-12
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
Comment
Question by:bearblack
6 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37711506
Application.SumIf("Vendor", "RC1", "Freight_Cost")

Is RC1 also a range name?
0
 
LVL 2

Author Comment

by:bearblack
ID: 37711729
no it is not it is the cell directly to the left
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 37711814
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37711839
Oops.

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

To the left of "What"?
0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 37711887
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
 
LVL 2

Author Closing Comment

by:bearblack
ID: 37711969
Thanks for your help
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now