View Full Version : VBA Script to get Commision Amt

03-22-2012, 06:46 AM

I need some help creating a VBA Script in Excel 2010. Here is the situation:

File 1 (Sales): Has the Order ID in Column B.

File 2 (Commision_Amt): Has the Order ID in Column C,
Rec_Type in Column D,
Commission_Amt in Column E.

What I want to do is for each Order ID (can have multiple rows with
same Order ID, but this is the driver file) in Sales, find the corresponding
Order ID in Commission_Amt and Rec_type = "Commission" in File 2, then
move the Commission_amt to Column C in Sales. When the Sales File
Order ID has 2 rows with the same Order ID, the Commsiion_Amt would be
the same for both rows.
I need to create the script to run when I bring in the files to Excel. Thanx in advance for your assistance in this matter.


03-22-2012, 10:01 AM

03-24-2012, 07:39 PM
I had thought about using this function, but have never seen a multiple condition VLOOKUP, therefore I do think is is possible. This is why I am trying to figure out how to do a VBA script to do it with.
Thanx in advance.


03-27-2012, 01:35 PM
post a sample workbook and i can help you out

03-27-2012, 02:14 PM
Awesome. Here are the details:

Workbook Name: Combine_File
Worksheet Name: A. Sales_File (Driver WS)
Order ID (Column B)
Comm_Amt (Column M, need to populate)
B. Commission_Information (Reference WS)
Order ID (Column B)
Payment Detail (Column F, must EQ "Commission")
Amount (Column G, move to Col M of Sales_File WS)

Then I need to save Sales_Files as a Tab delimited txt file. Thanx in
advance for your assistance in this matter.


03-27-2012, 02:42 PM
Sub getCommission()
Dim cell As Range, cell2 As Range
Dim mysheet As Worksheet
'Insert commission in Column M
For Each cell In Range("H2:H" & Range("H65536").End(xlUp).Row)
For Each cell2 In Sheets(2).Range("C5:C" & Sheets(2).Range("C65536").End(xlUp).Row)

If cell.Value = cell2.Value And InStr(cell2.Offset(0, 3).Text, "Commission") Then

cell.Offset(0, 5).Value = cell2.Offset(0, 4).Value
End If
Next cell2
Next cell
'Save as tab delimited text file
ChDir "C:\TEST"
ActiveWorkbook.SaveAs Filename:="C:\TEST\MYFILE.txt", FileFormat:=xlText, _
End Sub

03-28-2012, 06:40 AM
I used this VBA script and the results were not what I expected or correct. What I did was have both worksheets sorted in Order ID order from low to high. The attached file is the result. Can you let me know what I need to change.
Thanx in advance.

03-29-2012, 09:37 AM
what is it not doing that you want? it looks like it pulled the correct commission amounts to me...and it saves it as a tab delimited text file but you may have to change the directory it saves to?

03-29-2012, 11:06 AM
If you copy an Order ID from the Sales WS and then do a find on the Commission WS and look for the row which has Commission for that Order ID and the amount value is that is brought back to Sales is not correct.

IE: Order ID:
002-1810088-0066640has a Commission rate of (2.17) with 2 rows,
not blank for the first row and (0.90) for the 2nd row.