[1004 Error] Dynamic Range

[Paulo_3456]

Hello everyone,

I tried everything before coming here with my code. It gives me the following error :

Run-time error '1004'. Application-defined or operation-defined error.

The goal of the macro is to set dynamic table range (located in "d_ws" worksheet) for chart's (located in "e_ws" worksheet) data source.

In order to get this dynamic table range, I define two variables (first and last table rows) using MATCH + COUNTIF functions.

Indeed, in the "d_ws" worksheet, datas are disposed with a Lookup value in first column (M).

Then, when I want to set the range with the variables, I get the 1004 error. Here is my code :

Public Sub graph_update()

Dim first_row As Double
Dim last_raw As Double
Dim Stock_ticker As String
Dim dates_range As Range
Dim cht As ChartObject
Dim e_ws As Worksheet
Dim d_ws As Worksheet


Application.ScreenUpdating = False


'Chart and worksheets Definition
Set e_ws = ActiveWorkbook.Worksheets("Equities")
Set d_ws = ActiveWorkbook.Worksheets("Datas_fs")
Set cht = e_ws.ChartObjects("GraphSharePrice")


'Lookup Value
Stock_ticker = WorksheetFunction.VLookup(e_ws.Range("tick_tab"), e_ws.Range("C:D"), 2, False)

'First and last row definition 
first_row = Application.WorksheetFunction.Match(Stock_ticker, d_ws.Range("M:M"), 0)

last_row = (Application.WorksheetFunction.Match(Stock_ticker, d_ws.Range("M:M"), 0) _
+ Application.WorksheetFunction.CountIf(d_ws.Range("M:M"), Stock_ticker) - 1)
    
'THIS LINE IS GETTING ME THE 1004 ERROR
Set dates_range = d_ws.Range(d_ws.Cells(first_row - 1, 14), d_ws.Cells(first_row - 1 - 7, 14))
Set prices_range = d_ws.Range(d_ws.Cells(first_row - 1, 18), d_ws.Cells(first_row - 1 - 7, 18))

cht.Activate
ActiveChart.SetSourceData Source:=Range(dates_range, prices_range)

Application.ScreenUpdating = True


End Sub

The name "tick_tab" is referring to a named cell in e_ws worksheet.

If you need more info to understand where the error could come from, please let me know !

Thanks a lot for helping if you can !

 

[KolGuyXcel]

It seems that the value of the first_row is less than or equal to 7/8.

 

[rlv01]

Somewhere, somehow you are getting an invalid value for first_row. Add a debug.print statement and then monitor the "immediate" window in the debugger to see where the problem is

    'THIS LINE IS GETTING ME THE 1004 ERROR
    Debug.Print "Value of first_row = " & first_row
    Set dates_range = d_ws.Range(d_ws.Cells(first_row - 1, 14), d_ws.Cells(first_row - 1 - 7, 14))
    Set prices_range = d_ws.Range(d_ws.Cells(first_row - 1, 18), d_ws.Cells(first_row - 1 - 7, 18))

 

[Paulo_3456]

It seems that the value of the first_row is less than or equal to 7/8.

Yes it is absolutely true ! Indeed, Row number can't be negative, I am so dumb ...

Thanks a lot !

 

[Paulo_3456]

Somewhere, somehow you are getting an invalid value for first_row. Add a debug.print statement and then monitor the "immediate" window in the debugger to see where the problem is

    'THIS LINE IS GETTING ME THE 1004 ERROR
    Debug.Print "Value of first_row = " & first_row
    Set dates_range = d_ws.Range(d_ws.Cells(first_row - 1, 14), d_ws.Cells(first_row - 1 - 7, 14))
    Set prices_range = d_ws.Range(d_ws.Cells(first_row - 1, 18), d_ws.Cells(first_row - 1 - 7, 18))

Thank you very much for your prompt answer and for this tip I did not know yet. I think I am going to use it every time I get blocked now.