Macro #5

Hi i need to write a macro and have no idea how to do it if some one can show 
me form scratch

Ihave different formalus in different column which calculate commission for 
agent 
Eg : if(d1247>300,"$15",if(d1247>100,"$12","$10"))-(j1247+k1247)

=IF(D1246>1500,"$25",IF(D1246>1000,"$20",IF(D1246>500,"$15","10")))-(J1246+K1246)

this 2 different formula in same column 

i want to write a macro so i dont have to use two different formula when i 
say certain column is X then its should run 1st formula if i select "y" it 
should run 2nd formula.

Any help is appreciated.


-- 
Nisha P
0
nishkrish (19)
6/3/2008 5:50:02 PM
excel.newusers 15348 articles. 2 followers. Follow

5 Replies
394 Views

Similar Articles

[PageSpeed] 3

hi,

Before attempting to answer your question what is "$15" etc in your formula 
because it seems to me that the only correct answer you formula can return is 
when none of the conditions evaluate as true (D1246 is less than 500) and the 
formula returns J1246+K1246.

If any condition does evaluate as TRUE then it tries to subtract J1246+K1246 
from a text value and gives an error.

Mike

"nishkrish" wrote:

> Hi i need to write a macro and have no idea how to do it if some one can show 
> me form scratch
> 
> Ihave different formalus in different column which calculate commission for 
> agent 
> Eg : if(d1247>300,"$15",if(d1247>100,"$12","$10"))-(j1247+k1247)
> 
> =IF(D1246>1500,"$25",IF(D1246>1000,"$20",IF(D1246>500,"$15","10")))-(J1246+K1246)
> 
> this 2 different formula in same column 
> 
> i want to write a macro so i dont have to use two different formula when i 
> say certain column is X then its should run 1st formula if i select "y" it 
> should run 2nd formula.
> 
> Any help is appreciated.
> 
> 
> -- 
> Nisha P
0
MikeH (222)
6/3/2008 7:12:00 PM
its the formula which calculates amount if the sent amount is lessthen $100 
then charge should be $10, so on and so forth.

-- 
Nisha P


"Mike H" wrote:

> hi,
> 
> Before attempting to answer your question what is "$15" etc in your formula 
> because it seems to me that the only correct answer you formula can return is 
> when none of the conditions evaluate as true (D1246 is less than 500) and the 
> formula returns J1246+K1246.
> 
> If any condition does evaluate as TRUE then it tries to subtract J1246+K1246 
> from a text value and gives an error.
> 
> Mike
> 
> "nishkrish" wrote:
> 
> > Hi i need to write a macro and have no idea how to do it if some one can show 
> > me form scratch
> > 
> > Ihave different formalus in different column which calculate commission for 
> > agent 
> > Eg : if(d1247>300,"$15",if(d1247>100,"$12","$10"))-(j1247+k1247)
> > 
> > =IF(D1246>1500,"$25",IF(D1246>1000,"$20",IF(D1246>500,"$15","10")))-(J1246+K1246)
> > 
> > this 2 different formula in same column 
> > 
> > i want to write a macro so i dont have to use two different formula when i 
> > say certain column is X then its should run 1st formula if i select "y" it 
> > should run 2nd formula.
> > 
> > Any help is appreciated.
> > 
> > 
> > -- 
> > Nisha P
0
nishkrish (19)
6/3/2008 9:10:01 PM
Hi

There is no need for a macro to achieve what you want.
just include another IF clause, testing against your selection of X.
I used Cell A1 to either hold X or not

=IF(A1="X",
IF(D1246>1500,25,
IF(D1246>1000,20,
IF(D1246>500,15,10))),
IF(D1247>300,15,
IF(D1247>100,12,10)))
-(J1246+K1246)

I have split the formula on each IF so you can read it more clearly, and to 
prevent the newsreader from breaking it in strange places. It is all one 
long formula really.

