Convert Formulas to VBA Code

**KumarPK** · 04-29-2015, 01:01 AM

Dear Experts,

Kindly Convert the below formulas to VBA code.

Sheet1 having morethan 25000 lines. Please help me its very urgent.

=IF(BT3=AI3,"CURRENT MONTH","CATCHUP/CATCHDOWN").............Values in (AK3)
=IF(CB3<0,0,IF(CB3>1,1,CB3))........ ...................Values in (AL3)
=IF(ISERROR((AL3/CD3)*MIN(CF3,CG3,CE3)),0,((AL3/CD3)*MIN(CF3,CG3,CE3))) .............Values in (AM3)
=IF(BZ3="CURRENT MONTH",IF(AL3<0,0,AL3),0)............................................Values in (AN3)
=IF(CC3=0,0,SUMIF(A:A,A3,CC:CC)).......................................................Values in (AO3)
=IF(BZ3="CURRENT MONTH",IF(C3<$CG$1,100%,IF(ISERROR((D3-C3)/($CG$2-$CG$1)),100%,((D3-C3)/($CG$2-$CG$1)))),0)....Values in (AP3)
=IF(BZ3="CURRENT MONTH",IF(C3<$CG$1,100%,($CG$2-C3)/($CG$2-$CG$1)),0) ...............................Values in (AQ3)
=IF(BZ3="CURRENT MONTH",IF(D3="NULL",100%,IF(D3>$CG$2,100%,(D3-$CG$1)/($CG$2-$CG$1))),0)...............Values in (AR3)
=IF(BZ3="CURRENT MONTH",IF(OR(AO3="NULL",AO3=100633,AO3=104857,AO3=100634),0,AL3),0) ..............Values in (AW3)
=IF(BZ3="CURRENT MONTH",AK3,0) .................................................................Values in (AT3)
=IF(BZ3="CURRENT MONTH",SUMIF(A:A,A3,CH:CH),0) ............................Values in (AU3)
=IF(BZ3="CURRENT MONTH",SUMIF(A:A,A3,CI:CI),0) .........................................Values in (AV3)
=IF(OR(CH3=0,AV3="company",BM3="Level 9",BM3="Level 10",BM3="Level 11",CJ3<80),"-",IF(CK3=0,"Buffer","-")).............Values in (AW3)
=IF(CL3="Buffer",CA3,0) ........................................................Values in (AX3)
=IF(OR(BM3="level 1",BM3="level 2"),CA3,0)..................................Values in (AY3)

Thanks,

Kumar

**LokeshKumar** · 04-29-2015, 02:41 AM

Hi,
Your requirement is not clear....you can not convert this to VBA code.....but you can do something in VBA so that output will remain same....

**bagullo** · 04-29-2015, 02:42 AM

you want to set formulas by vba code?
calculate by vba and paste the only the result?

it might be better to calculate the formula and then copy-paste values. I do it all the time.
you should rewrite the formulas to use named ranges, so that the formula is exactly the same in all rows.

then you can leave formulas in a auxiliary row (at the top of the sheet for instance) and then just copy the formula to the full column.

fullcolumnRange.formula = auxRowcell.formula

and then to convert to values you can just do

fullcolumnrange.value = fullcolumnrange.value

as long as no errors appear as result. Otherwise you can do copy, and pastespecial xlpastevalues of fullcolumnrange

HTH

**KumarPK** · 04-29-2015, 09:40 AM

Hi Bagullo,

Yes, I want to set formulas by vba code. I am just learning VBA and not familier with codes.

I have already created a command button to run this codes.

Please write the codes......

Thanks

Kumar

**KumarPK** · 04-29-2015, 10:01 AM

Hi Experts,

Yes, I want to set formulas by vba code. I am just learning VBA and not familier with codes.

I have already created a command button to run this codes.

Please write the codes......

Thanks

Kumar

**JOHN H. DAVIS** · 04-29-2015, 10:12 AM

Maybe?

Please Login or Register  to view this content.

**amity21** · 04-29-2015, 10:14 AM

Originally Posted by KumarPK

Hi Experts,

Yes, I want to set formulas by vba code. I am just learning VBA and not familier with codes.

I have already created a command button to run this codes.

Please write the codes......

Thanks

Kumar

Hello Kumar,

It will be easier for us to write the code if we actually know what kind of input you are placing in the cells. Is it possible for you to share the excel that you currently have using these formulas?

**KumarPK** · 04-29-2015, 12:58 PM

Hi John, Thank you so much for reply, I have pasted your code but its not working.( There is no error msg also).

I m trying to upload the xl file, but its not uploading.

The base data contains in Sheet1

Let me know if any more information.

Thanks

Kumar.

**JOHN H. DAVIS** · 04-29-2015, 01:04 PM

I can't see why it doesn't work, unless I misunderstood? However, if you load the sample, then I could test it too see. BTW Sheet1 should be active before running the code.

