vendredi 31 mars 2017

Iterate through populated rows, Match based on criteria, add value to specific cell in another sheet in Excel using VBA

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