Please debug this one formula in VB
 

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

Please debug this one formula in VB
 

"=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

Please debug this one formula in VB
 

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

Please debug this one formula in VB
 

Well, the sformula definitely passed through and did its
thing. Now, if I can just figure out why it didn't pull
the line number like I thought it would <LOL. I'll post
back later if I have trouble figuring that part out.

Thanks Bob



-----Original Message-----
Bruce,

How about

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


--

HTH

Bob Phillips

"Bruce Roberson" wrote in
message
...
Tom:

Ok, here it is in spreadsheet form.

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

Assuming the Active cell is in row 4163, and that the
first condition in the if statement is true, then the
result string will be:
SE~4161~0001

The first time this loop passes this line, condition 1
will be true and lines 1 in string format will be filled
into the string. But on the 2nd and successive times
through, condition l will not be true, in which case the
lines will be less because the previous value in lines 1
will have already been copied to lines 2, so that on the
2nd and successive passes, the value of the current
cursor
line minus the previous value of lines 1 (which will
then
become lines2) is substracted to get the new result. So,
the next pass might yield something like:

SE~150~0001


Note that the spreadsheet range lines1 is set to
substract
2 from the number of the current row for purposes of
this
particular string.


Does that help you to help me?

Thanks,


Bruce


.