vba update cell values

A

alowe

New Member
Joined
Dec 6, 2013
Messages
3
I have 2 sheets, first is a daily lineup, 2nd is a knowledge tracker. I am trying to get, through the use of a button, for vba to look at who did what for the day, and then insert that date into the knowledge tracker. Not everyone knows everything so i am running into some issues. In the following example, the vba should look at sheet 1, see that person 3 did job 3, and insert the date into sheet 2 person 3 job 3. The idea is that it would allow me to be sure everyone's knowledge stays fresh. Please help!

[TABLE="width: 632"]
<tbody>[TR]
[TD]Sheet 1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sheet 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]Job 1[/TD]
[TD]Job 2[/TD]
[TD]Job 3[/TD]
[TD]Job 4[/TD]
[/TR]
[TR]
[TD]Job 1[/TD]
[TD]Person 1[/TD]
[TD][/TD]
[TD]Person 1[/TD]
[TD="align: right"]1/1/2000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job 2[/TD]
[TD]Person 2[/TD]
[TD][/TD]
[TD]Person 2[/TD]
[TD="align: right"]1/1/2000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job 3
[/TD]
[TD]Person 3[/TD]
[TD][/TD]
[TD]Person 3[/TD]
[TD][/TD]
[TD="align: right"]1/1/2000[/TD]
[TD="align: right"]1/1/2000[/TD]
[TD="align: right"]1/1/2000[/TD]
[/TR]
[TR]
[TD]Job 4[/TD]
[TD]Person 4[/TD]
[TD][/TD]
[TD]Person 4[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1/1/2000[/TD]
[TD="align: right"]1/1/2000[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
Hi alowe,

Iv'e written a small macro which may be of use...


Sheet1

*AB
*
**
Job 1Person 4
Job 2Person 1
Job 3Person 2
Job 4Person 3

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 73px;"><col style="width: 69px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="align: right"]10/12/2013[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

You will see I've altered the order of the person so you can see the effect...

Before macro is run...

Sheet2

*ABCDE
Person 1****
Person 2****
Person 3****
Person 4****

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 111px;"><col style="width: 79px;"><col style="width: 79px;"><col style="width: 79px;"><col style="width: 79px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="align: left"]Name[/TD]
[TD="align: left"]Job 1[/TD]
[TD="align: left"]Job 2[/TD]
[TD="align: left"]Job 3[/TD]
[TD="align: left"]Job 4[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4


...after macro is run.

Sheet2

*ABCDE
Person 1***
Person 2***
Person 3***
Person 4***

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 111px;"><col style="width: 79px;"><col style="width: 79px;"><col style="width: 79px;"><col style="width: 79px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="align: left"]Name[/TD]
[TD="align: left"]Job 1[/TD]
[TD="align: left"]Job 2[/TD]
[TD="align: left"]Job 3[/TD]
[TD="align: left"]Job 4[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="align: center"]10/12/2013[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

[TD="align: center"]10/12/2013[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

[TD="align: center"]10/12/2013[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]

[TD="align: center"]10/12/2013[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4



Apply the following code to a button and you are good to go...

Code: 
Sub testJob()
'http://www.mrexcel.com/forum/excel-questions/743517-visual-basic-applications-update-cell-values.html
'Provided for alowe 6/12/13 by ArthriticPanda

Dim myVal As Variant

'select Sheet2
Sheets("Sheet2").Select

'set the variable to cycle through the Job number columns on sheet 2
k = 1

'create 2 loops for rows and columns
For j = 2 To 5
    For i = 2 To 5
        If Cells(i, j) = "" Then 'test to see if cell already has a date and if not, use VLOOKUP to find the person against the job
            myVal = WorksheetFunction.VLookup(Cells(1, j), Sheets(1).Range("A3:B6"), 2, 0)
                If Cells(i, 1) = myVal Then Cells(i, j).Value = Sheets(1).Range("A1").Value 'test VLOOKUP value against person in row and if they match enter the date on sheet 1
        End If
    Next i
        k = k + 1 'increment counter
Next j

End Sub
 
Last edited:
Upvote 0
It says subscript out of range. Not sure what that means. Is it because the lineup looks like this:

Date
Job 1 Person 2

Job 2 Person 4

Job 3 Person 5

Job 4 Person 1

The date is in cell C9, Job 1 is A11, Person 2 is in C11, Job 2 is in C13, etc.
 
Upvote 0
Thanks, the last job title is at A31, so the last person would be at C31 for the lineup. On sheet 2 for the knowledge tracker person 1 is at A3, Job 1 is titled at B1, Job 8 is titled at J1.

I forgot to mention that on the lineup job 7 and 8 feed to job 7 on the knowledge tracker, and job 9,10,and 11 feed to job 8 on the knowledge tracker. (there are multiple people needed for the spot each day so it would be like Job7 A, Job7 B, J7 C. I hope I explained that right.
 
Upvote 0
I've had to re-adjust your table of data on sheet1 as vlookup is querky with how it reads the data. Is it ok to remove the spaces between each row of data on sheet1? It will look like this:

Excel Workbook
AB
1
2
3
4
5
6
7
8
910/12/2013
10TitleName
11Job 1Person 1
12Job 2Person 2
13Job 3Person 3
14Job 4Person 4
15Job 5Person 5
16Job 6Person 6
17Job 7APerson 7
18Job 7BPerson 8
19Job 8APerson 9
20Job 8BPerson 10
21Job 8CPerson 11
Sheet1



If so, then the adjusted script will work. Let me know if this will be ok.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Excel VBA Searching Unique Variables to Match against Exported Schedule Files to Determine Date
Replies
1
Views
561
sijpie
sijpie
J
  • Question Question
VBA help for timesheet
Replies
9
Views
517
jmccall93
J
A
  • Question Question
Automate Attendance Marking Based on Daily Schedule in Excel
Replies
1
Views
389
Peter_SSs
Peter_SSs
K
  • Solved
Code to search job id from multiple sources and update main spreadsheet.
2
Replies
10
Views
774
mumps
mumps
WERNER SLABBERT
  • Question Question
Run-time error 70 after code changed
Replies
3
Views
213
RoryA
RoryA

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top