I didn't intend to use these long formulas in my vb
script. But on this one occassion, it is absolutely
necessary. I have live spreadsheet ranges that are
calculating what line the active cell is and I have to
capture that number at the appropriate time in order to do
the right string for this one line. So, at the point I
have this formula is the precise moment in the script
where I need to pass the value of this formula

The way it is here it beeps at me at me with the message
of a compile error. And I've worked and I've listened to
how Bob and Tom said it works, but I still don't get it,
at least not for this long a formula anyway. So, please
just copy, paste, and edit as necessary to show me the
proper syntax in this case.

ActiveCell.Formula = "=IF(FIXED(INDEX("&Range("Summary")
&","&Range("ROWSUM"&"+1,2),0,TRUE)" _
=INDEX("&Range("Summary")&","&Range("ROWSUM")
&"+1,1),="SE~"&FIXED(Range("LINES1")&",0,TRUE)"&"~ 0001, _
SE~"&"FIXED("Range("LINES1")-Range("LINES2")&",0)~0001)"

Otherwise, I will continue to be extremely frustrated
trying to learn it by trial and error.

Thanks,


Bruce

"=IF(FIXED(INDEX("&Range("Summary")
says to append the value in the range("Summary") to the preceding string. I
doubt that is what you want. But with such ambiguities as this, it would be
hard to tell what works and what doesn't.

Show us what a working identical worksheet formula looks like (what your end
result would be) and then someone might be able to tell you if there is a
problem with your string.

Regards,
Tom Ogilvy


"Bruce Roberson" wrote in message
...
I didn't intend to use these long formulas in my vb
script. But on this one occassion, it is absolutely
necessary. I have live spreadsheet ranges that are
calculating what line the active cell is and I have to
capture that number at the appropriate time in order to do
the right string for this one line. So, at the point I
have this formula is the precise moment in the script
where I need to pass the value of this formula

The way it is here it beeps at me at me with the message
of a compile error. And I've worked and I've listened to
how Bob and Tom said it works, but I still don't get it,
at least not for this long a formula anyway. So, please
just copy, paste, and edit as necessary to show me the
proper syntax in this case.

ActiveCell.Formula = "=IF(FIXED(INDEX("&Range("Summary")
&","&Range("ROWSUM"&"+1,2),0,TRUE)" _
=INDEX("&Range("Summary")&","&Range("ROWSUM")
&"+1,1),="SE~"&FIXED(Range("LINES1")&",0,TRUE)"&"~ 0001, _
SE~"&"FIXED("Range("LINES1")-Range("LINES2")&",0)~0001)"

Otherwise, I will continue to be extremely frustrated
trying to learn it by trial and error.

Thanks,


Bruce

Bruce,

Much too hard to try and get into your mind and work out what you are trying
to do.

Suggestion:

Type out the formula that you want to see on the worksheet, with correct
values. Tell us what ranges Summary, ROWSUM, LINES1 and LINES2 cover, and
the values therein, and which you want cell references and which values, and
it will be easy then.

--

HTH

Bob Phillips

"Bruce Roberson" wrote in message
...
I didn't intend to use these long formulas in my vb
script. But on this one occassion, it is absolutely
necessary. I have live spreadsheet ranges that are
calculating what line the active cell is and I have to
capture that number at the appropriate time in order to do
the right string for this one line. So, at the point I
have this formula is the precise moment in the script
where I need to pass the value of this formula

The way it is here it beeps at me at me with the message
of a compile error. And I've worked and I've listened to
how Bob and Tom said it works, but I still don't get it,
at least not for this long a formula anyway. So, please
just copy, paste, and edit as necessary to show me the
proper syntax in this case.

ActiveCell.Formula = "=IF(FIXED(INDEX("&Range("Summary")
&","&Range("ROWSUM"&"+1,2),0,TRUE)" _
=INDEX("&Range("Summary")&","&Range("ROWSUM")
&"+1,1),="SE~"&FIXED(Range("LINES1")&",0,TRUE)"&"~ 0001, _
SE~"&"FIXED("Range("LINES1")-Range("LINES2")&",0)~0001)"

Otherwise, I will continue to be extremely frustrated
trying to learn it by trial and error.

Thanks,


Bruce