C
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
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