CODEWITHSUNDEEP
vbaexcel
Ollie
Ollie

Reputation: 209

Adding a formula into Excel cell from vba

I have a worksheet labeled my_new_name and want to put in a formula, having looked at other stack overflow threads i have come up with the following:

Set wsDest = Sheets("my_new_name")
wsDest.Range("J2").Formula = "=RIGHT(K2,2)&" / "&MID(K2,5,2)&" / "&LEFT(K2,4)"
wsDest.Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J")

However this doesn't work and the error comes up with a 'type mismatch'

Any help would be great

Upvotes: 0

Views: 70

Answers (1)

Rik Sportel
Rik Sportel

Reputation: 2679

Provided that the Formula output you're after reads as =RIGHT(K2,2)&"/"&MID(K2,5,2)&"/"&LEFT(K2,4), you'll have to escape the double quotes in your VBA line.

wsDest.Range("J2").Formula = "=RIGHT(K2,2)&" / "&MID(K2,5,2)&" / "&LEFT(K2,4)" (throws type mismatch)

Should be:

wsDest.Range("J2").Formula = "=RIGHT(K2,2)&"" / ""&MID(K2,5,2)&"" / ""&LEFT(K2,4)" (doesn't error)

Edit: Also, as per comment section you'll need to specify the end row afterwards (J2:J####) - where ### is a row number.

Upvotes: 2

Related Questions