Solved

How do you use Set VBA Ranges in formulas?

Posted on 2012-03-12
6
157 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

685 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