Posts by sachin483

Re: Addtional condition in array formula

Can some one help me out

I have this grid for commission payout arrived at this formula,but if the ach% is >120 it should pay 120% amount now reflecting zero can we modify this formula ie:- grid can be of >120 to 150 but after formula checks at end if no amount is there of that ach% it should pay the last max amount and so on

DIV ACH A
ZZ 100 16876
ZZ 101 17976
ZZ 102 19076
ZZ 103 20176
ZZ 104 21276
ZZ 105 22376
ZZ 106 25996
ZZ 107 27516
ZZ 108 29036
ZZ 109 30556
ZZ 110 32076
ZZ 111 34861
ZZ 112 36496
ZZ 113 38131
ZZ 114 39766
ZZ 115 41401
ZZ 116 44716
ZZ 117 46456
ZZ 118 48196
ZZ 119 49936
ZZ 120 51676

DD WW ACH PAY AA2 I2
ZZ A 106 25996 85 95
ZZ A 120 51676 85 95
ZZ A 125 0 85 95

The same i have posted on other forum but not replied received

http://www.excelforum.com/excel-general/1148411-addtional-condition-in-array-formula.html

http://www.mrexcel.com/forum/excel-questions/953801-commision-payout.html

Re: Simply-fly Subtotal and add some condition

Perfect Thanks a lot:congrats:

Re: Simply-fly Subtotal and add some condition

Kindly check from column no (X) to (AJ) and row no.10 like wise there is no subtotal reflecting if i extended column wise data for 2 years

Re: Simply-fly Subtotal and add some condition

It is doing the subtotal upto coloumn no (W) if the data range is extended only blank rows get inserted no subtotal relfects

Re: Simply-fly Subtotal and add some condition

Thanks KjBox your code is working fine but little change can be made if we repeat column (A) in subtotal where it is blank and if the column range get extended then also it should do subtotal and standard formatting should rename same

Re: Simply-fly Subtotal and add some condition

Thanks for the code Max1616 , while testing this i had found that while sorting column no (B) ,corresponding column does not get sorted , it should be sorted as the subtotal we are getting for corresponding column and grand total is also not reflecting for the 3 column we are sorting and doing subtotal in the end, and if the column are extended subtotal not reflecting

Re: Simply-fly Subtotal and add some condition

Thanks a lot

Simply-fly Subtotal and add some condition

please find enclosed file for which i do sorting and subtotal in data sheet and the result sheet, After sorting and subtotal i colored all the totals and then concatenate with some character for reference purpose and put them in column number (I) then do the paste special

how to simply-fly this macro

i have recorded the macro while doing subtotal i want to simply-fly the process

1.Sorting should not be of specific range , it should be for full column range rows in data can increase or decrease ,column sorting order is ok .

2. There are 3 subtotal - for 1st subtotal only Grand Total must be there in the end for other 2 not required - how to remove

3. Color for all the 3 subtotal can be done

4.Subtotal is not delete after last subtotal

Sub subtotal()'' subtotal Macro''    Range(Selection, Selection.End(xlToRight)).Select    Range(Selection, Selection.End(xlDown)).Select    ActiveWorkbook.Worksheets("12").Sort.SortFields.Clear    ActiveWorkbook.Worksheets("12").Sort.SortFields.Add Key:=Range("B2:B80"), _        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal    ActiveWorkbook.Worksheets("12").Sort.SortFields.Add Key:=Range("D2:D80"), _        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal    ActiveWorkbook.Worksheets("12").Sort.SortFields.Add Key:=Range("F2:F80"), _        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal    ActiveWorkbook.Worksheets("12").Sort.SortFields.Add Key:=Range("H2:H80"), _        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal    With ActiveWorkbook.Worksheets("12").Sort        .SetRange Range("A1:W80")        .Header = xlYes        .MatchCase = False        .Orientation = xlTopToBottom        .SortMethod = xlPinYin        .Apply    End With    Range("A1").Select    Range(Selection, Selection.End(xlToRight)).Select    Range(Selection, Selection.End(xlDown)).Select    Selection.subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(10, 11, 12 _        , 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23), Replace:=True, PageBreaks:=False, _        SummaryBelowData:=True    Selection.Copy    Application.Run "ASAPRunProc268"    Application.CutCopyMode = False    Selection.RemoveSubtotal    Selection.subtotal GroupBy:=5, Function:=xlSum, TotalList:=Array(10, 11, 12 _        , 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23), Replace:=True, PageBreaks:=False, _        SummaryBelowData:=True    Selection.Copy    Application.Run "ASAPRunProc268"    Application.CutCopyMode = False    Selection.RemoveSubtotal    Selection.subtotal GroupBy:=7, Function:=xlSum, TotalList:=Array(10, 11, 12 _        , 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23), Replace:=True, PageBreaks:=False, _        SummaryBelowData:=True    Selection.Copy    Application.Run "ASAPRunProc268"    Application.CutCopyMode = False    Selection.RemoveSubtotal    Range("A1").SelectEnd Sub

and the has been posted on another forum link below
http://www.mrexcel.com/forum/e…l-add-some-condition.html

Re: split word document

Thanks a lot you made my day...........

Re: split word document

sorry for the delay

the format and font should remained same if it is landscape mode the it should split in landscape or if portrait then portrait,after split the file name will the search criteria

for example i want split the file by searching the key word -XP01 there are 4 pages it should create a new word file and append all the pages one below another like that i have around 500 pages in word which i have to separate based on criteria

Re: Email through excel from lotus notes 9

But i could not understood where to change in the macro so i re-posted

i have this macro for sending email with email-id , subject , body of the email from excel , but it is sending one email at a time on which the cursor is , i want to modify it with sending email to all the email address present in column one after another and if the email-id is same then also it should send, please let us know where should i modify as i am new to VB.

Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long




Sub SendEMail()
Dim Email As String, Subj As String
Dim Msg As String, URL As String
Dim r As Integer, x As Double




r = ActiveCell.Row
'Get the email address
Email = Cells(r, 3)




'Message subject
Subj = Cells(r, 6)




'Compose the message
Msg = ""
Msg = Msg & Cells(r, 7) & "," & vbCrLf & vbCrLf




'Replace spaces with %20 (hex)
Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")




'Replace carriage returns with %0D%0A (hex)
Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")




'Create the URL
URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg




'Execute the URL (start the email client)
ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus




'Wait two seconds before sending keystrokes
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%1"
End Sub

I want to match coloumn DIV & CLS of coloumn A & B with coloumn H and row - 1 to get the result, can anyone help me out

Re: Selecting particular block

This is the ongoing activity hence can anybody help me out if possible

I have this extension list which there are many departments , first it has to sorted alphbetically for each any every department with ext no,then from this i want a particular list of department to be selected from the sheet1 and to be pasted in sheet2 one below another, then the blank row should be deleted from sheet1

Eg:- i want to select department "abc1","abc4","abc9","abc5","abc15","abc16" and to be pasted in sheet2 this i want dynamic in vba so that i can change the department

Re: If and condition

sorry for inconvenience cause from next time onward i will take care, hence kindly provide me the loop formula

Re: If and condition

i want a formula which can loop in the grid

Re: If and condition

thanks for the helpbut formula is not looping in the grid