PDA

View Full Version : Need VBA help duplicating certain rows and manipulating data on these rows



three60grab
05-29-2014, 10:04 AM
I'm fairly new to VBA. I'm about 3/5 done with my Excel VBA Programming for Dummies book and it has already helped my life at work a lot. Here is my situation.

I've got about 10,000 lines. The important info on the line is account number, department, amount and sales type. I have to look in either the department or account column (depending on the process I'm doing) for each row. Let's say the department is 500, I need to take the amount on this line and allocate it to 2 and some times 3 sales types. For example:

All lines with department 500 should be allocated to sales type A at 40% and sales type B at 60%. So if the amount on this specific line is 100, then I need to copy this line, insert it below, change the sales types for these two lines to A and B (regardless if the original sales type was C) with 40% of the original amount going to the line with sales type A and 60% going to the line with sales type B.

I also have to run this same process on the account column for a specific set of accounts. These two processes won't overlap.

This is what I started with and then just got confused...also, I know there has to be an easier way to do this.

Sub AllocationTemp()
Dim SearchRow As Integer
Dim Bucket As Variant
Dim bucketprod1 As Variant
Dim bucketprod2 As Variant

SearchRow = 5

While Len(Range("A" & CStr(SearchRow)).Value) > 0
If Range("B" & CStr(SearchRow)).Value = 500 Then
Range("B" & CStr(SearchRow)).Value = Bucket
Rows(CStr(SearchRow) & ":" & CStr(SearchRow)).Select
Selection.Copy
Selection.Insert Shift:=xlDown
bucketprod1 = Bucket * 0.4
bucketprod2 = Bucket * 0.6


End Sub



Any help is greatly appreciated.

jolivanes
05-29-2014, 11:36 PM
and then just got confused
You also? I am too!
Maybe put code tags around your code first. Highlight your code and click on the number (#) sign.
A before and after sanitized workbook would help.