Send excel range as mail body HTML & email

Hi,

Many company requires to send Ledger summary to their client, which requires un-necessary copy & paste from excel to email, in this macro you need to select range which you want to email (In this case select yellow color highlighted cell) & press Alt + F8 to run macro.  A mail will be automatically created which will be ready to send.

Share your suggestion & feedback.

Cheers!!


Click to Download

Excel formula to calculate commission on sales for different slabs

HI,

Although a flat-rate sales commission is fairly straightforward to calculate, most modern commission plans include tiers and bonus plans. In this post I will explain how to calculate a commission payout where slab structure is involved. Calculating commissions for as slab structure is difficult because you are required to calculate commission for different slabs at the different rate.

Below given us slab used for given example:

Sales Amount	Commission %
20000	0%
30000	5%
40000	7%
50000	10%
60000	12%
70000	13%
80000	14%
90000	15%
100000	16%
110000	17%
120000	18%
130000	19%
99999999	19%

so for commission for sales upto 20k is nil, for next 10k it is 5% of amount in excess of 20k and so on..

Refer excel attachment for formula & in 1st sheet & example for understanding in second sheet.

Cheers!!


Click to Download

Excel VBA Macro to assign payment received to Invoices using FIFO method

Hi

Today i am sharing this fantastic template for assigning payment received to Invoices using FIFO method.

If you are not using any ERP or Accounting package then it is nightmare for accountant to manually assign payment received one by one to invoice, also there is risk of assigning payment twice or missing payments..

Just fill data in template & click on Image to run macro..

Download file by clicking this.


Cheers!!

Tags:
#Excel#VBA#MACRO#FIFO#INVOICE#PAYMENT

Windows like calculator in excel using VBA macro

While working with excel need for calculator arises for doing manual calculations..

follow this link for downloading excel with free VBA codes:

With this calculator in excel you can do basic functions & trigonometric functions too, also it has option for deleting last miss typed texts..

Download

Excel VBA Extract domain name from email address using Excel VBA Macro

Excel VBA Extract domain name from email address using Excel VBA Macro

How to Extract, follow steps as below:

Step 1 : Split email id from @ sign
Step 2: Get second portion of split result
Step 3: Now in domain name there could be more than one dots, so we will split value in step 2 using "." sign
Step 4: Store values so split in to array & remove last array
Step 5 : Remove last array value
Step 6 : Join array value using "."
Step 7: Result is your domain name

Check Code:

Sub domain_name_old()

Dim a() As String

Dim domain As String

domain = "vba@sub.domain.com" 'ActiveCell.Value

domain = Split(domain, "@")(1)

a = Split(domain, ".")

ReDim Preserve a(UBound(a) - 1)

domain = Join(a, ".")

MsgBox "Domain name is : " & domain

End Sub

Method 2 ***

Sub domain_name2()

Dim a() As String

Dim domain As String



domain = ActiveCell.Value

domain = Split(domain, "@")(1)

domain = Replace(domain, "." & Right(domain, (Len(domain) - InStrRev(domain, "."))), "")

End Sub

Employee Roster / Shift allocation template

Hi,

Preparing employee roster or allocation of shift times is very time consuming & requires accuracy so as to avoid any emergencies.

In this template you need to fill data in Main sheet in front of each employee & press Run All button & all shifts will be marked in respective sheets against each employees.

For changing shift allocated time, just change shift time in main sheet & same will be replicated in respective day sheet automatically.

Column R is for entering maximum time an employee is allowed to work, enter hrs manually in column R, column S will calculate total Hours allocated & balance hours in column T.

To Reset all data in all sheets, Just press Rest All Data button.

When you click on name of Day in column 4 you will be taken to that day sheet directly & from day sheet click on Main in Cell Q1 to come back to Main sheet..

Cheers & waiting for feedback for improvements..

Cheers!!

Download Attachment

Find text in all sheet & return Sheet Name & Header in new Excel sheet

You can use below code for find particular text in all sheets

'Run from standard module, like: Module1.

    'Find all data on all sheets!

    'Do not search the sheet the found data is copied to!

    'List a message box with all the found data addresses, as well!

    Dim ws As Worksheet, Found As Range

    Dim myText As String, FirstAddress As String

    Dim AddressStr As String, foundNum As Integer



    myText = InputBox("Enter text to find")



    If myText = "" Then Exit Sub



    For Each ws In ThisWorkbook.Worksheets

    With ws

    'Do not search sheet4!



    Set Found = .UsedRange.Find(what:=myText, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)



    If Not Found Is Nothing Then

    FirstAddress = Found.Address



    Do

    foundNum = foundNum + 1

    'AddressStr = AddressStr & .Name & "," & Found.Address & vbCrLf

    AddressStr = AddressStr & .Name & "//" & Found.Address & "//" & ws.Cells(1, Found.Column).Value & ";" & vbCrLf



    Set Found = .UsedRange.FindNext(Found)



    'Copy found data row to sheet4 Option!

    'Found.EntireRow.Copy _

    'Destination:=Worksheets("Sheet4").Range("A65536").End(xlUp).Offset(1, 0)

    Loop While Not Found Is Nothing And Found.Address <> FirstAddress And Found.Column <> Range(FirstAddress).Column

    End If



myNext:

    End With



    Next ws



    If Len(AddressStr) Then

    MsgBox "Found: """ & myText & """ " & foundNum & " times." & vbCr & _

    AddressStr, vbOKOnly, myText & " found in these cells"

   

    Set ws = Sheets.Add(After:=Sheets(Sheets.Count))

    ws.Name = "Report"

   

    Range("A1").Resize(foundNum, 1) = Application.Transpose(Split(AddressStr, ";"))

    Else:



    MsgBox "Unable to find " & myText & " in this workbook.", vbExclamation

    End If

   

    Columns("A:A").AutoFit

End Sub