Scripting Dictionary and Next Without For Error

[reberryjr]

I'm trying to leverage a scripting dictionary to copy a value from 1 worksheet (mD) to another worksheet (mI); where both worksheets have common values. Simple example: If on worksheet mI, the value in column A and the value in column L = the value in column A and the value in column H, then update column Q on the mI worksheet with the value from column J on the mD worksheet. I was attempting the scripting dictionary based off of some advice I've received previously on this site, but I'm getting a Next without For error. I'm not sure why, as the code I was given previously is built almost exactly the same way.

Sub IQR_Calcs()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim m As Workbook
Dim mI As Worksheet, mM As Worksheet, mD As Worksheet, mO As Worksheet, mP As Worksheet, mV As Worksheet
Dim mILR As Long, mMLR As Long, mDLR As Long, mOLR As Long, mPLR As Long, mVLR As Long, i As Long
Dim Rng As Range
Dim Dic As Object

Set m = ThisWorkbook
Set mI = m.Sheets("CC_IQR")
Set mM = m.Sheets("CC_MC")
Set mD = m.Sheets("CC_MD")
Set mO = m.Sheets("ORC_PAR")
Set mP = m.Sheets("PRF")
Set mV = m.Sheets("Variables")

mILR = mI.Range("A" & Rows.Count).End(xlUp).Row
mMLR = mM.Range("A" & Rows.Count).End(xlUp).Row
mDLR = mD.Range("A" & Rows.Count).End(xlUp).Row
mOLR = mO.Range("A" & Rows.Count).End(xlUp).Row
mPLR = mP.Range("A" & Rows.Count).End(xlUp).Row
mVLR = mV.Range("A" & Rows.Count).End(xlUp).Row

Set Dic = CreateObject("Scripting.Dictionary")

For Each Rng In mI.Range("A2", mI.Range("A" & mI.Rows.Count).End(xlUp))
    If Not Dic.Exists(Rng.Value & Rng.Offset(0, 11)) Then
        Dic.Add Rng.Value & Rng.Offset(0, 11), Nothing
    End If
Next Rng

For Each Rng In mD.Range("A2", mD.Range("A" & mD.Rows.Count).End(xlUp))
    If Dic.Exists(Rng.Value & Rng.Offset(0, 11)) Then
        mD.Range("I" & Rng.Row).Copy
            mI.Range("Q" & Rng.Row).PasteSpecial xlPasteValues
    Else
    If Not Dic.Exists(Rng.Value & Rng.Offset(0, 11)) Then
        mI.Range("Q" & Rng.Row) = "Review"
    End If
Next Rng

Dic.RemoveAll

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[Joe4]

You are missing an "END IF" in your second loop block.
You have 2 "IF" statements, but only one "END IF".

Try changing that block like this:

For Each Rng In mD.Range("A2", mD.Range("A" & mD.Rows.Count).End(xlUp))
    If Dic.Exists(Rng.Value & Rng.Offset(0, 11)) Then
        mD.Range("I" & Rng.Row).Copy
            mI.Range("Q" & Rng.Row).PasteSpecial xlPasteValues
    Else
        If Not Dic.Exists(Rng.Value & Rng.Offset(0, 11)) Then
            mI.Range("Q" & Rng.Row) = "Review"
        End If
    End If
Next Rng

 

[reberryjr]

You are missing an "END IF" in your second loop block.
You have 2 "IF" statements, but only one "END IF".

Try changing that block like this:

For Each Rng In mD.Range("A2", mD.Range("A" & mD.Rows.Count).End(xlUp))
    If Dic.Exists(Rng.Value & Rng.Offset(0, 11)) Then
        mD.Range("I" & Rng.Row).Copy
            mI.Range("Q" & Rng.Row).PasteSpecial xlPasteValues
    Else
        If Not Dic.Exists(Rng.Value & Rng.Offset(0, 11)) Then
            mI.Range("Q" & Rng.Row) = "Review"
        End If
    End If
Next Rng

Ugh! I even looked at the If vs. End If; just missed it. The End If addition allows the script to run, but it takes over 10 minutes.

 

[Joe4]

Ugh! I even looked at the If vs. End If; just missed it. The End If addition allows the script to run, but it takes over 10 minutes.

The time is due much more to looping than it is to IF/END IF statements.
Loops are notoriously slow, especially if you have a lot of data to loop through.

You may also want to try suppressing calculations until the end of your code also, like you have done with Screen Updating and Alerts.

 

[reberryjr]

The time is due much more to looping than it is to IF/END IF statements.
Loops are notoriously slow, especially if you have a lot of data to loop through.

You may also want to try suppressing calculations until the end of your code also, like you have done with Screen Updating and Alerts.

@Joe4 Moving the calcs to the end of the code sped it up immensely. Unfortunatley, the dictionary isn't working. If the underlying scenario is true, then I want the value in column I to map over to column Q. I've been looking online, and at past dictionaries I've received help on, but I can't seem to figure out where I'm going wrong. If I need to create a new post, I don't mind.

Sub IQR_Calcs()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim m As Workbook
Dim mI As Worksheet, mM As Worksheet, mD As Worksheet, mO As Worksheet, mP As Worksheet, mV As Worksheet
Dim mILR As Long, mMLR As Long, mDLR As Long, mOLR As Long, mPLR As Long, mVLR As Long, i As Long
Dim Rng As Range
Dim Dic As Object

Set m = ThisWorkbook
Set mI = m.Sheets("CC_IQR")
Set mM = m.Sheets("CC_MC")
Set mD = m.Sheets("CC_MD")
Set mO = m.Sheets("ORC_PAR")
Set mP = m.Sheets("PRF")
Set mV = m.Sheets("Variables")

mILR = mI.Range("A" & Rows.Count).End(xlUp).Row
mMLR = mM.Range("A" & Rows.Count).End(xlUp).Row
mDLR = mD.Range("A" & Rows.Count).End(xlUp).Row
mOLR = mO.Range("A" & Rows.Count).End(xlUp).Row
mPLR = mP.Range("A" & Rows.Count).End(xlUp).Row
mVLR = mV.Range("A" & Rows.Count).End(xlUp).Row

'Identify the VTC start date.
Set Dic = CreateObject("Scripting.Dictionary")

For Each Rng In mI.Range("A2", mI.Range("A" & mI.Rows.Count).End(xlUp))
    If Not Dic.exists(Rng.Value & Rng.Offset(0, 11)) Then
        Dic.Add Rng.Value & Rng.Offset(0, 11), Nothing
    End If
Next
For Each Rng In mD.Range("A2", mD.Range("A" & mD.Rows.Count).End(xlUp))
    If Dic.exists(Rng.Value & Rng.Offset(0, 7)) Then
        mD.Range("I" & Rng.Row).Copy
            mI.Range("Q" & Rng.Row).PasteSpecial xlPasteValues
    Else
        If Not Dic.exists(Rng.Value & Rng.Offset(0, 11)) Then
            mI.Range("Q" & Rng.Row) = "Review"
        End If
    End If
Next

Dic.RemoveAll

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[Joe4]

If I need to create a new post, I don't mind.

Yeah, it might be best to do that, as the original question/issue has been resolved (regarding the error you were getting), and I have never worked with Dictionaries, so I cannot offer much help there.