Scripting Dictionary and Next Without For Error

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
714
Office Version
  1. 365
Platform
  1. Windows
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.


VBA Code:
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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:
Rich (BB code):
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
 
Upvote 0
Solution
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:
Rich (BB code):
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.
 
Upvote 0
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.
 
Upvote 0
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.
1689076600812.png


VBA Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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