Dictionary to Match Copy and Paste: won't until after certain row?

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]


gGusJ.png



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.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Forum statistics

Threads
1,223,276
Messages
6,171,140
Members
452,381
Latest member
Nova88

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