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

Well, I gave up the idea of embedding the formulas in VB.
Most of these formulas are bad enough as it is in the
spreadsheet itself without trying to make them into
strings or actual formulas inside the VB sub. I tried the
string method as best I could construct it but that was
too hard also. I just ended up constructing the text
itself in spreadsheet cells and then I assemble it a
record at a time in a range called "Workarea" and then
finally I copy the completed text a well record at a time
over to the report sheet where it resides in columnar
fashion. The detailloop you can see below is the longest
SUB of the project

This loop for the detail works, but I am sure you will
find the coding structure to be horrendously inefficient.
I still have too many selects as I work between sheets and
ranges to start my copying.

I keep having to select my worksheet on one line, and then
my range on another line. If I don't, it keeps giving me
this run time error '1004' Select method of range class
failed. And, to that, I say a big "WHATEVER!!!", like I
know why it does that <LOL But that is why the sheet is
selected on one line (see line 7 of the loop), and then I
selected the range on a separate line. Otherwise it speaks
that Greek mumbo jumbo to me.

There are three basic worksheets I'm working between. The
sheet "import" is where I've imported the row wise data
from my database Alpha V. The sheet "Data_Assembly" is
where I assemble all those awful formulas you got a peak
at yesterday, and its where I have a gob of spreadsheet
ranges. And it also is where I temporarily copy the
results of the data assembly into a spreadsheet ranged
called "Workarea".

Finally, it copies structured data from the workarea into
the sheet called "report". This is where I'm laying out my
report to the state, and the layout on this sheet is
straight text in columnar arrangement. It will be copied
and pasted to another file and saved as a TXT file when
this thing is finished.

So, those are my three sheets in the workbook that are
referred to in this looping, Import, Data_Assembly, and
Report.

Here is the detailloop at this point:


Sub detailloop()
Dim Crows As Long
Range("rowsum").Value = 1
Sheets("Import").Select
Crows = Cells(1, 1).End(xlDown).Row
For i = 1 To Crows - 1
Sheets("Data_Assembly").Select
Range("Workarea").Select
ActiveCell.Value = Range("Detailloop").Offset(0, 0)
ActiveCell.Offset(1, 0).Value =
Range("Detailloop").Offset(1, 0)
ActiveCell.Offset(2, 0).Value =
Range("Detailloop").Offset(2, 0)
ActiveCell.Offset(3, 0).Value =

And so on like this through the 13th offset then I do a
check of a True false range in the if statement below:

If Range("Do_lse_use") Then
ActiveCell.Offset(14, 0).Value =
Range("lease_use").Offset(0, 0)
ActiveCell.Offset(15, 0).Value =
Range("lease_use").Offset(1, 0)

This part goes on till the offset in range "lease use" is
at 13.


Else
GoTo copyrecords
End If
copyrecords:
Range(ActiveCell, ActiveCell.End(xlDown)).Copy
Sheets("Report").Select
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveCell.PasteSpecial xlPasteValues
Worksheets("Data_Assembly").Select
Range("Workarea").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Clear
Range("Row").Value = Range("Row").Value + 1
If Range("New_Prmo") Then
Range("rowsum").Value = Range("Rowsum").Value + 1
Call summonth
Else
End If
Next
End Sub

Bruce,

I have only just joined the forum and have just seen your post.

You could change the code to the following. I have highlighted th
changes in red

Sub detailloop()
Dim Crows As Long
Range("rowsum").Value = 1
Sheets("Import").Select
Crows = Cells(1, 1).End(xlDown).Row
For i = 1 To Crows - 1

Application.Goto Reference:="Workarea"
ActiveCell.Value = Range("Detailloop").Offset(0, 0)
ActiveCell.Offset(1, 0).Value =
Range("Detailloop").Offset(1, 0)
ActiveCell.Offset(2, 0).Value =
Range("Detailloop").Offset(2, 0)
ActiveCell.Offset(3, 0).Value =

And so on like this through the 13th offset then I do a
check of a True false range in the if statement below:

If Range("Do_lse_use") Then
ActiveCell.Offset(14, 0).Value =
Range("lease_use").Offset(0, 0)
ActiveCell.Offset(15, 0).Value =
Range("lease_use").Offset(1, 0)

This part goes on till the offset in range "lease use" is
at 13.


Else
GoTo copyrecords
End If
copyrecords:
Range(ActiveCell, ActiveCell.End(xlDown)).Copy
Sheets("Report").Select
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveCell.PasteSpecial xlPasteValues

Application.Goto Reference:="Workarea"

Range(ActiveCell, ActiveCell.End(xlDown)).Clear
Range("Row").Value = Range("Row").Value + 1
If Range("New_Prmo") Then
Range("rowsum").Value = Range("Rowsum").Value + 1
Call summonth
Else
End If
Next
End Sub


This should save you the problem of having to select the sheet firs
then select the name.

Hope this is of some use to you

--
Message posted from http://www.ExcelForum.com