my VBA is bad, very bad

You should see mine!.

--
Biff
Microsoft Excel MVP


"John C" <johnc@stateofdenial wrote in message
...
Ok, my VBA is bad, very bad, but I have a 'partial' solution. Currently, I
have it set to a macro that I run, but because of my bad VBA, it still
shows
the blinking dashes around the 'handwritten formula' from the VBA copy.

Building off the TryThis example, with Try in C1, This in C2, also row in
E1, column in E2.
In a 'dummy' cell, I have the following (my dummy cell is H8).
="="&LEFT($C$1,3)&LEFT($C$2,4)
TryThis is defined as:
=INDEX(OFFSET('Sheet1'!$A$1,0,0,10,1),'Sheet1'!$E$ 1,'Sheet1'!$E$2)

And currently, my macro coding is as follows:

Sub ConvertStringToFormula()
Range("H8").Copy
With ActiveCell
.PasteSpecial
ActiveCell.Formula = ActiveCell.Text
Range("H9").Select
End With
End Sub

As I said, it's crude, if you know more than me about VBA (which shouldn't
be difficult), you can modify to suit your needs.




--
John C


"T. Valko" wrote:

Here's how it can be done using CHOOSE.

Assume you have named ranges defined using OFFSET:

RngOne
RngTwo

A1 = Rng
A2 = Two

=INDEX(CHOOSE(MATCH(A1&A2,{"RngOne","RngTwo"},0),R ngOne,RngTwo),1)

Note that you're limited to the number of ranges that CHOOSE can handle
depending on which version of Excel you're using. In Excel 2007 you can
have
up to 254 ranges. In other versions it's limited to 29.

If you have more than a few ranges you can create a table and MATCH the
name
in the table rather than hardcode the names as I've done in the above
formula.

J1 = RngOne
J2 = RngTwo

=INDEX(CHOOSE(MATCH(A1&A2,J1:J2,0),RngOne,RngTwo), 1)

Although I've never had to do it, I imagine there should be some way to
make
this work if you have more ranges than CHOOSE can handle. Probably a
nested
CHOOSE CHOOSE combination would work.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
INDIRECT requires a text representation of a *valid reference*. The
OFFSET
formula does not meet this requirement. When you call SGDCalendar
you're
actually calling the named OFFSET *formula* which is not a text
representation of a valid reference.

About the only way I know of to get around this invovles using the
CHOOSE
function. However, since you're concatentating strings to build the
named
range this adds complexity to the mix. I love complexity! <g

There's nothing "special" about your named range, it's just a static
range. Why don't you just use:

SGDCalendar =Sheetname!$A$1:$A$4000

Replace "Sheetname" with the actual sheet name.

--
Biff
Microsoft Excel MVP


"nashism" wrote in message
...
Hi,

I have defined a range using the following syntax:

SGDCalendar=Offset(A$1,0,0,4000,1) in a worksheet

I have similarly defined other names like MYRCalendar, IDRCalendar
primarily because I have other data that is labeled starting SGD, MYR
etc that I would like to combine this with.

now when I try to get a value from the range as INDEX(SGDCalendar,1,1)
it works correctly

but if I do something Iike INDEX(Indirect("SGD"&"Calendar"),1,1) it
gives me an #REF! error (note that "SGD" and "Calendar" will be
strings that will come from elsewhere in my workbook)

I have troubled many people around me to solve this but cant seem to
get any further. Any help would be greatly appreciated!

ciao
Nash