vba find function assistance

[jiddings]

I have a spreadsheet that I need to collect data and copy it to a separate spreadsheet.
The source spreadsheet is configured as follows
Column A: Column B:
Employee: Employee clock #
Department: Department A
Reg Pay: daily hrs Sun - Sat(column B - Column H)
Department: Department B
Reg Pay: daily hrs Sun - Sat(column B - Column H)
Department: Department C
Reg Pay: daily hrs Sun - Sat(column B - Column H)
Employee: Employee clock #
Department: Department A
Reg Pay: daily hrs Sun - Sat(column B - Column H)
Department: Department B
Reg Pay: daily hrs Sun - Sat(column B - Column H)
Department: Department C
Reg Pay: daily hrs Sun - Sat(column B - Column H)

This repeats for approx. 60 employees.

As the employees are a shared resource between departments, not all employees work for each department each week and some work in only one department for a given week. Thus, other department designations will not appear for a given employee and will only appear if the employee works for that department during that week.
I've used the vba find function to locate each employee's clock #. I'm having difficulity with vba to locate Department "B" for each employee and I'm attempting to utilize the find function. Any recommendations on vba code to do this would be appreciated.

 

[Ralajer]

The error is likely occurring because the '.select' method will only work if the range's parent worksheet is the active worksheet. For that reason and also because most every thing can be done without the '.select' method, avoid using it unless you want a particular range to be selected at the end of the code.

If you are trying to copy/format individual cells in a general manner you would have to replacing the resize code and do something along the lines of the following.

    DeptRefCell.Offset(k, 1).Copy Destination:=TargetWS1.Cells(outLR, 2) '//Copy Col B to B
    TargetWS1.Cells(outLR, 2).Interior.Color = RGBCategories(j)   

    DeptRefCell.Offset(k, 2).Copy Destination:=TargetWS1.Cells(outLR, 4) '//Copy Col C to D
    TargetWS1.Cells(outLR, 4).Interior.Color = RGBCategories(j)

    DeptRefCell.Offset(k, 3).Copy Destination:=TargetWS1.Cells(outLR, 7) '//Copy Col D to G 
    TargetWS1.Cells(outLR, 7).Interior.Color = RGBCategories(j)
    ...
    ...
    ...

 

[jiddings]

I just realized I had not previously posted the structure of the target worksheets.
Here is the structure. It's the same for the reg pay hrs also.

Excel 2007
	A	B	C	D	E	F	G	H	I	J
1	Holiday/Breavement/Jury Duty/Floating Holiday/Vacation
2	2014		Holiday Pay
3			Bereavement
4			Jury Duty
5			Floating Holiday
6			Vacation
7			Bonus Vacation Day
8	Go to Holiday Summary			Status	RPT	RPT	RPT	RPT	RPT	RPT
9			Do not chg this line	Clock #	3051	2097	3503	3980	2751	1348
10	Week Ending	Day of Week	TOTAL Hours	% of Total	XXXXXX	XXXXX	WWWWW	RRRRR	YYYYYY	ZZZZZZZ
11	29-Dec	Sun	0.00	0%	0	0	0	0	0	0
12	30-Dec	Mon	0.00	0%	0	0	0	0	0	0
13	31-Dec	Tue	0.00	0%	0	0	0	0	0	0
14	1-Jan	Wed	0.00	0%	0	0	0	0	0	0
15	2-Jan	Thu	0.00	0%	0	0	0	0	0	0
16	3-Jan	Fri	0.00	0%	0	0	0	0	0	0
17	4-Jan	Sat	0.00	0%	0	0	0	0	0	0
18		Total Week	0.00		0	0	0	0	0	0
19		Year To Date	0.00		0.00	0.00	0.00	0.00	0.00	0.00
20
21	5-Jan	Sun	0.00	0%	0	0	0	0	0	0
22	6-Jan	Mon	0.00	0%	0	0	0	0	0	0
23	7-Jan	Tue	0.00	0%	0	0	0	0	0	0
24	8-Jan	Wed	0.00	0%	0	0	0	0	0	0
25	9-Jan	Thu	0.00	0%	0	0	0	0	0	0
26	10-Jan	Fri	0.00	0%	0	0	0	0	0	0
27	11-Jan	Sat	0.00	0%	0	0	0	0	0	0
28		Total Week	0.00		0	0	0	0	0	0
29		Year To Date	0.00		0	0	0	0	0	0
30
31	12-Jan	Sun	0.00	0%	0	0	0	0	0	0
32	13-Jan	Mon	0.00	0%	0	0	0	0	0	0
Holidays - Vacations

 

