loop code_ go through 2 date ranges, list of employees and place in value

[kharding15]

Hi the main goal I am trying to do is almost what I did before with a loop but the value is randomly placed everywhere in one spread sheet and not down one column, so I need some help. Also I am seeing that some information is not being grabbed in my loop. Here is my code with some notes inside, along with an attached example.

Private Sub CommandButton1_Click()
On Error Resume Next
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Sheets("Summary")
Set ws2 = Sheets("Week Summary")

Dim vardate1(283) As Variant 'compare date range list #1-in worksheet "Summary"
Dim vardate2(283) As Variant 'compare date range list #2-in worksheet "Week Summary"

Dim sinvalue As Double 'random values in one sheet that needs to another sheet

Dim varEngineer As Variant 'compare list 1 of employees-in worksheet "Summary"
Dim varEngineer2 As Variant 'compare list 2 of employees-in worksheet "Week Summary"


Dim x As Integer
Dim i As Integer
Dim j As Integer
Dim g As Integer
Dim b As Integer
  


'ignore this:
'L = 5
'Do While L < 300
        'cells(4, L).Value = Cells(5, L).Value
'L = L + 1
'Loop



Do While j < 51
g = 5
i = 0
                    Do While i < 283    'goal is to look through these 2 date ranges
                        vardate1(i) = ws1.Cells(2, g).Value
                            Debug.Print vardate1(i) & "    Vardate 1"
                        vardate2(i) = ws2.Cells(4, g).Value
                            Debug.Print vardate2(i) & "       vardate 2" 'it will not grab this value, only the message -PLEASE HELP
                        g = g + 1
                        i = i + 1
                    Loop

               If vardate1(i) <= vardate2(i) Then
                        i = 0
                        g = 5
                        x = 6
                        varEngineer2 = ws2.Cells(1, g).Value
                        varEngineer = ws1.Cells(j, 4).Value
                        sinvalue = ws1.Cells(j, g) ' it will not grab the value-PLEASE HELP
                        Debug.Print varEngineer
                            
                            Do While i < 283
                                     If vardate1(i) = vardate2(i) Then
                                        Cells(x, g).Value = sinvalue
                                    End If
                            x = x + 1
                            i = i + 1
                            g = g + 1
                            Loop
                End If
j = j + 1
Loop
    




End Sub

Let me know if something doesn't make sense. Thank you!

[quekbc]

Without going through the macro in detail, it seems you want to convert the table in Summary into a similar table in Week Summary? Am I right?

Do you need VBA to do this? I think it can be done without.

[kharding15]

The dates will change job to job in the main summary. And the Week Summary format will always need to start on a Monday. If there is a formula for the values (hours in this case) to move with dates please let me know. For example if this started on 7/28/15, Tuesday, I want the last column to be Monday and move the hours to match the dates and employees.
I do not have to go through VBA, so any suggestions would be very much appreciated.

[kharding15]

Sorry, I have been working crazy hours please excuse my grammar.

[kharding15]

Here is the beginning cell formula for to move the dates in a cell:

=IF(Summary!E1="Monday",Summary!E2,IF(Summary!E1="Tuesday",WORKDAY(Summary!E2,-1),IF(Summary!E1="Wednesday",WORKDAY(Summary!E2,-2),IF(Summary!E1="Thursday",WORKDAY(Summary!E2,-3),IF(Summary!E1="Friday",WORKDAY(Summary!E2,-4),"")))))

This is just for the first column. I will have to do this for the other 4 to make the first week dates correct and then make the other weeks workday +1. Though, my issue is the hours being placed in the correct location to the appropriate employee.

[quekbc]

In that case, you can use this formula in E6 and subsequently copied to the rest of the tables, E6:I29, K6:O29, Q6:U29

=INDEX(Summary!$E$28:$KA$51,MATCH($D6,Summary!$D$28:$D$51,0),MATCH(E$4,Summary!$E$2:$KA$2,0))

See attached for a populated example.

[kharding15]

This is awesome! Thank you!!!

[quekbc]

Excellent. If this resolves your initial question, can you kindly mark this thread as solved? You can do that by clicking on Thread Tools above your initial post and click on Mark This Thread as Solved.

[kharding15]

Ah, so that's where is was. Thank you, again.