Note that I have removed the "  " around your values. As you had the 
formula, it would place text values in the cell, which when you try to 
deduct (J1246+K1246) at the end, would produce a #VALUE result.
Format the cell as Currency $ if you wish it to show as Dollar amounts

-- 
Regards
Roger Govier

"nishkrish" <nishkrish@discussions.microsoft.com> wrote in message 
news:6C21A239-851A-4F84-BFC0-C2483C2143B3@microsoft.com...
> Hi i need to write a macro and have no idea how to do it if some one can 
> show
> me form scratch
>
> Ihave different formalus in different column which calculate commission 
> for
> agent
> Eg : if(d1247>300,"$15",if(d1247>100,"$12","$10"))-(j1247+k1247)
>
> =IF(D1246>1500,"$25",IF(D1246>1000,"$20",IF(D1246>500,"$15","10")))-(J1246+K1246)
>
> this 2 different formula in same column
>
> i want to write a macro so i dont have to use two different formula when i
> say certain column is X then its should run 1st formula if i select "y" it
> should run 2nd formula.
>
> Any help is appreciated.
>
>
> -- 
> Nisha P 

0
Roger
6/4/2008 7:04:47 AM
Roger

this cannot work for since it has to 2 formulas if a= x then 1st formula 
should run and if a=y then 2nd formula should run.


-- 
Nisha P


"Roger Govier" wrote:

> Hi
> 
> There is no need for a macro to achieve what you want.
> just include another IF clause, testing against your selection of X.
> I used Cell A1 to either hold X or not
> 
> =IF(A1="X",
> IF(D1246>1500,25,
> IF(D1246>1000,20,
> IF(D1246>500,15,10))),
> IF(D1247>300,15,
> IF(D1247>100,12,10)))
> -(J1246+K1246)
> 
> I have split the formula on each IF so you can read it more clearly, and to 
> prevent the newsreader from breaking it in strange places. It is all one 
> long formula really.
> 
> Note that I have removed the "  " around your values. As you had the 
> formula, it would place text values in the cell, which when you try to 
> deduct (J1246+K1246) at the end, would produce a #VALUE result.
> Format the cell as Currency $ if you wish it to show as Dollar amounts
> 
> -- 
> Regards
> Roger Govier
> 
> "nishkrish" <nishkrish@discussions.microsoft.com> wrote in message 
> news:6C21A239-851A-4F84-BFC0-C2483C2143B3@microsoft.com...
> > Hi i need to write a macro and have no idea how to do it if some one can 
> > show
> > me form scratch
> >
> > Ihave different formalus in different column which calculate commission 
> > for
> > agent
> > Eg : if(d1247>300,"$15",if(d1247>100,"$12","$10"))-(j1247+k1247)
> >
> > =IF(D1246>1500,"$25",IF(D1246>1000,"$20",IF(D1246>500,"$15","10")))-(J1246+K1246)
> >
> > this 2 different formula in same column
> >
> > i want to write a macro so i dont have to use two different formula when i
> > say certain column is X then its should run 1st formula if i select "y" it
> > should run 2nd formula.
> >
> > Any help is appreciated.
> >
> >
> > -- 
> > Nisha P 
> 
0
nishkrish (19)
6/4/2008 9:05:46 PM
Hi
Sorry, typo with row numbers

=IF(A1="X",
IF(D1246>1500,25,
IF(D1246>1000,20,
IF(D1246>500,15,10))),
IF(D1246>300,15,
IF(D1246>100,12,10)))
-(J1246+K1246)

It does run 2 different formulae.
If A1 = X then
IF(D1246>1500,25,
IF(D1246>1000,20,
IF(D1246>500,15,10)))

Else if A1 <>"X" then
IF(D1246>300,15,
IF(D1246>100,12,10))

In both cases take the following from the result
-(J1246+K1246)

If you want a situation where A1 could hold something other than X or Y, 
then you could modify the formula to
=IF(A1="X",
IF(D1246>1500,25,
IF(D1246>1000,20,
IF(D1246>500,15,10))),
IF(A1="Y",
IF(D1246>300,15,
IF(D1246>100,12,10)),0))
-(J1246+K1246)

