CODEWITHSUNDEEP
vbaexceldate
Clauric
Clauric

Reputation: 1896

How to convert given formula to VBA code

I got a formula from Ron Ronsfield's answer to "In excel, date shows as mdyyyy, want as mm-dd-yyyy"

The formula is:

=--TEXT(A1,"00\/00\/0000")

I was able to bodge this formula into the following code for my macro:

a = 1
    While Range("A" & a) <> ""
        Range("M" & a) = "=--TEXT(" & Range("H" & a) & ",""00\/00\/0000"")"

        a = a + 1
    Wend

I have tried a couple of ways to get this into a code that just prints an answer to my excel, and doesn't insert a formula, but to know avail.

I was wondering if anybody had a suggestion on how to get this into a code?

Upvotes: 1

Views: 82

Answers (1)

Axel Richter
Axel Richter

Reputation: 61995

The equivalent which places the date values directly in column M instead of the formulas would be:

a = 1
While Range("A" & a) <> ""
    'Range("M" & a) = "=--TEXT(" & Range("H" & a) & ",""00\/00\/0000"")"
    Range("M" & a) = CDate(Format(Range("H" & a), "00\/00\/0000"))

    a = a + 1
Wend

Upvotes: 2

Related Questions