Rymare
New Member
- Joined
- Apr 20, 2018
- Messages
- 37
Ok so 1) this is sort of a cross post--I posted something similar in stackoverflow (https://stackoverflow.com/questions...tch-and-paste-unknown-error/50276484#50276484), but I have a new problem with the same code so I thought I'd ask the new question here. 2) This is also a sort of attempt to answer my own earlier questions posted here: https://www.mrexcel.com/forum/excel...dex-match-using-vba-multi-sheet-workbook.html , but it's not really the answer to that question because I've decided to eliminate parts of the initial requirement.
The gist of it is I want this to happen (the sheet with no red writing is a given inspectors sheet):
Inspectors have tasks in their given sheets, compare those unique tasks numbers with the task numbers in the master sheet and paste the date the inspector finished the task from the given inspectors sheet to the master sheet [see image]
Here's my code
Now for some unknown reason--it won't start matching and appropriately pasting in col. R until AFTER row 36. I have no idea why it's doing this. I know for a fact that some of the tasks above row 36 are assigned to the inspector I'm testing this code with, and that's he completed some of them--there should be dates pasted! I have no clue what I did wrong. The values are all formatted correctly, no formulas, no duplicates, no wonky accidental spaces after the end of the task number. For all intents and purposes it should work but doesn't.
I've been working on this excel workbook forever(~1 month), I am at the last step--if anyone has any help please, I beg you, let me know.
The gist of it is I want this to happen (the sheet with no red writing is a given inspectors sheet):
Inspectors have tasks in their given sheets, compare those unique tasks numbers with the task numbers in the master sheet and paste the date the inspector finished the task from the given inspectors sheet to the master sheet [see image]
Here's my code
Code:
Sub dates()
Application.ScreenUpdating = False
Dim AVals As Object: Set AVals = CreateObject("scripting.dictionary")
Dim i As Long, j As Long, lastRow1 As Long, lastRow2 As Long
Dim sh_insp, sh_2018 As Worksheet
Dim MyName As String
Set sh_insp = ActiveSheet
Set sh_2018 = Sheets("2018")
With sh_insp
lastRow1 = .Range("A:A").Rows.Count 'last row in spreadsheet
lastRow1 = .Cells(lastRow1, 7).End(xlUp).Row 'last used row in column G
'load the AVal dict
For j = 18 To lastRow1
MyName = .Cells(j, 7).Value
If Len(MyName) > 0 And Len(.Cells(j, 18)) > 0 Then AVals.Add MyName, .Cells(j, 18).Value
Next j
End With
With sh_2018
lastRow2 = .Range("A:A").Rows.Count
lastRow2 = .Cells(lastRow2, 7).End(xlUp).Row 'last used row in column G
For i = 18 To lastRow2
MyName = .Cells(i, 7).Value
If AVals.Exists(MyName) Then
.Cells(i, 18).Value = AVals.Item(MyName)
End If
Next i
End With
Application.ScreenUpdating = True
End Sub
Now for some unknown reason--it won't start matching and appropriately pasting in col. R until AFTER row 36. I have no idea why it's doing this. I know for a fact that some of the tasks above row 36 are assigned to the inspector I'm testing this code with, and that's he completed some of them--there should be dates pasted! I have no clue what I did wrong. The values are all formatted correctly, no formulas, no duplicates, no wonky accidental spaces after the end of the task number. For all intents and purposes it should work but doesn't.
I've been working on this excel workbook forever(~1 month), I am at the last step--if anyone has any help please, I beg you, let me know.