In which case if A1 was neither X nor Y, the only part of the formula that 
would be invoked would be
-(J1246+K1246)
-- 
Regards
Roger Govier

"nishkrish" <nishkrish@discussions.microsoft.com> wrote in message 
news:16D549BA-B486-44BC-A69D-8119F7810C20@microsoft.com...
>
> Roger
>
> this cannot work for since it has to 2 formulas if a= x then 1st formula
> should run and if a=y then 2nd formula should run.
>
>
> -- 
> Nisha P
>
>
> "Roger Govier" wrote:
>
>> Hi
>>
>> There is no need for a macro to achieve what you want.
>> just include another IF clause, testing against your selection of X.
>> I used Cell A1 to either hold X or not
>>
>> =IF(A1="X",
>> IF(D1246>1500,25,
>> IF(D1246>1000,20,
>> IF(D1246>500,15,10))),
>> IF(D1247>300,15,
>> IF(D1247>100,12,10)))
>> -(J1246+K1246)
>>
>> I have split the formula on each IF so you can read it more clearly, and 
>> to
>> prevent the newsreader from breaking it in strange places. It is all one
>> long formula really.
>>
>> Note that I have removed the "  " around your values. As you had the
>> formula, it would place text values in the cell, which when you try to
>> deduct (J1246+K1246) at the end, would produce a #VALUE result.
>> Format the cell as Currency $ if you wish it to show as Dollar amounts
>>
>> -- 
>> Regards
>> Roger Govier
>>
>> "nishkrish" <nishkrish@discussions.microsoft.com> wrote in message
>> news:6C21A239-851A-4F84-BFC0-C2483C2143B3@microsoft.com...
>> > Hi i need to write a macro and have no idea how to do it if some one 
>> > can
>> > show
>> > me form scratch
>> >
>> > Ihave different formalus in different column which calculate commission
>> > for
>> > agent
>> > Eg : if(d1247>300,"$15",if(d1247>100,"$12","$10"))-(j1247+k1247)
>> >
>> > =IF(D1246>1500,"$25",IF(D1246>1000,"$20",IF(D1246>500,"$15","10")))-(J1246+K1246)
>> >
>> > this 2 different formula in same column
>> >
>> > i want to write a macro so i dont have to use two different formula 
>> > when i
>> > say certain column is X then its should run 1st formula if i select "y" 
>> > it
>> > should run 2nd formula.
>> >
>> > Any help is appreciated.
>> >
>> >
>> > -- 
>> > Nisha P
>> 
0
Roger
6/5/2008 7:23:09 AM
Reply:

Similar Artilces:

automatically run a macro everyday
Dear all, I am going for vacation in two days. I need to run my VBA code automatically. Everyday, I get one excel workbook by using another macro. This workbook contains some values. I have written a vba code in Excel_Macro.xls which does the work for me. This vba code generates Pivot table in sheets in to Excel_Macro.xls. Script In Excel_Macro.xls Sheets("Pivot_Table").Select With Worksheets("Pivot_Table") Range("A:K").Select With Selection Cells.Clear End With Dim PT_9_Cache ...

importing text file into excel file using macro
Hi, How to import text file into excel file using Macro? Can this proces run on form menu button? Please help and give me some ideas of doin it. I have attached a text file for your reference. Thanks, An Attachment filename: rdata.txt Download attachment: http://www.excelforum.com/attachment.php?postid=66130 -- Message posted from http://www.ExcelForum.com >Hi, >How to import text file into excel file using Macro? Can this process >run on form menu button? Please help and give me some ideas of doing >it. I have attached a text file for your r...

Exchange 5.5 DL's in Exchange 2003 GAL
we are currently running a mixed mode environment (Exchange 5.5 and Exchange 2003) which has two domains. Exchange 2003 is running in Domain A, and Domain B contains both 2003 and 5.5. The problem that we have is that Exchange 5.5 DL's in doamin A show up in the Exchange 2003 GAL, but Exchange 5.5 DL's from domain B do not. I have had a look at the ADC and everything appears to be ok. Take a look at these: http://support.microsoft.com/default.aspx?scid=kb;en-us;275194 http://support.microsoft.com/default.aspx?scid=kb;en-us;329200 "swingman" <steven.jones@arup.com&g...

