Consulting

Results 1 to 2 of 2

Thread: Need VBA help duplicating certain rows and manipulating data on these rows

  1. #1

    Need VBA help duplicating certain rows and manipulating data on these rows

    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.

  2. #2
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •