1

I have the following Spreadsheet: [Data in Columns A-Q and almost 3000 Rows][1]

I have a dropdown on another page that has a cell for Name/Mode/Shift Listed on it and am creating a variable for each. For some reason when I change a field in a drop down, I get a '91' Object error. When I use a combination of all of the items in the dropdown that are in the 1st position, the macro works just fine. The issue is always when I change either the DC/Mode/Shift. DC is a string, Mode is a String, and Shift is an Integer.

Each Dim Search / Dim FindRow was it's own passthrough function but combined everything. Any help would be much appreciated!!!

Below is my code:

Sub DailyRouteInput_Button8_Click()
    Dim DC As String

    Worksheets("Daily Route Input").Activate

    Range("U1").Select
    DC = ActiveCell.Value

    ActiveCell.Offset(0, 1).Select
    Mode = ActiveCell.Value

    Range("C5").Select
    Shift = ActiveCell.Value

    Worksheets("Daily Route Master Data").Activate

    (Was new passthrough function)

    Dim SearchRange As Range
    Dim FindRow As Range
    Set SearchRange = Range("A2", Range("A2").End(xlUp))
    Set FindRow = SearchRange.Find(What:=DC, LookIn:=xlValues, lookat:=xlWhole)

    DC = FindRow.Row '---- Here is where the problem is ---------

    Range("A" & DC).Offset(0, 1).Select

    (Was new passthrough function)

    Dim newSearchRange As Range
    Dim newFindRow As Range
    Set newSearchRange = Range("B" & DC, Range("B" & DC).End(xlUp))
    Set newFindRow = newSearchRange.Find(Mode, LookIn:=xlValues, lookat:=xlWhole)

    Mode = newFindRow.Row '---- Here is where the problem is ---------

    Range("B" & Mode).Offset(0, 1).Select

    (Was new passthrough function)

    Dim finalNewSearchRange As Range
    Dim finalNewFindRow As Range
    Set finalNewSearchRange = Range("C" & Mode, Range("C" & Mode).End(xlUp))
    Set finalNewFindRow = finalNewSearchRange.Find(Shift, LookIn:=xlValues, lookat:=xlWhole)

    Shift = finalNewFindRow.Row '---- Here is where the problem is ---------

    Range("C" & Mode).Offset(0, 1).Select

    WeekCheck = ActiveCell.Value
    ActiveCell.Offset(0, 1).Select
    MonthCheck = ActiveCell.Value
Community
  • 1
  • 1
Raf
  • 11
  • 3
  • 1
    Probably whatever you searched for wasn't found, and `finalNewFindRow` is `Nothing`. Lots of examples of this in SO – chris neilsen May 29 '18 at 06:21
  • 2
    You might want to read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) which solves a lot of issues, makes your code much more stable and a lot faster! Always specify a concrete worksheet for every `Range()`. – Pᴇʜ May 29 '18 at 06:41
  • Do you declare the data type for `Mode` anywhere? Once it gets to the `Find` row it may be searching for the correct value but in the wrong data type - e.g. `"1"` instead of `1`. Avoiding `Select` as @Peh mentions would help a lot. – Darren Bartrup-Cook May 29 '18 at 09:24
  • I'm also not getting what you're doing with `DC` and `Mode` - you set `DC` as a `String` data type and use it in the `FIND` command. You then assign the found row number to the `DC` variable - wouldn't it be better to hold the row number in a `Long` data type? – Darren Bartrup-Cook May 29 '18 at 09:29

1 Answers1

0

Thank you everyone that tried to give me some tips. I was able to figure it out. XlUp was the culprit and I changed it to xlDown. It was returning an empty object for that reason because I was testing data in a column that had nothing above it. Then I listened to the rest of you and set object variables and was able to eliminate .copy and did what I originally set out to do which is increase the overall speed of the returned results from 13 seconds to just over 1 second. You can find my code below. I know I didn't call out a few variables as strings or integers but outside of that if you can provide me with any further constructive criticism I would definitely be grateful! 

Sub DailyRouteInput_Button8_Click()

Dim nResult As Long
 nResult = MsgBox( _
 Prompt:="Did you save your prior DC/Mode updates to Master?", _
 Buttons:=vbYesNo)
 If nResult = vbNo Then
 Exit Sub
 End If

