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 firstIm 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(A 11,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 doesnt 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