Macro Subroutines
Hi I want to build a new macro to run a sequence of macros already built, would like these subroutines to run after a delay of 4 seconds so as t not overload my computer. So when i hit the macro button on the toolbar i would like macro one t run straight away,then macro two four seconds after pressing th button,then macro three four seconds later(8 seconds after pressing th toolbar button),etc. Here are two of the macro subroutines that i want to build into the ne macro:- Sub DAILYSOLVER() ' ' DAILYSOLVER Macro ' Macro recorded 25/01/2004 by ADE ' ' SolverReset Solver...

fix biz portal 2.5 copy req funct. is very buggy
When using the copy functionality of a saved requisition to create a new one, the newly created req becomes corrupted and does not flow through workflow. The creator become the last to update and the currently assigned to. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message...

OWA Access #5
I have exchange server 2003 running. OWA had been working correctly both inside and outside of the network. All of a sudden if using OWA inside of the network, if I type http://server/exchange it automatically logs me in as the user I am logged into the nextwork. Previously it would ask for a user name and a password. Is there something that could of gotten changed to cause this to occur. Thanks Jon "jonl" <jonl@discussions.microsoft.com> wrote: >I have exchange server 2003 running. OWA had been working correctly both >inside and outside of the network. Al...

new to excel #5
I need a 31 day workbook with the 32 worksheet as a monthly. I need to add totals for all 31 on the 32 sheet for the monthly totals. I hope this makes sense. Assuming the 31 sheets have their totals in same cell. In Sheet32 in a cell enter =SUM(Sheet1:Sheet31!A10) Where A1 is total cell on each sheet. Gord Dibben MS Excel MVP On Wed, 27 May 2009 19:11:01 -0700, adbolak <adbolak@discussions.microsoft.com> wrote: >I need a 31 day workbook with the 32 worksheet as a monthly. I need to add >totals for all 31 on the 32 sheet for the monthly totals. I hope this makes &g...

[ANN] Office 11.5 and 12.1.1
Hi All, The Office 11.5 and 12.1.1 updates are now up on MacTopia: <http://www.microsoft.com/mac/downloads.mspx> They are not yet available through auto-update, but they'll probably be in a few days. The respective release notes for these updates are on <http://support.microsoft.com/kb/953824> and <http://support.microsoft.com/kb/953822> The Office 12.1.1 udpate for Office 2008 fixes the double-click issue apparently. Corentin -- --- Mac:MS MVP http://www.cortig.net/wordpress/ --- http://www.mvps.org - http://mvp.suppor...

Sigh.. Macro delay still not working
Hi all, I have this code implemented, which should prohibit the CDO_Send macr to run if it already ran 10 minutes ago.. However, somehow it currently does not trigger the macro at al anymore. Sometimes when i just opened the file and something changes i does trigger.., there is no logic behind it though. I've tried to mov the dlastsent line between the 'end if' at the bottom.. yet no matte what i do, it does not work. So now and then it (:s) also gives a type mismatch error in thi line: If myVals(iRow, iCol) = myRng.Cells(iRow, iCol) Then Cells in the range change through an ...

Question regarding Exchange 5.5 and 2003 running at the same time
I recently upgraded my domain from NT 4.0 to 2003 in mix mode. I still have a few BDCs and Exchange 5.5 running with no problems. To prepare for Exchange 2003 install I ran forest prep, domain prep and ADC connector. Can I install Exchange 2003 with Exchange 5.5 running at the same time. What downtime should I expect? What could go wrong?? If anyone has any insight or comments feel free to pass it on. DC There are a bunch of documents and good books available on that subject. Search on the microsoft site for keywords "5.5 migrate 2003" I followed the instructions carefully,...

