Sub HeaderRecord()
ActiveCell.Cells(1, 1).Formula = "ISA~03~TX813080RP~00~ ~ZZ~17512548722~ZZ~TEX COMPTROLLER~" & Range ("YYMMDD") & "~" & Range("Time") & "~U~00401~000000093~0~P~^"
ActiveCell.Cells(2, 1).Formula = "GS~TF~17512548722 ~TEX COMPTROLLER~" & Range("CCYYMMDD") & "~" & Range ("Time") & "~2~X~004010"
ActiveCell.Cells(3, 1).Formula = "ST~813~0001"
ActiveCell.Cells(4, 1).Formula = "BTI~T6~082~47~TX~" & Range("CCYYMMDD") & "~~~~49~17512548722~SV~00000156C~" & Range("Btilineend")
ActiveCell.Cells(5, 1).Formula ="DTM~683~~~~RD8~"& CHOOSE(MOD(VALUE(INDEX(Range("Summary"),Range ("ROWSUM"+1,8)),100),31,28,31,30,31,30,31,31,30,31 ,30,31)


End Sub

I'm hung up right now on the line that has Cells(5,1) in it. Excel keeps giving me the message: Compile error: Expected: Expression, and it highlights the portion "MOD".

I've already tested the following formula, so the "MOD" part of the formula shouldn't be a problem. The standalone formula is:
="DTM~683~~~~RD8~"&CHOOSE(MOD(VALUE(INDEX(Summary, ROWSUM+1,8)),100),31,28,31,30,31,30,31,31,30,31,30 ,31)

and the result string of this formula comes out to be: DTM~683~~~~RD8~31

If I've got the syntax right on the first 4 activecell lines, then why is this one any different? Sure, it has a wild long formula, but if the formula is valid, then why won't it work in VB?

I couldn't wait till Monday to play with this, so I had to go to work and get my data files and bring them back to play at home. I told you guys this project was going to be a doozy.

Thanks,



Bruce

What doesn't work compared to what works"

CHOOSE(MOD(VALUE(INDEX(Range("Summary"),Range("ROW SUM"+1,8)),100),31,28,31,30,31,30,31,31,30,31,30,3 1)
CHOOSE(MOD(VALUE(INDEX(Summary,ROWSUM+1,8)),100),3 1,28,31,30,31,30,31,31,30,31,30,31)

If you want the first to be a string that is a legal formula, then you need to take out the Range stuff and just use a string.

Basically your syntax is screwed up.

Regards,
Tom Ogilvy





Bruce Roberson wrote in message ...
Sub HeaderRecord()
ActiveCell.Cells(1, 1).Formula = "ISA~03~TX813080RP~00~ ~ZZ~17512548722~ZZ~TEX COMPTROLLER~" & Range ("YYMMDD") & "~" & Range("Time") & "~U~00401~000000093~0~P~^"
ActiveCell.Cells(2, 1).Formula = "GS~TF~17512548722 ~TEX COMPTROLLER~" & Range("CCYYMMDD") & "~" & Range ("Time") & "~2~X~004010"
ActiveCell.Cells(3, 1).Formula = "ST~813~0001"
ActiveCell.Cells(4, 1).Formula = "BTI~T6~082~47~TX~" & Range("CCYYMMDD") & "~~~~49~17512548722~SV~00000156C~" & Range("Btilineend")
ActiveCell.Cells(5, 1).Formula ="DTM~683~~~~RD8~"& CHOOSE(MOD(VALUE(INDEX(Range("Summary"),Range ("ROWSUM"+1,8)),100),31,28,31,30,31,30,31,31,30,31 ,30,31)


End Sub

I'm hung up right now on the line that has Cells(5,1) in it. Excel keeps giving me the message: Compile error: Expected: Expression, and it highlights the portion "MOD".

I've already tested the following formula, so the "MOD" part of the formula shouldn't be a problem. The standalone formula is:
="DTM~683~~~~RD8~"&CHOOSE(MOD(VALUE(INDEX(Summary, ROWSUM+1,8)),100),31,28,31,30,31,30,31,31,30,31,30 ,31)

and the result string of this formula comes out to be: DTM~683~~~~RD8~31

If I've got the syntax right on the first 4 activecell lines, then why is this one any different? Sure, it has a wild long formula, but if the formula is valid, then why won't it work in VB?

I couldn't wait till Monday to play with this, so I had to go to work and get my data files and bring them back to play at home. I told you guys this project was going to be a doozy.

Thanks,



Bruce

This part would work by itself because it is a string:

="DTM~683~~~~RD8~"

The rest of it is a formula designed to choose the number of day in a month depending on the value of the rest of the formula. In this case, the value from that formula is a string value of 31, thereby making the line's correct value be: DTM~683~~~~RD8~31

In summary, this works by itself to produce the indicated portion of the correct string:
ActiveCell.Cells(5, 1).Formula = "DTM~683~~~~RD8~" 'Producs the string: DTM~683~~~~RD8~

or you can do this line by itself and it works to produce the indicated portion of the correct string.
The key is here that the ranges Summary and Rowsum don't have any declarations like Range("Summary") and they do not need them in that case below:

ActiveCell.Cells(5, 1).Formula = "=CHOOSE(MOD(VALUE(INDEX(Summary,ROWSUM+1,8)),100) ,31,28,31,30,31,30,31,31,30,31,30,31)" 'Produces the string: 31

But if I combine the two parts the way I had it which is the first selection below, or if I put it in with the Range added the way I think you're showing me in your reply, then neither one works. And, yes I am sure the combined syntax is definitely screwed up somehow since independently these two things work.

Bruce's formula:
ActiveCell.Cells(5, 1).Formula = "DTM~683~~~~RD8~"&CHOOSE(MOD(VALUE(INDEX(Summary,R OWSUM+1,8)),100),31,28,31,30,31,30,31,31,30,31,30, 31)

What I think Tom said to do:
ActiveCell.Cells(5, 1).Formula = "DTM~683~~~~RD8~"&CHOOSE(MOD(VALUE(INDEX(Range("Su mmary"),Range("ROWSUM")+1,8)),100),31,28,31,30,31, 30,31,31,30,31,30,31)"

Both of these two makes it highlight the "MOD" part of the formula when it points out the syntax error

The third idea I tried with no success was to put a quotation (") before choose, and again at the end of the line as well. that just made the whole choose formula a string, and that definitely isn't right. The syntax error doesn't occur is about the only positive thing about that option.

"Tom Ogilvy" wrote in message ...
What doesn't work compared to what works"

CHOOSE(MOD(VALUE(INDEX(Range("Summary"),Range("ROW SUM"+1,8)),100),31,28,31,30,31,30,31,31,30,31,30,3 1)
CHOOSE(MOD(VALUE(INDEX(Summary,ROWSUM+1,8)),100),3 1,28,31,30,31,30,31,31,30,31,30,31)

If you want the first to be a string that is a legal formula, then you need to take out the Range stuff and just use a string.

Basically your syntax is screwed up.

Regards,
Tom Ogilvy





Bruce Roberson wrote in message ...
Sub HeaderRecord()
ActiveCell.Cells(1, 1).Formula = "ISA~03~TX813080RP~00~ ~ZZ~17512548722~ZZ~TEX COMPTROLLER~" & Range ("YYMMDD") & "~" & Range("Time") & "~U~00401~000000093~0~P~^"
ActiveCell.Cells(2, 1).Formula = "GS~TF~17512548722 ~TEX COMPTROLLER~" & Range("CCYYMMDD") & "~" & Range ("Time") & "~2~X~004010"
ActiveCell.Cells(3, 1).Formula = "ST~813~0001"
ActiveCell.Cells(4, 1).Formula = "BTI~T6~082~47~TX~" & Range("CCYYMMDD") & "~~~~49~17512548722~SV~00000156C~" & Range("Btilineend")
ActiveCell.Cells(5, 1).Formula ="DTM~683~~~~RD8~"& CHOOSE(MOD(VALUE(INDEX(Range("Summary"),Range ("ROWSUM"+1,8)),100),31,28,31,30,31,30,31,31,30,31 ,30,31)


End Sub

I'm hung up right now on the line that has Cells(5,1) in it. Excel keeps giving me the message: Compile error: Expected: Expression, and it highlights the portion "MOD".

I've already tested the following formula, so the "MOD" part of the formula shouldn't be a problem. The standalone formula is:
="DTM~683~~~~RD8~"&CHOOSE(MOD(VALUE(INDEX(Summary, ROWSUM+1,8)),100),31,28,31,30,31,30,31,31,30,31,30 ,31)

and the result string of this formula comes out to be: DTM~683~~~~RD8~31

If I've got the syntax right on the first 4 activecell lines, then why is this one any different? Sure, it has a wild long formula, but if the formula is valid, then why won't it work in VB?

I couldn't wait till Monday to play with this, so I had to go to work and get my data files and bring them back to play at home. I told you guys this project was going to be a doozy.

Thanks,



Bruce

Bruce,

I think what Tom was saying is that you are getting your worksheet and VBA syntax mixed up. Even though you are in VBA, if you are defining a worksheet formula, you must still adhere to that syntax, and you must setup the formula as a string (what happened to putting in a string as I suggested, and then debugging that string to see is says what you expect?). In this case, Range is not permissible in a worksheet formula, you must pass the evaluation of that expression, something akin to

ActiveCell.Cells(5, 1).Formula = "DTM~683~~~~RD8~" & "CHOOSE(MOD(VALUE(INDEX(" & Range("Summary") & "," & Range("ROWSUM")+1 & ",8)),100),31,28,31,30,31,30,31,31,30,31,30,31 )"

I haven't tested it, but I hope you get the idea enough to take it forward.

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Bruce Roberson" wrote in message ...
This part would work by itself because it is a string:

="DTM~683~~~~RD8~"

The rest of it is a formula designed to choose the number of day in a month depending on the value of the rest of the formula. In this case, the value from that formula is a string value of 31, thereby making the line's correct value be: DTM~683~~~~RD8~31

In summary, this works by itself to produce the indicated portion of the correct string:
ActiveCell.Cells(5, 1).Formula = "DTM~683~~~~RD8~" 'Producs the string: DTM~683~~~~RD8~

or you can do this line by itself and it works to produce the indicated portion of the correct string.
The key is here that the ranges Summary and Rowsum don't have any declarations like Range("Summary") and they do not need them in that case below:

ActiveCell.Cells(5, 1).Formula = "=CHOOSE(MOD(VALUE(INDEX(Summary,ROWSUM+1,8)),100) ,31,28,31,30,31,30,31,31,30,31,30,31)" 'Produces the string: 31

But if I combine the two parts the way I had it which is the first selection below, or if I put it in with the Range added the way I think you're showing me in your reply, then neither one works. And, yes I am sure the combined syntax is definitely screwed up somehow since independently these two things work.

Bruce's formula:
ActiveCell.Cells(5, 1).Formula = "DTM~683~~~~RD8~"&CHOOSE(MOD(VALUE(INDEX(Summary,R OWSUM+1,8)),100),31,28,31,30,31,30,31,31,30,31,30, 31)

What I think Tom said to do:
ActiveCell.Cells(5, 1).Formula = "DTM~683~~~~RD8~"&CHOOSE(MOD(VALUE(INDEX(Range("Su mmary"),Range("ROWSUM")+1,8)),100),31,28,31,30,31, 30,31,31,30,31,30,31)"

Both of these two makes it highlight the "MOD" part of the formula when it points out the syntax error

The third idea I tried with no success was to put a quotation (") before choose, and again at the end of the line as well. that just made the whole choose formula a string, and that definitely isn't right. The syntax error doesn't occur is about the only positive thing about that option.

"Tom Ogilvy" wrote in message ...
What doesn't work compared to what works"

CHOOSE(MOD(VALUE(INDEX(Range("Summary"),Range("ROW SUM"+1,8)),100),31,28,31,30,31,30,31,31,30,31,30,3 1)
CHOOSE(MOD(VALUE(INDEX(Summary,ROWSUM+1,8)),100),3 1,28,31,30,31,30,31,31,30,31,30,31)

If you want the first to be a string that is a legal formula, then you need to take out the Range stuff and just use a string.

Basically your syntax is screwed up.

Regards,
Tom Ogilvy





Bruce Roberson wrote in message ...
Sub HeaderRecord()
ActiveCell.Cells(1, 1).Formula = "ISA~03~TX813080RP~00~ ~ZZ~17512548722~ZZ~TEX COMPTROLLER~" & Range ("YYMMDD") & "~" & Range("Time") & "~U~00401~000000093~0~P~^"
ActiveCell.Cells(2, 1).Formula = "GS~TF~17512548722 ~TEX COMPTROLLER~" & Range("CCYYMMDD") & "~" & Range ("Time") & "~2~X~004010"
ActiveCell.Cells(3, 1).Formula = "ST~813~0001"
ActiveCell.Cells(4, 1).Formula = "BTI~T6~082~47~TX~" & Range("CCYYMMDD") & "~~~~49~17512548722~SV~00000156C~" & Range("Btilineend")
ActiveCell.Cells(5, 1).Formula ="DTM~683~~~~RD8~"& CHOOSE(MOD(VALUE(INDEX(Range("Summary"),Range ("ROWSUM"+1,8)),100),31,28,31,30,31,30,31,31,30,31 ,30,31)


End Sub

I'm hung up right now on the line that has Cells(5,1) in it. Excel keeps giving me the message: Compile error: Expected: Expression, and it highlights the portion "MOD".

I've already tested the following formula, so the "MOD" part of the formula shouldn't be a problem. The standalone formula is:
="DTM~683~~~~RD8~"&CHOOSE(MOD(VALUE(INDEX(Summary, ROWSUM+1,8)),100),31,28,31,30,31,30,31,31,30,31,30 ,31)

and the result string of this formula comes out to be: DTM~683~~~~RD8~31

If I've got the syntax right on the first 4 activecell lines, then why is this one any different? Sure, it has a wild long formula, but if the formula is valid, then why won't it work in VB?

I couldn't wait till Monday to play with this, so I had to go to work and get my data files and bring them back to play at home. I told you guys this project was going to be a doozy.

Thanks,



Bruce

I copied what you showed, and I'm still piddling with it. But anything to do with " in front of choose so far just pastes the string from there on over rather than the value of the would be formula.

I'll keep plugging but I may have to give up till Monday.
"Bob Phillips" wrote in message ...
Bruce,

I think what Tom was saying is that you are getting your worksheet and VBA syntax mixed up. Even though you are in VBA, if you are defining a worksheet formula, you must still adhere to that syntax, and you must setup the formula as a string (what happened to putting in a string as I suggested, and then debugging that string to see is says what you expect?). In this case, Range is not permissible in a worksheet formula, you must pass the evaluation of that expression, something akin to

ActiveCell.Cells(5, 1).Formula = "DTM~683~~~~RD8~" & "CHOOSE(MOD(VALUE(INDEX(" & Range("Summary") & "," & Range("ROWSUM")+1 & ",8)),100),31,28,31,30,31,30,31,31,30,31,30,31 )"

I haven't tested it, but I hope you get the idea enough to take it forward.

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Bruce Roberson" wrote in message ...
This part would work by itself because it is a string:

="DTM~683~~~~RD8~"

The rest of it is a formula designed to choose the number of day in a month depending on the value of the rest of the formula. In this case, the value from that formula is a string value of 31, thereby making the line's correct value be: DTM~683~~~~RD8~31

In summary, this works by itself to produce the indicated portion of the correct string:
ActiveCell.Cells(5, 1).Formula = "DTM~683~~~~RD8~" 'Producs the string: DTM~683~~~~RD8~

or you can do this line by itself and it works to produce the indicated portion of the correct string.
The key is here that the ranges Summary and Rowsum don't have any declarations like Range("Summary") and they do not need them in that case below:

ActiveCell.Cells(5, 1).Formula = "=CHOOSE(MOD(VALUE(INDEX(Summary,ROWSUM+1,8)),100) ,31,28,31,30,31,30,31,31,30,31,30,31)" 'Produces the string: 31

But if I combine the two parts the way I had it which is the first selection below, or if I put it in with the Range added the way I think you're showing me in your reply, then neither one works. And, yes I am sure the combined syntax is definitely screwed up somehow since independently these two things work.

Bruce's formula:
ActiveCell.Cells(5, 1).Formula = "DTM~683~~~~RD8~"&CHOOSE(MOD(VALUE(INDEX(Summary,R OWSUM+1,8)),100),31,28,31,30,31,30,31,31,30,31,30, 31)

What I think Tom said to do:
ActiveCell.Cells(5, 1).Formula = "DTM~683~~~~RD8~"&CHOOSE(MOD(VALUE(INDEX(Range("Su mmary"),Range("ROWSUM")+1,8)),100),31,28,31,30,31, 30,31,31,30,31,30,31)"

Both of these two makes it highlight the "MOD" part of the formula when it points out the syntax error

The third idea I tried with no success was to put a quotation (") before choose, and again at the end of the line as well. that just made the whole choose formula a string, and that definitely isn't right. The syntax error doesn't occur is about the only positive thing about that option.

"Tom Ogilvy" wrote in message ...
What doesn't work compared to what works"

CHOOSE(MOD(VALUE(INDEX(Range("Summary"),Range("ROW SUM"+1,8)),100),31,28,31,30,31,30,31,31,30,31,30,3 1)
CHOOSE(MOD(VALUE(INDEX(Summary,ROWSUM+1,8)),100),3 1,28,31,30,31,30,31,31,30,31,30,31)

If you want the first to be a string that is a legal formula, then you need to take out the Range stuff and just use a string.

Basically your syntax is screwed up.

Regards,
Tom Ogilvy





Bruce Roberson wrote in message ...
Sub HeaderRecord()
ActiveCell.Cells(1, 1).Formula = "ISA~03~TX813080RP~00~ ~ZZ~17512548722~ZZ~TEX COMPTROLLER~" & Range ("YYMMDD") & "~" & Range("Time") & "~U~00401~000000093~0~P~^"
ActiveCell.Cells(2, 1).Formula = "GS~TF~17512548722 ~TEX COMPTROLLER~" & Range("CCYYMMDD") & "~" & Range ("Time") & "~2~X~004010"
ActiveCell.Cells(3, 1).Formula = "ST~813~0001"
ActiveCell.Cells(4, 1).Formula = "BTI~T6~082~47~TX~" & Range("CCYYMMDD") & "~~~~49~17512548722~SV~00000156C~" & Range("Btilineend")
ActiveCell.Cells(5, 1).Formula ="DTM~683~~~~RD8~"& CHOOSE(MOD(VALUE(INDEX(Range("Summary"),Range ("ROWSUM"+1,8)),100),31,28,31,30,31,30,31,31,30,31 ,30,31)


End Sub

I'm hung up right now on the line that has Cells(5,1) in it. Excel keeps giving me the message: Compile error: Expected: Expression, and it highlights the portion "MOD".

I've already tested the following formula, so the "MOD" part of the formula shouldn't be a problem. The standalone formula is:
="DTM~683~~~~RD8~"&CHOOSE(MOD(VALUE(INDEX(Summary, ROWSUM+1,8)),100),31,28,31,30,31,30,31,31,30,31,30 ,31)

and the result string of this formula comes out to be: DTM~683~~~~RD8~31

If I've got the syntax right on the first 4 activecell lines, then why is this one any different? Sure, it has a wild long formula, but if the formula is valid, then why won't it work in VB?

I couldn't wait till Monday to play with this, so I had to go to work and get my data files and bring them back to play at home. I told you guys this project was going to be a doozy.

Thanks,



Bruce

Bruce,

Sorry, I thought you were tryinmg to put the formula in the worksheet, not to get the value that that formula would return.

I would try this

aryDays = Array(0, 31, 28, 31,30, 31, 30, 31, 31, 30, 31, 30, 31)
ActiveCell.Value = "DTM~683~~~~~RD8" & _
aryDays(Range("Summary").Cells(Range("ROWSUM") + 1, 8) Mod 100)


--

HTH

Bob Phillips

"Bruce Roberson" wrote in message ...
I copied what you showed, and I'm still piddling with it. But anything to do with " in front of choose so far just pastes the string from there on over rather than the value of the would be formula.

I'll keep plugging but I may have to give up till Monday.
"Bob Phillips" wrote in message ...
Bruce,

I think what Tom was saying is that you are getting your worksheet and VBA syntax mixed up. Even though you are in VBA, if you are defining a worksheet formula, you must still adhere to that syntax, and you must setup the formula as a string (what happened to putting in a string as I suggested, and then debugging that string to see is says what you expect?). In this case, Range is not permissible in a worksheet formula, you must pass the evaluation of that expression, something akin to

ActiveCell.Cells(5, 1).Formula = "DTM~683~~~~RD8~" & "CHOOSE(MOD(VALUE(INDEX(" & Range("Summary") & "," & Range("ROWSUM")+1 & ",8)),100),31,28,31,30,31,30,31,31,30,31,30,31 )"

I haven't tested it, but I hope you get the idea enough to take it forward.

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Bruce Roberson" wrote in message ...
This part would work by itself because it is a string:

="DTM~683~~~~RD8~"

The rest of it is a formula designed to choose the number of day in a month depending on the value of the rest of the formula. In this case, the value from that formula is a string value of 31, thereby making the line's correct value be: DTM~683~~~~RD8~31

In summary, this works by itself to produce the indicated portion of the correct string:
ActiveCell.Cells(5, 1).Formula = "DTM~683~~~~RD8~" 'Producs the string: DTM~683~~~~RD8~

or you can do this line by itself and it works to produce the indicated portion of the correct string.
The key is here that the ranges Summary and Rowsum don't have any declarations like Range("Summary") and they do not need them in that case below:

ActiveCell.Cells(5, 1).Formula = "=CHOOSE(MOD(VALUE(INDEX(Summary,ROWSUM+1,8)),100) ,31,28,31,30,31,30,31,31,30,31,30,31)" 'Produces the string: 31

But if I combine the two parts the way I had it which is the first selection below, or if I put it in with the Range added the way I think you're showing me in your reply, then neither one works. And, yes I am sure the combined syntax is definitely screwed up somehow since independently these two things work.

Bruce's formula:
ActiveCell.Cells(5, 1).Formula = "DTM~683~~~~RD8~"&CHOOSE(MOD(VALUE(INDEX(Summary,R OWSUM+1,8)),100),31,28,31,30,31,30,31,31,30,31,30, 31)

What I think Tom said to do:
ActiveCell.Cells(5, 1).Formula = "DTM~683~~~~RD8~"&CHOOSE(MOD(VALUE(INDEX(Range("Su mmary"),Range("ROWSUM")+1,8)),100),31,28,31,30,31, 30,31,31,30,31,30,31)"

Both of these two makes it highlight the "MOD" part of the formula when it points out the syntax error

The third idea I tried with no success was to put a quotation (") before choose, and again at the end of the line as well. that just made the whole choose formula a string, and that definitely isn't right. The syntax error doesn't occur is about the only positive thing about that option.

"Tom Ogilvy" wrote in message ...
What doesn't work compared to what works"

CHOOSE(MOD(VALUE(INDEX(Range("Summary"),Range("ROW SUM"+1,8)),100),31,28,31,30,31,30,31,31,30,31,30,3 1)
CHOOSE(MOD(VALUE(INDEX(Summary,ROWSUM+1,8)),100),3 1,28,31,30,31,30,31,31,30,31,30,31)

If you want the first to be a string that is a legal formula, then you need to take out the Range stuff and just use a string.

Basically your syntax is screwed up.

Regards,
Tom Ogilvy





Bruce Roberson wrote in message ...
Sub HeaderRecord()
ActiveCell.Cells(1, 1).Formula = "ISA~03~TX813080RP~00~ ~ZZ~17512548722~ZZ~TEX COMPTROLLER~" & Range ("YYMMDD") & "~" & Range("Time") & "~U~00401~000000093~0~P~^"
ActiveCell.Cells(2, 1).Formula = "GS~TF~17512548722 ~TEX COMPTROLLER~" & Range("CCYYMMDD") & "~" & Range ("Time") & "~2~X~004010"
ActiveCell.Cells(3, 1).Formula = "ST~813~0001"
ActiveCell.Cells(4, 1).Formula = "BTI~T6~082~47~TX~" & Range("CCYYMMDD") & "~~~~49~17512548722~SV~00000156C~" & Range("Btilineend")
ActiveCell.Cells(5, 1).Formula ="DTM~683~~~~RD8~"& CHOOSE(MOD(VALUE(INDEX(Range("Summary"),Range ("ROWSUM"+1,8)),100),31,28,31,30,31,30,31,31,30,31 ,30,31)


End Sub

I'm hung up right now on the line that has Cells(5,1) in it. Excel keeps giving me the message: Compile error: Expected: Expression, and it highlights the portion "MOD".

I've already tested the following formula, so the "MOD" part of the formula shouldn't be a problem. The standalone formula is:
="DTM~683~~~~RD8~"&CHOOSE(MOD(VALUE(INDEX(Summary, ROWSUM+1,8)),100),31,28,31,30,31,30,31,31,30,31,30 ,31)

and the result string of this formula comes out to be: DTM~683~~~~RD8~31

If I've got the syntax right on the first 4 activecell lines, then why is this one any different? Sure, it has a wild long formula, but if the formula is valid, then why won't it work in VB?

I couldn't wait till Monday to play with this, so I had to go to work and get my data files and bring them back to play at home. I told you guys this project was going to be a doozy.

Thanks,



Bruce