First attempt at VBA coding problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rick in NS - 11 Jan 2006 21:50 GMT

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)&"""""

Signature

Rick in N S

Reply to this Message

bpeltzer - 11 Jan 2006 22:01 GMT

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

> I have a formula in a cell on a worksheet as follows:
>
[quoted text clipped - 7 lines]
>
> "=TEXT(DAY(RC[1]),""00"")&""-""&TEXT(MONTH(RC[1]),""00"")&""-""&RIGHT(TEXT(YEAR(RC[1]),""00""),2)&"","" & Cell(“contents”,D5)&"""""

Reply to this Message

Niek Otten - 11 Jan 2006 22:09 GMT

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

Signature

Kind regards,

Niek Otten

>I have a formula in a cell on a worksheet as follows:
>
[quoted text clipped - 10 lines]
> "=TEXT(DAY(RC[1]),""00"")&""-""&TEXT(MONTH(RC[1]),""00"")&""-""&RIGHT(TEXT(YEAR(RC[1]),""00""),2)&"",""
> & Cell("contents",D5)&"""""

Reply to this Message

Rick in NS - 11 Jan 2006 22:34 GMT

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])
& """

Signature

Rick in N S

> Hi Rick,
>
[quoted text clipped - 15 lines]
> > "=TEXT(DAY(RC[1]),""00"")&""-""&TEXT(MONTH(RC[1]),""00"")&""-""&RIGHT(TEXT(YEAR(RC[1]),""00""),2)&"",""
> > & Cell("contents",D5)&"""""

Reply to this Message

Niek Otten - 11 Jan 2006 23:06 GMT

Hi Rick,

wrong quotes around contents

Signature

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.
[quoted text clipped - 23 lines]
>> > "=TEXT(DAY(RC[1]),""00"")&""-""&TEXT(MONTH(RC[1]),""00"")&""-""&RIGHT(TEXT(YEAR(RC[1]),""00""),2)&"",""
>> > & Cell("contents",D5)&"""""

Reply to this Message

Rick in NS - 11 Jan 2006 23:21 GMT

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.

Signature

Rick in N S

> Hi Rick,
>
[quoted text clipped - 27 lines]
> >> > "=TEXT(DAY(RC[1]),""00"")&""-""&TEXT(MONTH(RC[1]),""00"")&""-""&RIGHT(TEXT(YEAR(RC[1]),""00""),2)&"",""
> >> > & Cell("contents",D5)&"""""

Reply to this Message

Niek Otten - 11 Jan 2006 23:50 GMT

AHA!

Now you left the RC[3] in R1C1 mode where it should be D5!

Signature

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
[quoted text clipped - 39 lines]
>> >> > "=TEXT(DAY(RC[1]),""00"")&""-""&TEXT(MONTH(RC[1]),""00"")&""-""&RIGHT(TEXT(YEAR(RC[1]),""00""),2)&"",""
>> >> > & Cell("contents",D5)&"""""

Reply to this Message

Rick in NS - 12 Jan 2006 13:42 GMT

Niek:

No cigar.

ActiveCell.Formula = "=TEXT(D5),""mm-yy-dd"")&"",""&Cell(""contents"",D5) &
"""

returns Run-time error '1004': Application-defined or object-defined error

Signature

Rick in N S

> AHA!
>
[quoted text clipped - 43 lines]
> >> >> > "=TEXT(DAY(RC[1]),""00"")&""-""&TEXT(MONTH(RC[1]),""00"")&""-""&RIGHT(TEXT(YEAR(RC[1]),""00""),2)&"",""
> >> >> > & Cell("contents",D5)&"""""

Reply to this Message

Niek Otten - 12 Jan 2006 15:19 GMT

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

Signature

Kind regards,

Niek Otten

> Niek:
>
[quoted text clipped - 58 lines]
>> >> >> > "=TEXT(DAY(RC[1]),""00"")&""-""&TEXT(MONTH(RC[1]),""00"")&""-""&RIGHT(TEXT(YEAR(RC[1]),""00""),2)&"",""
>> >> >> > & Cell("contents",D5)&"""""

Reply to this Message

Rick in NS - 12 Jan 2006 17:11 GMT

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,

Signature

Rick in NS

> Hi Rick,
>
[quoted text clipped - 65 lines]
> >> >> >> > "=TEXT(DAY(RC[1]),""00"")&""-""&TEXT(MONTH(RC[1]),""00"")&""-""&RIGHT(TEXT(YEAR(RC[1]),""00""),2)&"",""
> >> >> >> > & Cell("contents",D5)&"""""

Reply to this Message