Macro wait
Is there a macro command that I could add into a macro to tell it to wait for xx minutes (while something is running in the background)? Or maybe for it to wait for all processes to finish before continuing? JP, You can insert a pause into a macro, but this is as close as you will get, since you will need to manually trigger it to continue. Now if what you are looking for is to execute a macro (which in turn performs some actions) on a scheduled basis after some other process is completed, you can use the Macro Scheduler in Professional Services Tools Library. It sounds, however, you ...

invalid page fault #5
Trying to find what has caused this, OS 98, uninstalled office 2000 prof.,completely reinstalled, also did a repair to no avail, ran SFC, scandisk....any suggestions or fixes. OUTLOOK caused an invalid page fault in module MSOUPLUG.DLL at 0167:10016d58. Registers: EAX=0056f874 CS=0167 EIP=10016d58 EFLGS=00010216 EBX=021e4240 SS=016f ESP=0056f550 EBP=0056f880 ECX=00000000 DS=016f ESI=021e50bc FS=4157 EDX=0056f55c ES=016f EDI=10027924 GS=0000 Bytes at CS:EIP: 8b 01 57 c7 44 24 20 00 00 00 00 ff 50 18 3d 00 Stack dump: 0056f55c 021e4f41 021e4244 00000000 00000104 0056f874 10026058 ffffffff ...

Macro slow (worked well until I upgraded my machine)
I am doing an iterative hide of columns that of all but ones which meet the day of the week criteria. The macro ran fine for years through the upgrade from 2003 to office 2007. I have just upgraded my machine to a more powerful machine and my macro now takes has increased processing time by 50 to 60 times for this iterative hide. I have noticed that machines that went through the office 2003 installation prior to 2007 still run it fine. Example dell latitude d620 that never had 2003 takes 2 to 3 minutes to complete. same type of laptop that did go through the pa...

My chart in Excell will only let me use 5 dates. ?
I put in 5 dates on the chart that I am using to track weight loss. When I add more dates in Excel the chart does not see them. What do I do? Hi, If you can see the chart and the data at the same time. Click the series on the chart and notice the highlight in the spreadsheet around the data. The bottom right corner of each highlight has a small square in the same color as the highlight. Place your mouse over the square, when you see a 2-headed arrow drag to expand the range. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "WillB" wrote: > I ...

IF formulas #5
I'm wanting to continue this function all the way to "Z" and "9". This is used to quickly cypher names for phone pad input. It takes 4 formulas to achieve this since Excel only allows 7 IF functions. =IF(B10="A","2",IF(B10="B","2",IF(B10="C","2",IF(B10="D","3",IF(B10="E","3",IF(B10="F","3",IF(B10="G","4",IF(B10="H","4")))))))) I've completed all 4 formulas but I'm wanting to display the result under...

Calendar Sharing #5
Outlook 2007. Windows vista. Suddenly (past three days), cannot update shared calendars through MS web server. Keeps asking for password and nothing happens Anyone having this trouble or know what's going on? Thanks Bill The web service occasionally has issues - we usually recommend waiting a couple of days and trying again. -- Diane Poremsky [MVP - Outlook] Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net Exchange Messaging Outloo...