Dim VL As Workbook
Dim DailyRouteInput As Worksheet
Dim DailyRouteMaster As Worksheet
Dim masterRange As Range
Dim inputRange As Range
Dim DCInput As String
Dim ModeInput As String
Dim SearchRange As Range
Dim FindRow As Range
Dim finalNewSearchRange As Range
Dim finalNewFindRow As Range
Dim newSearchRange As Range
Dim newFindRow As Range


Set VL = ThisWorkbook
Set DailyRouteInput = VL.Sheets("Daily Route Input")
Set DailyRouteMaster = VL.Sheets("Daily Route Master Data")
Set masterRange = DailyRouteMaster.Range("A1:N2545")
Set inputRange = DailyRouteInput.Range("A1:BH57")


DCInput = inputRange.Cells(1, 21)
ModeInput = inputRange.Cells(1, 22)
ShiftInput = inputRange.Cells(5, 3)

DailyRouteMaster.Activate

Set SearchRange = masterRange.Range("A1", Range("A1").End(xlDown))
Set FindRow = SearchRange.Find(DCInput, LookIn:=xlValues, lookat:=xlWhole)
DC = FindRow.Row

Set newSearchRange = Range("B" & DC - 1, Range("B" & DC - 1).End(xlDown))
Set newFindRow = newSearchRange.Find(ModeInput, LookIn:=xlValues, lookat:=xlWhole)
Mode = newFindRow.Row

Set finalNewSearchRange = Range("C" & Mode - 1, Range("C" & Mode - 1).End(xlDown))
Set finalNewFindRow = finalNewSearchRange.Find(ShiftInput, LookIn:=xlValues, lookat:=xlWhole)
Shift = finalNewFindRow.Row

MonthCheck = DailyRouteMaster.Range("E" & DC)

If (MonthCheck = "January") Then
        
        DailyRouteMaster.Range("F" & Shift, "N" & Shift + 3).Copy
        DailyRouteInput.Activate
        inputRange.Cells(5, 26).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        DailyRouteMaster.Activate
        DailyRouteMaster.Range("F" & Shift + 4, "N" & Shift + 7).Copy
        DailyRouteInput.Activate
        inputRange.Cells(5, 39).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        DailyRouteMaster.Activate
        DailyRouteMaster.Range("F" & Shift + 8, "N" & Shift + 12).Copy
        DailyRouteInput.Activate
        inputRange.Cells(5, 52).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

        DailyRouteMaster.Range("F" & Shift + 13, "N" & Shift + 16).Copy
        DailyRouteInput.Activate
        inputRange.Cells(14, 26).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        DailyRouteMaster.Activate
        DailyRouteMaster.Range("F" & Shift + 17, "N" & Shift + 20).Copy
        DailyRouteInput.Activate
        inputRange.Cells(14, 39).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        DailyRouteMaster.Activate
        DailyRouteMaster.Range("F" & Shift + 21, "N" & Shift + 25).Copy
        DailyRouteInput.Activate
        inputRange.Cells(14, 52).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        
        DailyRouteMaster.Range("F" & Shift + 26, "N" & Shift + 29).Copy
        DailyRouteInput.Activate
        inputRange.Cells(23, 26).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        DailyRouteMaster.Activate
        DailyRouteMaster.Range("F" & Shift + 30, "N" & Shift + 33).Copy
        DailyRouteInput.Activate
        inputRange.Cells(23, 39).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        DailyRouteMaster.Activate
        DailyRouteMaster.Range("F" & Shift + 34, "N" & Shift + 38).Copy
        DailyRouteInput.Activate
        inputRange.Cells(23, 52).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        
        DailyRouteMaster.Range("F" & Shift + 39, "N" & Shift + 42).Copy
        DailyRouteInput.Activate
        inputRange.Cells(32, 26).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        DailyRouteMaster.Activate
        DailyRouteMaster.Range("F" & Shift + 43, "N" & Shift + 46).Copy
        DailyRouteInput.Activate
        inputRange.Cells(32, 39).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        DailyRouteMaster.Activate
        DailyRouteMaster.Range("F" & Shift + 47, "N" & Shift + 52).Copy
        DailyRouteInput.Activate
        inputRange.Cells(32, 52).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        DailyRouteInput.Activate

    Else
        MsgBox ("Something is Wrong!")
    End If

End Sub
Raf
  • 11
  • 3