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