Need Help writing a macro

[Sam Commar]

I would really appreciate help with creating a macro.

I have source data which looks like this. So the source date is always 5
columns.
Deltek 10 1 88 E 880
Deltek 13 1 50.5 E 505
Deltek 13 5 16.5 E 247.47
Deltek 14 1 96.5 E 880
Deltek 35 1 8 E 110
Deltek 5 1 36 E 176
Deltek 62 1 9 E 110
Deltek 68 1 13 E 130
Deltek 9 1 32 E 320


This data is extracted every 15 days. The number of rows vary every 15 days
but the columns are constant.

I want to create a macro which will translate the data with some headers and
some if then statements and put it on another sheet.

The results would be in 10 columns with the info as as follows:

Header Info
Col1 Col2 Col3 Col4
Col5 Col6 Col 7 COl8
Col9 Col10
CO CODE BATCH ID FILE # REG HOURS O/T HOURS REG
EARNINGS HOURS 3 CODE HOURS 3 AMOUNT EARNINGS 3 CODE EARNINGS 3 AMOUNT

Detail info would be populated as below and the if then statements would
refrence the source data.
Col1 Col2 Col3
Col4 Col5 Col6
EP7(Constant) BATCH01(Constatnt) =IF(B2<51,1000+B2,B2)
=IF(C2=1,D2," ") =IF(C2=5,D2," ") Blank

Col7
=IF(OR(C2={2,3,4,10}),C2," ")

Col8 Col9
Col10
=IF(OR(C2={2,3,4,10}),D2," ")
=IF(OR(C2={"8c",8,"8b","8d",60,"7W","7T"}),C2," ")
=IF(OR(C2={"8c",8,"8b","8d",60,"7W","7T"}),F2," ")



So the data would look like this based on the application of the above
mentioned items and if then statements.

CO CODE BATCH
ID
FILE # REG HOURS O/T HOURS REG EARNINGS HOURS 3 CODE HOURS 3
AMOUNT EARNINGS 3 CODE EARNINGS 3AMOUNT
EP7 Batch01 1010 88
EP7 Batch01 1013 50.5
EP7 Batch01 1013 16.5
EP7 Batch01 1014 96.5
EP7 Batch01 1035 8
EP7 Batch01 1005 36
EP7 Batch01 62 9
EP7 Batch01 68 13
EP7 Batch01 1009 32

 

[Bob Phillips]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cell As Range
Dim sh As Worksheet

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow 'iLastRow to 1 Step -1

If Not IsError(Application.Match(.Cells(i, "C").Value, _
Array("8c", 8, "8b", "8d", 60, "7W", "7T"), 0)) Then
.Cells(i, "I").Value = .Cells(i, "F").Value
Else
.Cells(i, "I").Value = ""
End If
If Not IsError(Application.Match(.Cells(i, "C").Value, _
Array("8c", 8, "8b", "8d", 60, "7W", "7T"), 0)) Then
.Cells(i, "I").Value = .Cells(i, "C").Value
Else
.Cells(i, "I").Value = ""
End If
If .Cells(i, "C").Value = 2 Or _
.Cells(i, "C").Value = 3 Or _
.Cells(i, "C").Value = 4 Or _
.Cells(i, "C").Value = 10 Then
.Cells(i, "H").Value = .Cells(i, "D").Value
Else
.Cells(i, "H").Value = ""
End If
If .Cells(i, "C").Value = 2 Or _
.Cells(i, "C").Value = 3 Or _
.Cells(i, "C").Value = 4 Or _
.Cells(i, "C").Value = 10 Then
.Cells(i, "G").Value = .Cells(i, "C").Value
Else
.Cells(i, "G").Value = ""
End If
.Cells(i, "F").Value = ""
If .Cells(i, "C").Value = 5 Then
.Cells(i, "E").Value = .Cells(i, "D").Value
Else
.Cells(i, "E").Value = ""
End If
If .Cells(i, "C").Value <> 1 Then
.Cells(i, "D").Value = ""
End If
If .Cells(i, "B").Value < 51 Then
.Cells(i, "C").Value = Cells(i, "B").Value + 1000
Else
.Cells(i, "C").Value = Cells(i, "B").Value
End If
.Cells(i, "B").Value = "BATCH01"
.Cells(i, "A").Value = "EP7"

Next i

.Rows(1).Insert
.Range("A1").Value = "CO CODE"
.Range("B1").Value = "BATCH ID"
.Range("C1").Value = "FILE #"
.Range("D1").Value = "REG HOURS"
.Range("E1").Value = "O/T HOURS"
.Range("F1").Value = "REG EARNINGS"
.Range("G1").Value = "HOURS 3 CODE"
.Range("H1").Value = "HOURS 3 AMOUNT"
.Range("I1").Value = "EARNINGS 3 CODE"
.Range("J1").Value = "EARNING 3 AMOUNT"

End With

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Sam Commar]

Bob

Cant thank you enough. Thanks a BILLION

You saved me here.