WS formula for Julian date not converting well to VBA??

[Ed]

I found the formula on Chip Pearson's site for converting a date to a Julian
date. When I try to use it as a VBA-inserted formula, it's not working out.
I keep getting an error message telling me "Expected: a close parenthesis
after the second YEAR". But doing that doesn't work. Can someone point me
to my error?

ws.Range("E1").Formula = "AR"& RIGHT(YEAR(Range("B1")),2)&
TEXT(Range("B1")-DATE(YEAR(Range("B1")),1,0),"000")

TIA
Ed

 

[Tom Ogilvy]

post the formula you want in the cell.

 

[PaulD]

: I found the formula on Chip Pearson's site for converting a date to a
Julian
: date. When I try to use it as a VBA-inserted formula, it's not working
out.
: I keep getting an error message telling me "Expected: a close parenthesis
: after the second YEAR". But doing that doesn't work. Can someone point
me
: to my error?
:
: ws.Range("E1").Formula = "AR"& RIGHT(YEAR(Range("B1")),2)&
: TEXT(Range("B1")-DATE(YEAR(Range("B1")),1,0),"000")
:
: TIA
: Ed
:

Ed,
Chips formula is meant to be pasted directly into a cell. To mimic this
from VBA you need to slightly modify

ws.Range("E1").Formula = "= ""AR"" & RIGHT(YEAR(B1),2) &
TEXT(B1-DATE(YEAR(B1),1,0),""000"")"

Note the double quotes and don't use range as this is VBA code and not
needed for a formula in a cell

Paul D

 

[PaulD]

"PaulD" <nospam> wrote in message
: : : I found the formula on Chip Pearson's site for converting a date to a
: Julian
: : date. When I try to use it as a VBA-inserted formula, it's not working
: out.
: <snip>

: Ed,
: Chips formula is meant to be pasted directly into a cell. To mimic this
: from VBA you need to slightly modify
:
: ws.Range("E1").Formula = "= ""AR"" & RIGHT(YEAR(B1),2) &
: TEXT(B1-DATE(YEAR(B1),1,0),""000"")"

<snip>

If you don't need the formula, just the result, you could also use

Public Function JulianDate(TheDate As Date)
JulianDate = Right(Year(TheDate), 2) & Format(DatePart("y", TheDate),
"000")
End Function
Then in cell E1, enter =JulianDate(B1)

Paul D

 

[Ed]

Worked perfectly, Paul! Thank you. Wonder if the extra quote had something
to do with pasting it directly into the VBE and then taking out the line
break. This time, I pasted into Word, took out the line break, then pasted
the single line into the module - no quotes.

Ed

 

[Tom Ogilvy]

when you put a string in the vbe and it does not have a closing quote, it is
added for you. Due to word wrap, this is the case you encountered I
believe.

 

[Ed]

So it'll add in a quote I don't need - but the IntelliSense doesn't
recognize ActiveSheet? Some programmer was paid to much to do the little
automatic things we all try to turn off, while ignoring a few more important
things, I think!

Ed