Problem programming a formula

[CB]

Hi all,

I'm having an issue with a formula not working in VB code that work just
fine when entered into a given cell.

A little background first…I’m importing into Excel data from a datalogger.
The year and Julian day are output from the logger as two separate fields. I
need to get the Julian day expressed as MMM-DD format. When I simply format
the cell with that format, the value is one day behind (and it shows a year
of 1900 when the cursor is actually in the cell). I did a little searching
and a found a formula on the ‘net that works when typed into a cell.
Unfortunately, the formula is overwritten when the data is imported. Hence,
my need to enter it programmatically after the data has been imported.

Before any coding I took the following steps to test my formulas in a blank
worksheet
i) Year imported to A11
ii) Julian day imported to B11
iii) Had the following formula in C11:
=IF(B11<100,CONCATENATE(A11,"0",B11),CONCATENATE(A11,B11))
iv) Had the following formula in D11: =RIGHT(C11,5)
v) Format cell E11 as MMM-DD and had the following formula in E11:
=("1/1/" &(IF(LEFT(D11,2)*1<20,2000,1900)+LEFT(D11,2)))+MOD(D11,1000)-1

This works perfectly. When I had a closer look at that last formula, I found
I could skip the last three steps and simply use the formula below to get
exactly the same result:

=("1/1/"&A11)+B11-1

For example, today is March 9, 2009. With 2009 in A11 and 68 (Julian day for
today) in B11, the resulting cell returns Mar-09 … exactly what I want.

The problem arises when I try to get the same results programmatically. I
have the following line of code after my code for importing the data.

Range("b11").Value = "=("1/1/" & a11) + b11 - 1"

I get an error when compiling. It doesn’t appear to like the quotation marks
inside the formula. The debugger stops on the first “1†in the formula and it
expects “End of Statement.â€

What am I missing?

FWIW, I'll then be expanding this to apply to a range of cells in a column.

Thanks for any and all assistance!

Chris

 

[JLGWhiz]

Maybe this:

Range("b11").Value = "=("1/1/"" & a11) + b11 - 1"

 

[CB]

Hi again,

Well, I decided to try to record a macro to copy the formula from one place
to another to see what the formula would look like in code and I got the
following:

ActiveCell.FormulaR1C1 = "=(""1/1/""&R11C[-4])+R11C[-3]-1"

I see I missed some quotation marks.

I changed this to (and also removed all spaces)

Range("b11").Value = "=(""1/1/""&R11C1)+R11C2-1"

The code now compiles properly but the cell value ends up as zero (instead
of 39881 for today's date).

What's up with that?

Thanks!

 

[JLGWhiz]

Yep, I see that I missed one of the double quotes. I need to read my own
work before I post it.

Hi again,

Well, I decided to try to record a macro to copy the formula from one place
to another to see what the formula would look like in code and I got the
following:

ActiveCell.FormulaR1C1 = "=(""1/1/""&R11C[-4])+R11C[-3]-1"

I see I missed some quotation marks.

I changed this to (and also removed all spaces)

Range("b11").Value = "=(""1/1/""&R11C1)+R11C2-1"

The code now compiles properly but the cell value ends up as zero (instead
of 39881 for today's date).

What's up with that?

Thanks!

Maybe this:

Range("b11").Value = "=("1/1/"" & a11) + b11 - 1"

 

[CB]

Hi,

No problem. At least it'll compile now. Any thoughts on why it's evaluating
to zero? FWIW, both fields A11 and B11 are formatted as General.

Chris

Yep, I see that I missed one of the double quotes. I need to read my own
work before I post it.

Hi again,

Well, I decided to try to record a macro to copy the formula from one place
to another to see what the formula would look like in code and I got the
following:

ActiveCell.FormulaR1C1 = "=(""1/1/""&R11C[-4])+R11C[-3]-1"

I see I missed some quotation marks.

I changed this to (and also removed all spaces)

Range("b11").Value = "=(""1/1/""&R11C1)+R11C2-1"

The code now compiles properly but the cell value ends up as zero (instead
of 39881 for today's date).

What's up with that?

Thanks!

Maybe this:

Range("b11").Value = "=("1/1/"" & a11) + b11 - 1"


:

Hi all,

I'm having an issue with a formula not working in VB code that work just
fine when entered into a given cell.

A little background first…I’m importing into Excel data from a datalogger.
The year and Julian day are output from the logger as two separate fields. I
need to get the Julian day expressed as MMM-DD format. When I simply format
the cell with that format, the value is one day behind (and it shows a year
of 1900 when the cursor is actually in the cell). I did a little searching
and a found a formula on the ‘net that works when typed into a cell.
Unfortunately, the formula is overwritten when the data is imported. Hence,
my need to enter it programmatically after the data has been imported.

Before any coding I took the following steps to test my formulas in a blank
worksheet
i) Year imported to A11
ii) Julian day imported to B11
iii) Had the following formula in C11:
=IF(B11<100,CONCATENATE(A11,"0",B11),CONCATENATE(A11,B11))
iv) Had the following formula in D11: =RIGHT(C11,5)
v) Format cell E11 as MMM-DD and had the following formula in E11:
=("1/1/" &(IF(LEFT(D11,2)*1<20,2000,1900)+LEFT(D11,2)))+MOD(D11,1000)-1

This works perfectly. When I had a closer look at that last formula, I found
I could skip the last three steps and simply use the formula below to get
exactly the same result:

=("1/1/"&A11)+B11-1

For example, today is March 9, 2009. With 2009 in A11 and 68 (Julian day for
today) in B11, the resulting cell returns Mar-09 … exactly what I want.

The problem arises when I try to get the same results programmatically. I
have the following line of code after my code for importing the data.

Range("b11").Value = "=("1/1/" & a11) + b11 - 1"

I get an error when compiling. It doesn’t appear to like the quotation marks
inside the formula. The debugger stops on the first “1†in the formula and it
expects “End of Statement.â€

What am I missing?

FWIW, I'll then be expanding this to apply to a range of cells in a column.

Thanks for any and all assistance!

Chris