Creating a range reference in Excvel VBA from text in a spreadsheet cell.

I wish to convert the following spreadsheet formula to one calculated in VBA.

=ROUND(SUMIFS(INDIRECT(B2),TM1_cats,A2,TM1_month,G2)*1000,0)

The problem I'm having is replicating the "Indirect(b2)" piece of the formula in VBA. I keep getting Error 1004. Does anyone have the right syntax for this? I've been searching a while but can't find anything that works.

Thanks
pfmurrayAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AlanConsultantCommented:
Hi,

Let's assume for the sake of example, that the cell:

YourWSName!B2

contains the text string:

="A1:A10" (six characters)

In VBA, you can return the range object (YourWSName!$A$1:$A$10) as follows:

Worksheet("YourWSName").Range(Worksheet("YourWSName").Range("B2").Value)

So, for example:

Worksheet("YourWSName").Range(Worksheet("YourWSName").Range("B2").Value).Address

=$A$1:$A$10


You can therefore use:

Worksheet("YourWSName").Range(Worksheet("YourWSName").Range("B2").Value)

in place of Indirect(B2).

HTH,

Alan.
Saurabh Singh TeotiaCommented:
You can also do this and it will give you the same result what you get in formula...

Dim i As Long

i = Application.Evaluate("=ROUND(SUMIFS(INDIRECT(B2),TM1_cats,A2,TM1_month,G2)*1000,0)")

Open in new window


Saurabh...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pfmurrayAuthor Commented:
Hi Alan, Saurabh.  Thanks for the suggestions but unfortunately neither work for me.

Alan - I just can't get the VBA to resolve this in a sumifs as there is a constant type mismatch. I should also have mentioned that I needed "B2" to be in the format in the VBA as cells(i,2) as the range name is in a list and I wanted the name to change after every iteration. This is my fault for not mentioning it

Saurabh - Yes, that should work however there seems to be a well documented issue that using some range names in an Evaluate needs Excel to write the data to a cell and call it back. It got too painful to think through!

I have decided to live with the speed issues as the Excel formulae work and since no-one else has responded I will award points equally. I'm still baffled as to why it's so hard to reference a cell's value as a VBA range name but I suppose that's my lack of understanding. Thanks for your efforts.
pfmurrayAuthor Commented:
Whilst both solutions may have worked with more effort and understanding the underlying issues weren't resolved and time was of the essence
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.