PDA

View Full Version : VBA Script to get Commision Amt



Ballj_35
03-22-2012, 06:46 AM
Hello,

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.

Jerry

CatDaddy
03-22-2012, 10:01 AM
vlookup

Ballj_35
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.

Jerry

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

Ballj_35
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.

Jerry

CatDaddy
03-27-2012, 02:42 PM
Sub getCommission()
Dim cell As Range, cell2 As Range
Dim mysheet As Worksheet
ActiveWorkbook.Sheets(1).Activate
'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
Sheets("Sales_File").Select
Sheets("Sales_File").Copy
ChDir "C:\TEST"
ActiveWorkbook.SaveAs Filename:="C:\TEST\MYFILE.txt", FileFormat:=xlText, _
CreateBackup:=False
ActiveWindow.Close
End Sub

Ballj_35
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.

CatDaddy
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?

Ballj_35
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.