[jiddings]

Considering the most recently posted target worksheet structure, I'm not understanding how your suggestion(s) would work wen each days hrs. could be different categories. I think your suggestion would result changing the cells for the whole week.
My thought would be a for / next loop to copy from source worksheet row to each days in the target worksheet column. Would that be something that would work?

 

[Ralajer]

You're right it won't work and some of the other previous code won't work for this target worksheet.

You're correct an additional for loop would be required to loop through the days. The loop would have to contain an if statement copy/formatting when a non-zero hour is detected. This assume that only one category of "Holiday" can be used on a given day.

In addition the output reference would have to be adjusted, which also means the current loops would probably have to be changed too. The simplest part of that would be determining the output column for a given "Clock#" assuming they all exist on the output sheet. Finding the correct week on the output would be fairly simple assuming it exists on the output sheet.

Let me know if my assumptions are correct.

 

[jiddings]

Here's a small sub I was thinking could possibly be applied.

Sub xxx()
For k = 0 To 6
With ActiveSheet
Cells(1, (3 + k)).Copy
If Cells(1, (3 + k)) = 0 Then
'do nothing
ElseIf Cells((1 + k), 1) <> 0 Then
'myvalue = Application.WorksheetFunction.Sum((Cells(1, (3 + k))), (Cells((1 + k), 1)))
'Add cell comment when value previously exists and change cell color
        existval = Cells((1 + k), 1).Value
        addval = Cells(1, (3 + k)).Value
        Cells((1 + k), 1) = Application.WorksheetFunction.Sum((Cells(1, (3 + k))), (Cells((1 + k), 1)))
        Cells((1 + k), 1).ClearComments
        Cells((1 + k), 1).AddComment.Text Text:=Format(Str(Now), "mmm-dd-yy hh:mm:ss") & vbCrLf & "Two Values:" & vbCrLf _
        & "Existing value = " & Str(existval) & vbCrLf & "Added value = " & Str(addval)
        Cells((1 + k), 1).Interior.Color = RGB(86, 220, 198) 'changed value color
        Cells((1 + k), 1).Font.Color = vbWhite 'font color
        Cells((1 + k), 1).Font.Bold = True 'font color bold
ElseIf Cells((1 + k), 1) < 1 Then
Cells((1 + k), 1).PasteSpecial xlPasteValues
End If

'/// need code for changing cell color for each of holidays
'If Cells((1 + k), 1).Value <> 0 Then
''Cells((1 + k), 1).Interior.Color = RGB(255, 0, 255) 'holiday color
''Cells((1 + k), 1).Interior.Color = RGB(255, 153, 204) 'floating holiday color
''Cells((1 + k), 1).Interior.Color = RGB(51, 102, 255) 'vacation color
'Cells((1 + k), 1).Interior.Color = RGB(153, 102, 255) 'bonus vac day color
'Cells((1 + k), 1).Font.Color = vbWhite 'font color
'Cells((1 + k), 1).Font.Bold = True 'font color
'End If
End With
Next k
Range("a1").Select
Application.CutCopyMode = False
End Sub

 

[jiddings]

Your assumptions are correct. My thought is to have the code "look" at the cell that the cell it is about to post a value into, add the two values and a comment with a unusual cell color to "flag" it. Otherwise, set the color for vacation, holiday, etc. I think the previous For / Next VBA might be a good start?