"runtime error 1004 method range of object _global failed" VBA error

[Yulyo]

Hello all,

Please help with an "runtime error 1004 method range of object _global failed" error, that i recieve in this vba. I have around 10 more VBAs before this one, none of them return an error, but this one (they are identical, only page numbers a different):



=============================================
Sub actualizare()
Dim lastRw1, lastRw2, nxtRw, m
Dim StartRow, x
Dim StartDate As String
Dim FndRw As String




lastRw1 = Sheets(11).Range("B" & Rows.Count).End(xlUp).Row




StartDate = Range("908!A21").Value




For StartRow = 1 To lastRw1
If Range("19_01!B" & StartRow).Value = StartDate Then




FndRw = StartRow
Exit For
End If
Next




Range("19_01!AM" & FndRw & ":AM" & lastRw1).ClearContents






lastRw2 = Sheets(15).Range("A" & Rows.Count).End(xlUp).Row




For nxtRw = 1 To lastRw2




With Sheets(11).Range("B2000:B" & lastRw1)




Set m = .Find(Sheets(15).Range("A" & nxtRw), lookat:=xlWhole)




If Not m Is Nothing Then
Sheets(15).Range("B" & nxtRw).Copy _
Sheets(11).Range("AM" & m.Row)




End If
End With
Next
End Sub
=============================================


Thank you!

 

[Yulyo]

Previous line it's ok now, but i recieve another on this line:
x = .Cells(1, 2).Resize(LR).Find(what:=arr(1, 1), LookIn:=xlValues, lookat:=xlWhole).Row

 

[JackDanIce]

Sure, try:

Sub Actualizare()

    Dim x       As Long
    Dim LR      As Long

    Dim arr()   As Variant
    Dim dic     As Object

    Dim rng     As Range
    Dim wkS     As Worksheet
    Dim wkD     As Worksheet
    
    Set wkS = Sheets("date_1901_908")
        
    With wkS
        If InStr(1, .Cells(21, 1).Value, "no", 1) Then
            MsgBox "Customer has no forecast available", vbExclamation, "No Forecast Available"
            Set wkS = Nothing
            Exit Sub
        End If
        Set dic = CreateObject("Scripting.Dictionary")
        LR = Application.Max(.Cells(.Rows.count, 1).End(xlUp), 21)
        arr = .Cells(21, 1).Resize(LR - 21 + 1, 2).Value
        For x = LBound(arr, 1) To UBound(arr, 1)
            dic(arr(x, 1)) = arr(x, 2)
        Next x
    End With
    
    Application.ScreenUpdating = False
        
    Set wkD = Sheets("A_19_01")
    
    With wkD
        LR = .Cells(.Rows.count, 2).End(xlUp).row
        Set rng = .Cells(1, 2).Resize(LR).find(what:=arr(1, 1), LookIn:=xlValues, lookat:=xlWhole).row
        LR = LR - rng.row + 1
        .Cells(rng.row, 39).Resize(LR).ClearContents
        arr = .Cells(rng.row, 2).Resize(LR).Value
        For x = LBound(arr, 1) To UBound(arr, 1)
            arr(x, 1) = vbNullString
            arr(x, 1) = dic(arr(x, 1))
        Next x
        .Cells(.Rows.count, 39).End(xlUp).Offset(1).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
    End With
    
    Application.ScreenUpdating = True
    
    Set wkS = Nothing
    Set wkD = Nothing
    Set dic = Nothing
    Erase arr
    
    MsgBox "Finished processing customer forecasts", vbOKOnly, "Finished Processing Forecasts"

End Sub

 

[Yulyo]

error
Set rng = .Cells(1, 2).Resize(LR).Find(what:=arr(1, 1), LookIn:=xlValues, lookat:=xlWhole).Row

 

[JackDanIce]

Remove .Row from the end of that line

 

[Yulyo]

Error
LR = LR - rng.Row + 1


I think this is way to complicated for me...

 

[JackDanIce]

I tried to recreate your spreadsheet and this seems to work:

Sub Actualizare()

    Dim x       As Long
    Dim LR      As Long

    Dim arr()   As Variant
    Dim dic     As Object
    Dim rng     As Range
            
    With Sheets("date_1901_908")
        If InStr(1, .Cells(21, 1).Value, "no", 1) Then
            MsgBox "Customer has no forecast available", vbExclamation, "No Forecast Available"
            Exit Sub
        End If
        Set dic = CreateObject("Scripting.Dictionary")
        LR = Application.Max(.Cells(.Rows.count, 1).End(xlUp).row, 21)
        arr = .Cells(21, 1).Resize(LR - 21 + 1, 2).Value
    End With
    
    For x = LBound(arr, 1) To UBound(arr, 1)
        dic(arr(x, 1)) = arr(x, 2)
    Next x
    
    Application.ScreenUpdating = False
    
    With Sheets("A_19_01")
        LR = .Cells(.Rows.count, 2).End(xlUp).row
        Set rng = .Cells(1, 2).Resize(LR).find(what:=arr(1, 1), LookIn:=xlValues, lookat:=xlWhole)
        LR = LR - rng.row + 1
        .Cells(rng.row, 39).Resize(LR).ClearContents
        Erase arr
        arr = .Cells(rng.row, 2).Resize(LR).Value
        For x = LBound(arr, 1) To UBound(arr, 1)
            If dic.exists(arr(x, 1)) Then
                arr(x, 1) = dic(arr(x, 1))
            Else
                arr(x, 1) = vbNullString
            End If
        Next x
        .Cells(.Rows.count, 39).End(xlUp).Offset(1).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
    End With
    
    Application.ScreenUpdating = True
    
    Set wkS = Nothing
    Set dic = Nothing
    Erase arr
    
    MsgBox "Finished processing customer forecasts", vbOKOnly, "Finished Processing Forecasts"

End Sub

 

[Yulyo]

Thank you JackDanIce. Everything it's ok now.
Best regards!

 

[JackDanIce]

You're welcome, glad it's resolved

 

[Yulyo]

I am trying now to adapt it to my main file.
Still not working right now ))) But i tested on a new, simple file. Everything was perfect. Just need to understand why i am still having an error on "LR = LR - rng.Row + 1"
But the ideea is that the code is working in the new file, i really appreciate your patience Jack

 

[JackDanIce]

Without your file, difficult to fix, it works for a simple test spreadsheet I created.