"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!

 

[BrianJN1]

Everything seems to cross-reference as far as I can see.

Since your error is apparently located in that red line, what is the Object?
Could it be suggesting that you select the sheet before performing that operation?
I'm at a loss.

 

[Yulyo]

Hello Brian,
It seems that my Sheet(15) doesn't contain any date in A21. Insead it contains a text "No forecast available". If i delete this text, or put a date myself in A21, everything it's ok...

 

[JackDanIce]

What is the sheet and cell address of this range in your code?

Range("908!A21").Value

Do you have a sheet named "908" and it is cell A21 in that sheet? If so, why not use Sheets("908").Range("A21").Value?

You also seem to be mixing sheet names with sheet index numbers, e.g. Sheets(11) is the same as Sheets("908"), being consistent helps with debugging.

 

[Yulyo]

Hello JackDanIce,
Yes, i have a sheet "908" and a cell A21 in sheet 908.
I tried your suggestion and it seems to work just fine. I will try to update all "sheet(11) / "sheet(908)" thing, to be more consistent.

The problem is that the VBA awaits to find a date (dd/mm/yyyy) in cell a21 from sheet 908. But there are some days when i have no request in this page. Instead of a date, the client is writing "no forecast available". When the code find this text instead of a date, it gives me the "runtime error 1004".

Is it possible to modify the code so that, if it does not find a date in sheet 908, cell A21, to ignore that line and not try to import my data?

As I think you realize, I'm a beginner in the VBA
Thank you, both!

 

[JackDanIce]

Hi Yulyo,

Please clean your code up and use sheet name references (not the sheet index) and post it back. Please explain clearly what you need it to do with examples.

It'll be easier to then include the IF condition than trying to review your code as it is (unless anyone else reading suggests otherwise) as well as suggest code/layout that may be easier to follow for yourself/VBA beginner.

If you want to do other reading, look at Excel objects and parent and child relationships, e.g. cell A1 if the sheet is not defined, the code interprets that as meaning A1 on the active sheet.

Range("Sheet1"!A1) is cell A1 on Sheet1 (using your code for guidance), but the norm is Sheets("Sheet1").Range("A1") since the range is a child of the parent sheet in this example.

 

[Yulyo]

Hello again,

I tried to clean my code a little bit, i hope it's better now

So, basically this is what i need:
- i have two sheets: "A_19_01" (this is the main sheet) and "date_1901_908" (this is a request sheet, that i recieve daily from our customers)

- on "A_19_01" column B i have a list of dates (dd/mm/yy) and on column AM i have a list of numbers (basically the same numbers as sheet "date_1901_908" column B)


- on "date_1901_908" i have a list of dates (dd/mm/yy) on column A, starting from A21, and some numbers on column B, starting from B21.

- the VBA reads the date (dd/mm/yy) in "date_1901_908"! starting from A21 and going down and copy the numbers in column B (starting from B21 and going down), to sheet "A_19_01" column AM.

On sheet "date_1901_908", column A is formated as date and column B as number.

Everything is working just fine, until our customer doesn't have any product request in sheet "date_1901_908". In this case, he usually write "no forecast available" in "date_1901_908"!A21.

The code is expecting a date (dd/mm/yy), in "date_1901_908"!A21, not a text, so it returns a ""runtime error 1004 method range object_global failed"

So bassically i neet to tweak the VBA a little bit, so that when a text is found in "date_1901_908"!A21, to skip the importing of "date_1901_908" column B into "A_19_01" column AM.


////////////////////////////////////////////////////////////////////////////////////////////


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




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


StartDate = Sheets("date_1901_908").Range("A21").Value


For StartRow = 1 To lastRw1
If Sheets("A_19_01").Range("B" & StartRow).Value = StartDate Then
FndRw = StartRow
Exit For
End If
Next




Sheets("A_19_01").Range("AM" & FndRw & ":AM" & lastRw1).ClearContents
lastRw2 = Sheets("date_1901_908").Range("A" & Rows.Count).End(xlUp).Row


For nxtRw = 1 To lastRw2


With Sheets("A_19_01").Range("B2000:B" & lastRw1)
Set m = .Find(Sheets("date_1901_908").Range("A" & nxtRw), lookat:=xlWhole)




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




End If
End With
Next
MsgBox "Done!"
End Sub
////////////////////////////////////////////////////////////////////////////////////////////

 

[JackDanIce]

Untested, so try on a copy of your workbook:

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
        
        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 dic = CreateObject("Scripting.Dictionary")
    Set wkD = Sheets("A_19_01")
    
    With wkD
        LR = .Cells(.Rows.count, 2).End(xlUp).row
        x = .Cells(1, 2).Resize(LR).find(what:=arr(1, 1), LookIn:=xlValues, lookat:=xlWhole).row
        LR = LR - rng.row + 1
        .Cells(x, 39).Resize(LR).ClearContents
        arr = .Cells(x, 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 wkD = Nothing
    Set dic = Nothing
    Erase arr
    
    MsgBox "Finished processing customer forecasts", vbOKOnly, "Finished Processing Forecasts"

End Sub

 

[Yulyo]

Right now i have an error on this line:
dic(arr(x, 1)) = arr(x, 2)

 

[JackDanIce]

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