Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Enter Formula With VBA

1. Established Member
Join Date
13th July 2004
Posts
373

## Enter Formula With VBA

I previously requested help and several folks helped me out. I've found that if I can convert to a VBA It would be easier. I can record the Macro but was wondering if anyone had any better ideas. Heres the formula I'm trying to work into a macro using VBA. Thanks

= DATE(LEFT(J3,4)+CHOOSE(MATCH(B3,{"CAPT","CDR","LCDR"}),38,35,30),MID(J3,5,2),RIGHT(J3,2))

THanks very much
Billyj

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Use Formula In Vba

You can't use that formula?

VB:
```Range("C1").Formula = "=sum(A1:B1)"

```

Excel Video Tutorials / Excel Dashboards Reports

3. Established Member
Join Date
13th July 2004
Posts
373

## Re: Use Formula In Vba

Thanks for the quick response. But I honestly do not know how to apply what you are saying. Thanks again

Excel Video Tutorials / Excel Dashboards Reports

4. ## Re: Use Formula In Vba

VB:
```Range("A1").Formula = "=DATE(LEFT(J3,4)+CHOOSE(MATCH(B3,{"CAPT","CDR","LCDR"}),38,35,30),MID(J3,5,2),RIGHT(J3,2))"

```
...would place your exact formula into cell A1.

Excel Video Tutorials / Excel Dashboards Reports

5. Established Member
Join Date
13th July 2004
Posts
373

## Re: Use Formula In Vba

Sorry brain burp.. I understand. Thanks

Excel Video Tutorials / Excel Dashboards Reports

6. ## Re: Use Formula In Vba

You need to double up the interior quotes:
VB:
```Range("A1").Formula = _
"=DATE(LEFT(J3,4) + CHOOSE(MATCH(B3, {""CAPT"",""CDR"",""LCDR""}), 38,35,30), MID(J3,5,2), RIGHT(J3,2))"

```

7. ## Re: Enter Formula With VBA

I can record the Macro but was wondering if anyone had any better ideas
That IS the best way, the Macro Recorder wont lie

8. Established Member
Join Date
13th July 2004
Posts
373

## Re: Enter Formula With VBA

Thanks everyone. I have used the recorder as well as your advice. I appreciate the help.

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno