First attempt at VBA coding problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)&"""""
 
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
 
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
 
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 said:
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 said:
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)&"""""
Click to expand...
Click to expand...
 
Hi Rick,

wrong quotes around contents

--
Kind regards,

Niek Otten

Rick in NS said:
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 said:
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 said:
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)&"""""
Click to expand...
Click to expand...
Click to expand...
 
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 said:
Hi Rick,

wrong quotes around contents

--
Kind regards,

Niek Otten

Rick in NS said:
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 said:
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

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)&"""""
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
Hi Rick,

wrong quotes around contents

--
Kind regards,

Niek Otten

Rick in NS said:
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


:

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

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)&"""""
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
Hi Rick,

wrong quotes around contents

--
Kind regards,

Niek Otten

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


:

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

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)&"""""
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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


:

Hi Rick,

wrong quotes around contents

--
Kind regards,

Niek Otten

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


:

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

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)&"""""
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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

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


:

Hi Rick,

wrong quotes around contents

--
Kind regards,

Niek Otten

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


:

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

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)&"""""
Click to expand...
Click to expand...
Click to expand...
 
Back
Top