Results 1 to 6 of 6

Macro to find every employee id found in column B and paste on matching row

Threaded View

walkingwill Macro to find every employee... 09-17-2013, 04:18 PM
tehneXus Re: How could I have done... 09-17-2013, 06:40 PM
jindon Re: Macro to find every... 09-17-2013, 07:27 PM
walkingwill Re: Macro to find every... 09-18-2013, 10:24 AM
jindon Re: Macro to find every... 09-18-2013, 10:50 AM
walkingwill Re: Macro to find every... 09-18-2013, 11:15 AM
  1. #1
    Registered User
    Join Date
    09-13-2013
    Location
    Houston, TX
    MS-Off Ver
    Excel 2003
    Posts
    3

    Macro to find every employee id found in column B and paste on matching row

    Hello, I'm pretty new to macroing and vba. I was wondering if you guys and gals could take a look at this macro and tell me how I could've made it better.

    Sub placeID()
    '
    'Macro created by William Johnson
    'Last edited: 9-17-13 1:39PM
    '
    
    Dim employeeID As String
    Dim counter As Integer
    Dim row As Integer
    Dim found As Boolean
    Dim search As Boolean
    
    'first row value selected
    row = 4
    found = False
    
    While found = False
        If IsEmpty(Cells.Range("B" & row).Value) = False Then
            If Len(Cells.Range("B" & row)) = 15 Then
                search = True
                'input
                employeeID = Range("B" & row).Value
                row = row + 1
                counter = 0
                While search = True
                    If IsNumeric(Cells.Range("B" & row).Value) = False Then
                        row = row + 1
                        counter = counter + 1
                    Else
                        row = row - counter
                        While counter > 1
                            'output
                            Cells.Range("E" & row).Value = employeeID
                            row = row + 1
                            counter = counter - 1
                        Wend
                        Cells.Range("E" & row).Value = employeeID
                        row = row + 2
                        search = False
                    End If
                 Wend
            Else
                row = row + 1
            End If
        Else
            found = True
            Exit Sub
        End If
    Wend
    End Sub
    What the macro is supposed to do is take every employee id found in the B column and paste it on every row that has some line item. There are about a thousand or more entries to check through so they wanted me to make this a macro. Maybe you could've found a more efficient way to do it but I'm rusty and yea....i know it looks like crap lol

    My idea was to loop through the b column with a row variable, find the employee id(they are the only one with 15 characters exactly), put it to a variable, then use another variable as a counter until it finds a number instead of text(like $968.00). Next, I move the column loop to the e column and subtract the counter variable from the row variable in order to start it at the first line item, then I set each cell's value to the employee id. Once I was finish with that, I reset the counter, go back to b column, and jump 2 rows in order to skip to the next employee id. Rinse and repeat until the main loop hits an empty cell at the very bottom of column b. The more I look at it, the more I think for the output part I should've used a do-while loop because the output loop needs to be run at least once otherwise it'll miss a line. If you +1 the counter variable, it'll only output every other employee id...which is weird. But yea, it's a mess but it got the job done. I want to get better though. I attached the .xls if you wanted to see it in action.
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 04-11-2013, 11:14 AM
  2. Macro to copy and paste rows based on "text" found in Column A
    By balston2 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-09-2013, 12:35 PM
  3. find and copy paste the value below found word
    By emina002 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-15-2011, 05:39 AM
  4. Replies: 1
    Last Post: 07-25-2011, 01:07 PM
  5. Macro to Find end of Column and paste Contents in Column 1
    By donnydorko in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-11-2009, 10:23 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1