**KumarPK** · 04-29-2015, 01:31 PM

John, Is there any other way to upload the sample file...

I am trying to upload but Its not uploading( File size is 21kb)

This is my first question and First time I have entered into this forum.

Please help me..

Thanks

Kumar

**JOHN H. DAVIS** · 04-29-2015, 01:33 PM

You don't need to upload the entire file if it is too large. Just create a sample with 50 lines or less for us to test on.

**KumarPK** · 04-29-2015, 01:52 PM

John, I have added only 20 lines but its not uploading..is there any other way to upload...??

**JOHN H. DAVIS** · 04-29-2015, 01:58 PM

Scroll down towards the bottom of this thread and click the button Go Advanced - then scroll down to the Manage Attachments button, browse to the required file, and then push the Upload button and click Done.

**KumarPK** · 04-29-2015, 02:18 PM

John,

My badluck its not upload, I have followed your procedure, its taking longtime to upload , still its not upload.

Shall I send mail to you... if Yes... Please provide your email ID..

Thnx
Kumar

**JOHN H. DAVIS** · 04-29-2015, 02:26 PM

See my PM.

**KumarPK** · 04-29-2015, 02:33 PM

Hi John,

I have sent the Sample XL file to your mail box.

Pls do the needful.

Thanks

Kumar

**JOHN H. DAVIS** · 04-29-2015, 03:41 PM

Try:

Please Login or Register  to view this content.

For your sample.

**JOHN H. DAVIS** · 04-29-2015, 03:46 PM

I added a module, revised your range and included it in the attachment.

**KumarPK** · 04-29-2015, 09:17 PM

Working perfectly....................Wow John its great.... Appreciate your Fabulous job.. you made my day.... God Bless..

John & Excel Forum Thank you so much

**JOHN H. DAVIS** · 04-30-2015, 06:17 AM

You're welcome. Glad to help out and thanks for the feedback. Please comply with Forum Rules and mark this thread as SOLVED. See link below for guidance.

http://www.excelforum.com/forum-rule...rum-rules.html

**KumarPK** · 04-30-2015, 06:29 AM

Hi John, Sorry for the disturbance...

Again we have some challenging inputs....I feel this is piece of cake for you..

1.
When I pulled the data from the system the date show is custom value and its not in proper way.
We are manually replacing the data with Correct date format in the following columns.

Column - Range C:D
Column - Range H:I
Column - Range BR:BS

I request this needs to be replaced with correct date format from Custom to Date format.

2.
There is a blank cells when we pulled the data from Range A:BY.
This should be replace with Null instead of blanks

I have sent the XL file for your mail box due to unable to upload the file in forum.

I request you to please do the needful.

Thanks

Kumar

**JOHN H. DAVIS** · 04-30-2015, 06:50 AM

Try:

Please Login or Register  to view this content.

**KumarPK** · 04-30-2015, 07:16 AM

John,

Thanks for reply.
Everything working fine but only Column "D" instead of Null its filling up the timings for blank cells. I have marked in Yellow.

Rest everything is file.

Thanks

Kumar

**JOHN H. DAVIS** · 04-30-2015, 07:33 AM

OK Try:

Please Login or Register  to view this content.

**KumarPK** · 04-30-2015, 07:39 AM

WOW.........Working Perfectly. This is I was looking for...

Thank you so much John... Have a wonderful day...

Thnx
KumarPK

**KumarPK** · 04-30-2015, 07:46 AM

Excellent.... SOLVED

**JOHN H. DAVIS** · 04-30-2015, 07:54 AM

Once again welcome. Please comply with Forum Rules and mark this thread as SOLVED. See link below for guidance.

http://www.excelforum.com/forum-rule...rum-rules.html

Convert Formulas to VBA Code

Re: Convert Formulas to VBA Code

Re: Convert Formulas to VBA Code

Re: Convert Formulas to VBA Code

Re: Convert Formulas to VBA Code

Re: Convert Formulas to VBA Code

Re: Convert Formulas to VBA Code

Re: Convert Formulas to VBA Code

Re: Convert Formulas to VBA Code

Re: Convert Formulas to VBA Code

Re: Convert Formulas to VBA Code

Re: Convert Formulas to VBA Code

Re: Convert Formulas to VBA Code

Re: Convert Formulas to VBA Code

Re: Convert Formulas to VBA Code

Re: Convert Formulas to VBA Code

Re: Convert Formulas to VBA Code

Re: Convert Formulas to VBA Code

Re: Convert Formulas to VBA Code

Re: Convert Formulas to VBA Code

Re: Convert Formulas to VBA Code

Re: Convert Formulas to VBA Code

Re: Convert Formulas to VBA Code

Re: Convert Formulas to VBA Code

Re: Convert Formulas to VBA Code

Re: Convert Formulas to VBA Code

Re: Convert Formulas to VBA Code

Thread Information

Users Browsing this Thread

Similar Threads

Bookmarks

Bookmarks

Posting Permissions