Macro For Excel Formula

[nuttycongo123]

Hello Forum,
I am exploring a possibility to convert the following excel Formula in a valid Macro Procedure

Please Login or Register  to view this content.

Attached is the workbook for clarity.Any help will be much appreciated.
Regards

[royUK]

Why would you need to convert it? Generally inbuilt functions will be faster than VBA

[nuttycongo123]

Hello Roy,
Great to see you comment.well the possible answer to your question is as I have to use it again and again and again and the range is dynamic most often .. but still any possibility is welcome

[nuttycongo123]

..Learned Forum Members,
Any help for the query is welcome..

[abousetta]

Generated via the vba recorder:

Please Login or Register  to view this content.

It's the best place I found to convert formulas into vba.

abousetta

[tarquinious]

Hello Forum,
I am exploring a possibility to convert the following excel Formula in a valid Macro Procedure

Please Login or Register  to view this content.

Attached is the workbook for clarity.Any help will be much appreciated.
Regards

I've updated your spreadsheet with this built in as a Function. There was an anomoly in the 3rd formula where you had "D5>0" instead of "D5<0", other than this, the function performs the same as the first two formula (and the reference on the left).

You will see how to call this Function in column J.

This should at least give you the starting point for adjusting it.

[snb]

Please Login or Register  to view this content.

[nuttycongo123]

Valued Members,
I really appreciate all the responses here ,but can any of you please explain ..
1/ How to convert this into a macro
2/ how to run it as a final result ..I have very limited knowledge of VBA..So please explain the intrigues of your code at lenght..You are requested to use the same work book i attached as base.
Thanks in advance

[abousetta]

Hi,

Easiest way I know to convert a formula into a macro is to use the Excel built-in macro recorder. It records the steps you did like a tape recorder would, but instead of recording sounds it records vba steps. You can find it under the Developer Tab.

Once its recording, put your cursor inside the cell that contains the formula and click enter. WHat you have done is essentially written a formula in Excel and the macro recorder has recorded it as a vba formula.

Then all you need to do is change the name of the range (in this case the cell) that will contain the formula. For example if you want it in cell I3 then the formula would be:

Please Login or Register  to view this content.

look at my previous post for an example of what it looks like.

Now tarquinious was kind enough to create a usedefined function for you that he named GetValues. This will do all the steps as your formula without requiring you type it in each time.

I hope this helps.

abousetta

P.S. attached is an updated workbook

[nuttycongo123]

Thanks abousetta for your effort ...I really appreciate it ..And as far as tarquinious's function goes it is a fantastique way forward...and I am thankful ..But I was just wondering how to call in that function using a Macro ..I tried couple of things but it seems not to work for me ..Can there be a suggestion for this query using the attached workbook ,in anticipation with best regards

[nuttycongo123]

Anybody who can assist with the request ...

[abousetta]

Hi,

I have added another sub that will call the function:

Please Login or Register  to view this content.

I have labelled columns I, J and K to reflect where the calculation come from.

Good luck.

abousetta

[nuttycongo123]

Can you assist me in making the raange dynamic ,as the data rows and columns vary many a times and then I am stuck .Regards

[nuttycongo123]

Can we do it for dynamic range as well I am attacing the sheet which explains what I mean to say ..

[abousetta]

Sorry, I don't follow... what do you want done exactly? place formula in every row there is data, or how do you define the range for each sheet?

Please provide some more details.

abousetta

[nuttycongo123]

place formula in every row there is data

This is what I am looking for .
Regards

[abousetta]

Maybe something like this:

Please Login or Register  to view this content.

abousetta

[nuttycongo123]

Thanks will review and post..

[nuttycongo123]

Thank you for the help .This almost works .I will request following change:
1/ The header "Remarks" to come up (Ref: worksheet in I3 ) with the same format as rest of the cells
2/ It is not retaining "@" as per the conditions and the formula
Attached is the worksheet.
-Regards

[nuttycongo123]

I am able to get the "Remarks" as the header by using

Range("AA1", Range("AA" & LastRw)) = "Remarks"

,but I am not able to figure out as to how to get the formating..The second query about "@" remains unsolved..

[nuttycongo123]

Any suggestions to get this going ..Shall we consider writing a macro based on the conditions suggested as per the attachment in earlier posts
Regards

[nuttycongo123]

Bump - hoping someone to help.Attaching Worksheet for clarity
Cross Posted at :

http://vbaexpress.com/forum/showthread.php?p=244814#post244814  

Reason I am trying to generate a Macro.I will be obliged if it can be done I will keep the developments posted here as well to avoid contributors from wasting their valuable time if the post is answered.
Regards

[nuttycongo123]

Bump..hoping to get some help here..

[nuttycongo123]

Almost solved at

http://vbaexpress.com/forum/showthread.php?t=37822

Please Login or Register  to view this content.

Regards

[nuttycongo123]

Thank You Simon Llyod for you code

Credits :In Alphabetical Order

abousetta,Simon,tarquinious

Please Login or Register  to view this content.

[jaslake]

Hi N
Been following this thread...glad you got it resolved. I frankly couldn't figure out what you were trying to do.

I'd recommend that you please amend your last two posts and use code tags...you used quote tags.

[nuttycongo123]

Hi N
Been following this thread...glad you got it resolved. I frankly couldn't figure out what you were trying to do.

I'd recommend that you please amend your last two posts and use code tags...you used quote tags.

Thanks J ,I edited as recommended.. I am glad that the contributors were able to assist me One does bump into nice people like you once in a while who are selfless and kind enough to assist you..
Regards

[nuttycongo123]

Here is one more beautiful solution suggested by my friend tarquinious,it's worth a study for it's simplicity and compactness.

Please Login or Register  to view this content.

Please modify it as per your need ..as he explains further

it will be easier for to add your own different criteria into the macro if you like. Just follow the same format as the other groupings.

Regards