|
Problem programming a formula
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 |
Problem programming a formula
Maybe this:
Range("b11").Value = "=("1/1/"" & a11) + b11 - 1" "CB" wrote: 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 |
Problem programming a formula
Hi there,
Thanks for responding. I tried your suggestion but I still get the same error. Chris "JLGWhiz" wrote: Maybe this: Range("b11").Value = "=("1/1/"" & a11) + b11 - 1" "CB" wrote: 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 |
Problem programming a formula
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" wrote: Maybe this: Range("b11").Value = "=("1/1/"" & a11) + b11 - 1" "CB" wrote: 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 |
Problem programming a formula
Yep, I see that I missed one of the double quotes. I need to read my own
work before I post it. "CB" wrote: 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" wrote: Maybe this: Range("b11").Value = "=("1/1/"" & a11) + b11 - 1" "CB" wrote: 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 |
Problem programming a formula
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 "JLGWhiz" wrote: Yep, I see that I missed one of the double quotes. I need to read my own work before I post it. "CB" wrote: 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" wrote: Maybe this: Range("b11").Value = "=("1/1/"" & a11) + b11 - 1" "CB" wrote: 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 |
| All times are GMT +1. The time now is 05:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com