VBA (probably) to separate data based on occupied cells

[Excellingconfusion]

Hello,

I am trying to create an Excel file that will scrub the output of a program I use (excel file output) into the format of the input I need for a different program. My issue arises when the output file will put the minutes of an employee into various columns of the same row based on regular, overtime, or doubletime; while the input to the other program needs them to be separate rows. For the output, Pay Type is 1 for regular hours, 2 for overtime, and 3 for doubletime. Paygroup is just a placeholder column. The output format requires that for each different pay type, there need to be a new line. E.g. the input file has employee 415 on cost code 91.105 for 90 minutes regular time and then 180 minutes overtime in that same column. The output will need to have employee 415 listed twice with this cost code to break apart those different pay types and then have the minutes from the appropriate column added. I also need to remove any letters off the end of the cost codes in the output which I was using this function to do. =IF(Input!C4<>"",IF(ISERR(RIGHT(Input!C4,1)*1),LEFT(Input!C4,LEN(Input!C4)-1),Input!C4),"")

Input Format:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Employee #[/TD]
[TD]Cost Code[/TD]
[TD]Cost Code Type[/TD]
[TD]UOM[/TD]
[TD]Regular Minutes[/TD]
[TD]OT Minutes[/TD]
[TD]Doubletime Minutes[/TD]
[TD]Bid Area[/TD]
[TD]Bid Typical Area[/TD]
[/TR]
[TR]
[TD]1/18/19[/TD]
[TD]376[/TD]
[TD]91.108[/TD]
[TD]c[/TD]
[TD]LF[/TD]
[TD]120[/TD]
[TD][/TD]
[TD][/TD]
[TD]B1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/18/19[/TD]
[TD]376[/TD]
[TD]91.116A[/TD]
[TD]c[/TD]
[TD]sf[/TD]
[TD]360[/TD]
[TD]240[/TD]
[TD]90[/TD]
[TD]B1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/18/19[/TD]
[TD]406[/TD]
[TD]91.171B[/TD]
[TD]c[/TD]
[TD]LF[/TD]
[TD]480[/TD]
[TD]60[/TD]
[TD][/TD]
[TD]B1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Ideal Output:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]# Employee[/TD]
[TD]Cost Code[/TD]
[TD]Pay Type[/TD]
[TD]Paygroup (Place Holder)[/TD]
[TD]Hours[/TD]
[/TR]
[TR]
[TD]376[/TD]
[TD]91.108[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]376[/TD]
[TD]91.116[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]376[/TD]
[TD]91.116[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]376[/TD]
[TD]91.116[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]1.5[/TD]
[/TR]
[TR]
[TD]406[/TD]
[TD]91.171[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]406[/TD]
[TD]91.171[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


Please Help!!!!

 

[rpaulson]

try this on a copy of you file.

create a worksheet name Results

then run this with your main sheet the active sheet

Sub Do_it()

With Worksheets("results")

wr = 2

For r = 2 To Cells(Rows.Count, "A").End(xlUp).Row

For c = 6 To 8 'llop thru pay types
If Cells(r, c) <> "" Then
.Cells(wr, "A") = Cells(r, "B") 'ID
.Cells(wr, "B") = Left(Cells(r, "C"), 6) 'Cost Code
.Cells(wr, "C") = c - 5 'pay type
.Cells(wr, "E") = Cells(r, c) / 60 'Hours

wr = wr + 1
End If
Next c

Next r
End With

End Sub

hth,

Ross

 

[Excellingconfusion]

I added it and encountered a runtime at line 13 when executed: Type Mismatch

This was this line:

" .Cells(wr, "E") = Cells(r, c) / 60 'Hours "

Any idea?

Thanks for your help Ross!

-Ryan

 

[Excellingconfusion]

Scratch that, I forgot to switch to only run this on that workbook, rather than all open workbooks. It worked!!!!

You are a gentleman and a scholar. Very appreciated.