|
First attempt at VBA coding problem
I have a formula in a cell on a worksheet as follows:
=TEXT(DAY(B5),"00")&"-"&TEXT(MONTH(B5),"00")&"-"&RIGHT(TEXT(YEAR(B5),"00"),2)&"," & CELL("contents",D5)&"" I am attempting to automate entering the formula in a cell with the following macro code. Obviously I have something wrong with the syntax. Can anyone identify the error in this code: ActiveCell.FormulaR1C1 = _ "=TEXT(DAY(RC[1]),""00"")&""-""&TEXT(MONTH(RC[1]),""00"")&""-""&RIGHT(TEXT(YEAR(RC[1]),""00""),2)&"","" & Cell(€ścontents€ť,D5)&""""" -- Rick in N S |
First attempt at VBA coding problem
The final D5 should read RC[3].
This is one of those that's probably easiest to translate by just turning on the macro recorder then dropping the formula you want in the cell where you want it. Then jump into the VB Editor and copy the line of code. --Bruce "Rick in NS" wrote: I have a formula in a cell on a worksheet as follows: =TEXT(DAY(B5),"00")&"-"&TEXT(MONTH(B5),"00")&"-"&RIGHT(TEXT(YEAR(B5),"00"),2)&"," & CELL("contents",D5)&"" I am attempting to automate entering the formula in a cell with the following macro code. Obviously I have something wrong with the syntax. Can anyone identify the error in this code: ActiveCell.FormulaR1C1 = _ "=TEXT(DAY(RC[1]),""00"")&""-""&TEXT(MONTH(RC[1]),""00"")&""-""&RIGHT(TEXT(YEAR(RC[1]),""00""),2)&"","" & Cell(€ścontents€ť,D5)&""""" -- Rick in N S |
First attempt at VBA coding problem
Hi Rick,
Two things: the quotes around "contents" are of the wrong type and you did not translate the D5 address to a R1C1 address -- Kind regards, Niek Otten "Rick in NS" .(donotspam) wrote in message ... I have a formula in a cell on a worksheet as follows: =TEXT(DAY(B5),"00")&"-"&TEXT(MONTH(B5),"00")&"-"&RIGHT(TEXT(YEAR(B5),"00"),2)&"," & CELL("contents",D5)&"" I am attempting to automate entering the formula in a cell with the following macro code. Obviously I have something wrong with the syntax. Can anyone identify the error in this code: ActiveCell.FormulaR1C1 = _ "=TEXT(DAY(RC[1]),""00"")&""-""&TEXT(MONTH(RC[1]),""00"")&""-""&RIGHT(TEXT(YEAR(RC[1]),""00""),2)&"","" & Cell("contents",D5)&""""" -- Rick in N S |
First attempt at VBA coding problem
The modifications to the code based on both recommendations results in a
run-time error of 1004. Debug drop me into the line of code. ActiveCell.Formula = "=TEXT(B5,""mm-yy-dd"")&"","" & Cell('contents',RC[3]) & """ -- Rick in N S "Niek Otten" wrote: Hi Rick, Two things: the quotes around "contents" are of the wrong type and you did not translate the D5 address to a R1C1 address -- Kind regards, Niek Otten "Rick in NS" .(donotspam) wrote in message ... I have a formula in a cell on a worksheet as follows: =TEXT(DAY(B5),"00")&"-"&TEXT(MONTH(B5),"00")&"-"&RIGHT(TEXT(YEAR(B5),"00"),2)&"," & CELL("contents",D5)&"" I am attempting to automate entering the formula in a cell with the following macro code. Obviously I have something wrong with the syntax. Can anyone identify the error in this code: ActiveCell.FormulaR1C1 = _ "=TEXT(DAY(RC[1]),""00"")&""-""&TEXT(MONTH(RC[1]),""00"")&""-""&RIGHT(TEXT(YEAR(RC[1]),""00""),2)&"","" & Cell("contents",D5)&""""" -- Rick in N S |
First attempt at VBA coding problem
Hi Rick,
wrong quotes around contents -- Kind regards, Niek Otten "Rick in NS" .(donotspam) wrote in message ... The modifications to the code based on both recommendations results in a run-time error of 1004. Debug drop me into the line of code. ActiveCell.Formula = "=TEXT(B5,""mm-yy-dd"")&"","" & Cell('contents',RC[3]) & """ -- Rick in N S "Niek Otten" wrote: Hi Rick, Two things: the quotes around "contents" are of the wrong type and you did not translate the D5 address to a R1C1 address -- Kind regards, Niek Otten "Rick in NS" .(donotspam) wrote in message ... I have a formula in a cell on a worksheet as follows: =TEXT(DAY(B5),"00")&"-"&TEXT(MONTH(B5),"00")&"-"&RIGHT(TEXT(YEAR(B5),"00"),2)&"," & CELL("contents",D5)&"" I am attempting to automate entering the formula in a cell with the following macro code. Obviously I have something wrong with the syntax. Can anyone identify the error in this code: ActiveCell.FormulaR1C1 = _ "=TEXT(DAY(RC[1]),""00"")&""-""&TEXT(MONTH(RC[1]),""00"")&""-""&RIGHT(TEXT(YEAR(RC[1]),""00""),2)&"","" & Cell("contents",D5)&""""" -- Rick in N S |
First attempt at VBA coding problem
Sorry Niek but your response doesn't make it clear to a novice just exactly
what the syntax error is. If I attempt using Cell("contents",RC[3]) it doesn't work. If I attempt using Cell('contents',RC[3]) it doesn't work. The only way I can get the code to save it by using Cell(""contents"",RC[3]) but when it runs it continues to return an error code of 1004. Please provide me with the exact line of code. -- Rick in N S "Niek Otten" wrote: Hi Rick, wrong quotes around contents -- Kind regards, Niek Otten "Rick in NS" .(donotspam) wrote in message ... The modifications to the code based on both recommendations results in a run-time error of 1004. Debug drop me into the line of code. ActiveCell.Formula = "=TEXT(B5,""mm-yy-dd"")&"","" & Cell('contents',RC[3]) & """ -- Rick in N S "Niek Otten" wrote: Hi Rick, Two things: the quotes around "contents" are of the wrong type and you did not translate the D5 address to a R1C1 address -- Kind regards, Niek Otten "Rick in NS" .(donotspam) wrote in message ... I have a formula in a cell on a worksheet as follows: =TEXT(DAY(B5),"00")&"-"&TEXT(MONTH(B5),"00")&"-"&RIGHT(TEXT(YEAR(B5),"00"),2)&"," & CELL("contents",D5)&"" I am attempting to automate entering the formula in a cell with the following macro code. Obviously I have something wrong with the syntax. Can anyone identify the error in this code: ActiveCell.FormulaR1C1 = _ "=TEXT(DAY(RC[1]),""00"")&""-""&TEXT(MONTH(RC[1]),""00"")&""-""&RIGHT(TEXT(YEAR(RC[1]),""00""),2)&"","" & Cell("contents",D5)&""""" -- Rick in N S |
First attempt at VBA coding problem
AHA!
Now you left the RC[3] in R1C1 mode where it should be D5! -- Kind regards, Niek Otten PS Time to sleep here! See you tomorrow "Rick in NS" .(donotspam) wrote in message ... Sorry Niek but your response doesn't make it clear to a novice just exactly what the syntax error is. If I attempt using Cell("contents",RC[3]) it doesn't work. If I attempt using Cell('contents',RC[3]) it doesn't work. The only way I can get the code to save it by using Cell(""contents"",RC[3]) but when it runs it continues to return an error code of 1004. Please provide me with the exact line of code. -- Rick in N S "Niek Otten" wrote: Hi Rick, wrong quotes around contents -- Kind regards, Niek Otten "Rick in NS" .(donotspam) wrote in message ... The modifications to the code based on both recommendations results in a run-time error of 1004. Debug drop me into the line of code. ActiveCell.Formula = "=TEXT(B5,""mm-yy-dd"")&"","" & Cell('contents',RC[3]) & """ -- Rick in N S "Niek Otten" wrote: Hi Rick, Two things: the quotes around "contents" are of the wrong type and you did not translate the D5 address to a R1C1 address -- Kind regards, Niek Otten "Rick in NS" .(donotspam) wrote in message ... I have a formula in a cell on a worksheet as follows: =TEXT(DAY(B5),"00")&"-"&TEXT(MONTH(B5),"00")&"-"&RIGHT(TEXT(YEAR(B5),"00"),2)&"," & CELL("contents",D5)&"" I am attempting to automate entering the formula in a cell with the following macro code. Obviously I have something wrong with the syntax. Can anyone identify the error in this code: ActiveCell.FormulaR1C1 = _ "=TEXT(DAY(RC[1]),""00"")&""-""&TEXT(MONTH(RC[1]),""00"")&""-""&RIGHT(TEXT(YEAR(RC[1]),""00""),2)&"","" & Cell("contents",D5)&""""" -- Rick in N S |
First attempt at VBA coding problem
Niek:
No cigar. ActiveCell.Formula = "=TEXT(D5),""mm-yy-dd"")&"",""&Cell(""contents"",D5) & """ returns Run-time error '1004': Application-defined or object-defined error -- Rick in N S "Niek Otten" wrote: AHA! Now you left the RC[3] in R1C1 mode where it should be D5! -- Kind regards, Niek Otten PS Time to sleep here! See you tomorrow "Rick in NS" .(donotspam) wrote in message ... Sorry Niek but your response doesn't make it clear to a novice just exactly what the syntax error is. If I attempt using Cell("contents",RC[3]) it doesn't work. If I attempt using Cell('contents',RC[3]) it doesn't work. The only way I can get the code to save it by using Cell(""contents"",RC[3]) but when it runs it continues to return an error code of 1004. Please provide me with the exact line of code. -- Rick in N S "Niek Otten" wrote: Hi Rick, wrong quotes around contents -- Kind regards, Niek Otten "Rick in NS" .(donotspam) wrote in message ... The modifications to the code based on both recommendations results in a run-time error of 1004. Debug drop me into the line of code. ActiveCell.Formula = "=TEXT(B5,""mm-yy-dd"")&"","" & Cell('contents',RC[3]) & """ -- Rick in N S "Niek Otten" wrote: Hi Rick, Two things: the quotes around "contents" are of the wrong type and you did not translate the D5 address to a R1C1 address -- Kind regards, Niek Otten "Rick in NS" .(donotspam) wrote in message ... I have a formula in a cell on a worksheet as follows: =TEXT(DAY(B5),"00")&"-"&TEXT(MONTH(B5),"00")&"-"&RIGHT(TEXT(YEAR(B5),"00"),2)&"," & CELL("contents",D5)&"" I am attempting to automate entering the formula in a cell with the following macro code. Obviously I have something wrong with the syntax. Can anyone identify the error in this code: ActiveCell.FormulaR1C1 = _ "=TEXT(DAY(RC[1]),""00"")&""-""&TEXT(MONTH(RC[1]),""00"")&""-""&RIGHT(TEXT(YEAR(RC[1]),""00""),2)&"","" & Cell("contents",D5)&""""" -- Rick in N S |
First attempt at VBA coding problem
Hi Rick,
ActiveCell.Formula = "=TEXT(D5,""mm-yy-dd"")&"",""&Cell(""contents"",D5) & """"" That is, remove the ) after the first D5, and add two quotes at the end -- Kind regards, Niek Otten "Rick in NS" .(donotspam) wrote in message ... Niek: No cigar. ActiveCell.Formula = "=TEXT(D5),""mm-yy-dd"")&"",""&Cell(""contents"",D5) & """ returns Run-time error '1004': Application-defined or object-defined error -- Rick in N S "Niek Otten" wrote: AHA! Now you left the RC[3] in R1C1 mode where it should be D5! -- Kind regards, Niek Otten PS Time to sleep here! See you tomorrow "Rick in NS" .(donotspam) wrote in message ... Sorry Niek but your response doesn't make it clear to a novice just exactly what the syntax error is. If I attempt using Cell("contents",RC[3]) it doesn't work. If I attempt using Cell('contents',RC[3]) it doesn't work. The only way I can get the code to save it by using Cell(""contents"",RC[3]) but when it runs it continues to return an error code of 1004. Please provide me with the exact line of code. -- Rick in N S "Niek Otten" wrote: Hi Rick, wrong quotes around contents -- Kind regards, Niek Otten "Rick in NS" .(donotspam) wrote in message ... The modifications to the code based on both recommendations results in a run-time error of 1004. Debug drop me into the line of code. ActiveCell.Formula = "=TEXT(B5,""mm-yy-dd"")&"","" & Cell('contents',RC[3]) & """ -- Rick in N S "Niek Otten" wrote: Hi Rick, Two things: the quotes around "contents" are of the wrong type and you did not translate the D5 address to a R1C1 address -- Kind regards, Niek Otten "Rick in NS" .(donotspam) wrote in message ... I have a formula in a cell on a worksheet as follows: =TEXT(DAY(B5),"00")&"-"&TEXT(MONTH(B5),"00")&"-"&RIGHT(TEXT(YEAR(B5),"00"),2)&"," & CELL("contents",D5)&"" I am attempting to automate entering the formula in a cell with the following macro code. Obviously I have something wrong with the syntax. Can anyone identify the error in this code: ActiveCell.FormulaR1C1 = _ "=TEXT(DAY(RC[1]),""00"")&""-""&TEXT(MONTH(RC[1]),""00"")&""-""&RIGHT(TEXT(YEAR(RC[1]),""00""),2)&"","" & Cell("contents",D5)&""""" -- Rick in N S |
First attempt at VBA coding problem
Niek:
Finally! What a painfull experience. Hope the rest of the learning curve is not so difficult with Excel. I needed to add quotes around one of the extracted items and attempted to do so using five quote marks in the middle of the string. This did not work and finally stumbled across the use of char(34) somewhere else in the news group. Thanks for your help. Regards, -- Rick in NS "Niek Otten" wrote: Hi Rick, ActiveCell.Formula = "=TEXT(D5,""mm-yy-dd"")&"",""&Cell(""contents"",D5) & """"" That is, remove the ) after the first D5, and add two quotes at the end -- Kind regards, Niek Otten "Rick in NS" .(donotspam) wrote in message ... Niek: No cigar. ActiveCell.Formula = "=TEXT(D5),""mm-yy-dd"")&"",""&Cell(""contents"",D5) & """ returns Run-time error '1004': Application-defined or object-defined error -- Rick in N S "Niek Otten" wrote: AHA! Now you left the RC[3] in R1C1 mode where it should be D5! -- Kind regards, Niek Otten PS Time to sleep here! See you tomorrow "Rick in NS" .(donotspam) wrote in message ... Sorry Niek but your response doesn't make it clear to a novice just exactly what the syntax error is. If I attempt using Cell("contents",RC[3]) it doesn't work. If I attempt using Cell('contents',RC[3]) it doesn't work. The only way I can get the code to save it by using Cell(""contents"",RC[3]) but when it runs it continues to return an error code of 1004. Please provide me with the exact line of code. -- Rick in N S "Niek Otten" wrote: Hi Rick, wrong quotes around contents -- Kind regards, Niek Otten "Rick in NS" .(donotspam) wrote in message ... The modifications to the code based on both recommendations results in a run-time error of 1004. Debug drop me into the line of code. ActiveCell.Formula = "=TEXT(B5,""mm-yy-dd"")&"","" & Cell('contents',RC[3]) & """ -- Rick in N S "Niek Otten" wrote: Hi Rick, Two things: the quotes around "contents" are of the wrong type and you did not translate the D5 address to a R1C1 address -- Kind regards, Niek Otten "Rick in NS" .(donotspam) wrote in message ... I have a formula in a cell on a worksheet as follows: =TEXT(DAY(B5),"00")&"-"&TEXT(MONTH(B5),"00")&"-"&RIGHT(TEXT(YEAR(B5),"00"),2)&"," & CELL("contents",D5)&"" I am attempting to automate entering the formula in a cell with the following macro code. Obviously I have something wrong with the syntax. Can anyone identify the error in this code: ActiveCell.FormulaR1C1 = _ "=TEXT(DAY(RC[1]),""00"")&""-""&TEXT(MONTH(RC[1]),""00"")&""-""&RIGHT(TEXT(YEAR(RC[1]),""00""),2)&"","" & Cell("contents",D5)&""""" -- Rick in N S |
| All times are GMT +1. The time now is 05:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com