I am trying to select a value in column hours in tab Time and put it in the appropriate column in tab Month. I would like to iterate through every row in sheet Time and add all of the Data to tab Month.
For every line in Tab TIME add the Hours to the appropriate engagement in Tab Month based on Engagement Number, Engagement Phase, Staff Level. I do this manually every month. I create a copy of last months tab and add on to that each month. Most columns have cells with something like this in them: =1+3+6+4+14+5+2+5 which are the hours that have been worked on that engagement. For cells with something in them i would just like to add on to what is there. For cells with nothing in them i would like to make the new value: = 1 if the value was 1. I want to automate this as it takes up a few days every first of the month, This one just so happens to be on a weekend so guess what I'll be doing. :)
This is where I am at.
Sub Recon()
'Macro to add time to reconciliation report
'Declare variables
Dim Last_Row_TIME As Double
Dim Last_Row_MONTH As Double
Dim wb As Workbook
Dim ws As Worksheet
Dim rw As Range
Dim wstime As
Set wb = ActiveWorkbook
Set wstime = Sheets("TIME")
Set wsmonth = Sheets("MONTH")
wstime.Select
'Find the last non-blank cell in column A(1)
Last_Row_wstime = wstime.Cells(Rows.Count, 1).End(xlUp).Row
'Find the first blank cell in column A
First_Empty_Row_wstime = Last_Row_wstime + 1
I_Col = 1
For i = 1 To Last_Row_wstime
For Each rw In wstime.Rows
If wstime.Cells(rw.Row,1).Value = wsmonth.Cells(rw.Row, 3).Value
And
End Sub
I am stuck at the if statement and not sure how to make this work.
IF
TIME.Eng. No. (Column A) = Month.Eng. No (Column B)
AND
TIME.Eng. Phase (Column C) = Month.Eng.Phase (Column C)
AND
TIME.Staff Level (Column M) = PARTNER or MANAGING DIRECTOR
THEN
Add Value TIME.Hours (Column Y) to Month.Partner/MD (Column I)
'If blank then "=TIME.Hours) elseif add to the previous value "previous addition statement +(hours)"
ELSEIF
TIME.Eng. No. (Column A) = Month.Eng. No (Column B)
AND
TIME.Eng. Phase (Column C) = Month.Eng.Phase (Column C)
AND
TIME.Staff Level (Column M) = SR. MANAGER/DIRECTOR
THEN
Add Value TIME.Hours (Column Y) to Month.Director (Column J)
ELSEIF
TIME.Eng. No. (Column A) = Month.Eng. No (Column B)
AND
TIME.Eng. Phase (Column C) = Month.Eng.Phase (Column C)
AND
TIME.Staff Level (Column M) = MANAGER
THEN
Add Value TIME.Hours (Column Y) to Month.Manager (Column K)
ELSEIF
TIME.Eng. No. (Column A) = Month.Eng. No (Column B)
AND
TIME.Eng. Phase (Column C) = Month.Eng.Phase (Column C)
AND
TIME.Staff Level (Column M) = SENIOR ASSOCIATE
THEN
Add Value TIME.Hours (Column Y) to Month.Sr.Assoc (Column L)
ELSEIF
TIME.Eng. No. (Column A) = Month.Eng. No (Column B)
AND
TIME.Eng. Phase (Column C) = Month.Eng.Phase (Column C)
AND
TIME.Staff Level (Column M) = ASSOCIATE
THEN
Add Value TIME.Hours (Column Y) to Month.Associate (Column M)
ELSEIF
'Create a new line after the biggest primary key, located in Month.Primary Key (Column A)
TIME.Eng. No. (Column A) = Month.Eng. No (Column B)
AND
TIME.Eng. Phase (Column C) = Month.Eng.Phase (Column C)
AND
TIME.Eng. Description (Column B) = Month.Project Name (Column D)
End If
Aucun commentaire:
Enregistrer un commentaire