Help with Macro #2
I need to create a macro to modify a spreadsheet into which I have imported an address list. The steps I am unsure about are:- Go to the last 2 rows which contain text and delete, the last row could vary each time the list is imported. In col A if an X is present delete the row. In col H delete all rows which do not contain a V Hope you can help Thanks Alistair Alistair, This should do it for you Dim cLastRow As Long Dim i As Long Dim oRng As Range cLastRow = Cells(Rows.Count, "A").End(xlUp).Row Set oRng = Cells(cLastRow, 1).EntireRow Set oRng = Union(oRng, Cells(cLa...

Wince 5.0 : why does ASSERT_KINDOF fails on a derived class
class MainCoreTab : public CPropertyPage { .... } .... MainCoreTab mainCoreTab; .... mainCoreTab.IsKindOf(RUNTIME_CLASS(CPropertyPage)); returns false where mainCoreTab.IsKindOf(RUNTIME_CLASS(MainCoreTab)); returns true. What am I missing here? on http://msdn2.microsoft.com/en-us/library/4d38h138.aspx : "This macro asserts that the object pointed to is an object of the specified class, or is an object of a class derived from the specified class." fixed: rechecked my DECLARE_DYNAMIC and IMPLEMENT_DYNAMIC macros.... ...

Exchange 5.5 w32.Sober.X@mm
Is there any way I can find out the IP of the PC infected with the Sober.X@mm. I kept receiving tons of e-mail from NAV for Exchange about e-mail infected with the worm. Thanks, Ismael Hi Ismael, "Ismael" <Ismael@discussions.microsoft.com> wrote in message news:034EC4EE-2CA3-420A-84BB-DC74271A9973@microsoft.com... > Is there any way I can find out the IP of the PC infected with the > Sober.X@mm. I kept receiving tons of e-mail from NAV for Exchange about > e-mail infected with the worm. be sure, that the Source of the Emails is inside you network. You may use...

Category Groups #5
I think I understand setting up categories and subcategories. However, as part of the process, you have assign each category to a Category Group. How do you set up/modify the various Category Groups? Thanx, CB In microsoft.public.money, Charlie Brown wrote: >I think I understand setting up categories and >subcategories. However, as part of the process, you have >assign each category to a Category Group. How do you set >up/modify the various Category Groups? See FAQ available at http://www.bollar.org/msmoney/ for information. ...

Excel Won't close in .NET 3.5 using C#
I have tryed all the various versions of closing an excel object, but it still remains in the Task Manager. CODE SAMPLE ONE: ===================================== xlBook.SaveAs(getFile, Excel.XlFileFormat.xlWorkbookNormal, null, null, true, false, Excel.XlSaveAsAccessMode.xlShared, false, false, null, null, null); xlBook.Close(null, null, null); xlApp.Workbooks.Close(); xlApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook)...

outlook 2007 and activesync 4.5
After reinstalling wxp and office 2007 i can't synchronise my Dell-PDA in outlook. Activesync says: Problem 85010014 on desktop.... I have reinstalled Outlook / office / activesync. Nothing worked. It has something totdo with outlook, but untilll now nobody coud help me out. You can...?! i hope. My pda says that there has been an sync... "Marius" <Marius@discussions.microsoft.com> wrote in message news:CCA1BF9E-FB29-4F77-BB0A-C02172FAC0AF@microsoft.com... > After reinstalling wxp and office 2007 i can't synchronise my Dell-PDA in > outlook. > A...

Hyperlink #5
Is it possible to make the destination of a hyperlink a graph o graphic -- Message posted from http://www.ExcelForum.com Hi not without using VBA and triggering the selection_change event of your worksheet. Have a look at http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=209#jon016 for some code Frank > Is it possible to make the destination of a hyperlink a graph or > graphic? ...

Can not seem to get Csharp to talk to the sql compact 3.5 database
Hi I am new to csharp. I am trying to get my Csharp windows app to connect to the sql compact database. I am using Visual Csharp Express 2008 and I created the database using the Csharp express IDE. It is in the project folder. I created the tables and added elements to the table manually with the IDE. So the IDE seems to connect to the database. I can also contact the database using the SQL Server management studio. When I created the database I had the connection string placed in the config.app file. A string does appear to exist there. When I run the app. I can not seem to g...

What r Macros?
Hope it is not a silly question, but I don't have a complete understanding what exactly are Macros? Thanks. Macros are for recording actions so you can run them over and over again. Try a simple macro by typing a word in any cell. Now go to tools/macro/record new macro. Select the cell you earlier typed into and filldown a few cells. Go back now to tools/macro/stop recording. Delete the cells you earlier filled down, leaving the origional cell with data. Go now to tools/macro/macros and select Macro1 then click run. The filldown action you recorded will be played. Play around with r...