Error 1004 - object-defined error when vba code not applicable

Thank you very much, that worked perfectly

I see you removed the various ".select and selection". Can you please explain what the benefit of this is?

Thank you,
S

No need to use "Select". Let's say I want Range("A1"). VBA knows it is Range("A1") whether or not I added select.
In fact it is bad programming style and could slow the execution of a code. However, as you learn and record macro, it is very helpful tool to easily see what is going on with the flow of the code.

You have fixed the range and therefore lost the flexibility. I am surprised why you got an error. The best and easiest way to solve the issue is for you to attach a sample. I can quickly adjust your code by testing it with the sample provided.

This is not a sample data, as you do not have any blanks on all of the columns, but I have attached a code which shows no error. Please attach a realistic sample.

Hi AB33,

Apologies for not sending the full file, it simply cannot fit (over 70Mo).

So I've verified everything once again and the issue keeps occuring.

Effectively I run the macro once and everything is fine. I have one column at the end that checks whether the line balances out and it does. I run the macro a second time and while nothing should change (because all blanks are filled and all the arguments shouldn't execute), all my numbers change (but it is too difficult for me to isolate to one particular column because of the sheer volume of data).

So my question is, is there anything I can add to the below code so each "argument" (probably not using the right terminology) does not run if the conditions aren't met (eg don't do anything to column H if you can't find blank cells).

Sub Fill_Gaps_Spreadsheet()

' Interrupts Automatic Calculation, ScreenUpdating and StatusBar

Application.Calculation = xlCalculationManual

Application.ScreenUpdating = False

Application.DisplayStatusBar = False

' Converts column D to number

    Range("D2:D70000").Select
    With Selection
        .NumberFormat = "General"
        .Value = .Value
    End With
    
    
' Imports Min for current range where SOH is Nil

    With Range("H2:H70000").Select
      On Error Resume Next
      Selection.SpecialCells(xlCellTypeBlanks).Select
      On Error GoTo 0
      Selection.FormulaR1C1 = "=INDEX(CurrentMinMax!R2C4:R50000C4,MATCH(RC[-7]&RC[-4],CurrentMinMax!R2C1:R50000C1,0))"
    End With
    
    
' Replaces current blank Unit Price with start of year Unit Price

    With Range("Q2:Q70000").Select
      On Error Resume Next
     Selection.SpecialCells(xlCellTypeBlanks).Select
      On Error GoTo 0
      Selection.FormulaR1C1 = "=RC[31]"
    End With
    
' Calculates Min Stock Value & SOH Value for current range

    With Range("R2:R70000").Select
      On Error Resume Next
      Selection.SpecialCells(xlCellTypeBlanks).Select
      On Error GoTo 0
      Selection.FormulaR1C1 = "=RC[-1]*RC[-10]"
    End With
    
    With Range("T2:T70000").Select
      On Error Resume Next
      Selection.SpecialCells(xlCellTypeBlanks).Select
      On Error GoTo 0
      Selection.FormulaR1C1 = "=RC[-3]*RC[-9]"
    End With
   

' Imports Min for start of year range

    With Range("AM2:AM70000").Select
      On Error Resume Next
      Selection.SpecialCells(xlCellTypeBlanks).Select
      On Error GoTo 0
      Selection.FormulaR1C1 = "=INDEX(OldMinMax!R2C4:R129556C4,MATCH(RC[-38]&RC[-35],OldMinMax!R2C1:R129556C1,0))"
    End With

' Imports Min for start of year range where old min is not available

    With Range("AM2:AM70000").Select
      On Error Resume Next
      Selection.SpecialCells(xlCellTypeFormulas, 16).Select
      On Error GoTo 0
      Selection.FormulaR1C1 = "=RC[-31]"
    End With

  
' Replaces blank Unit Price for start of year range with Unit Price from current range

    With Range("AV2:AV70000").Select
      On Error Resume Next
     Selection.SpecialCells(xlCellTypeBlanks).Select
      On Error GoTo 0
      Selection.FormulaR1C1 = "=RC[-31]"
    End With
    
    
' Enter 0 for start of year SOH Nil

    With Range("AP2:AP70000").Select
      On Error Resume Next
      Selection.SpecialCells(xlCellTypeBlanks).Select
      On Error GoTo 0
      Selection.FormulaR1C1 = "=0"
    End With
    
' Calculates Min Stock Value & SOH Value for start of year range

    With Range("AW2:AW70000").Select
      On Error Resume Next
      Selection.SpecialCells(xlCellTypeBlanks).Select
      On Error GoTo 0
      Selection.FormulaR1C1 = "=RC[-1]*RC[-10]"
    End With

    With Range("AY2:AY70000").Select
      On Error Resume Next
      Selection.SpecialCells(xlCellTypeBlanks).Select
      On Error GoTo 0
      Selection.FormulaR1C1 = "=RC[-3]*RC[-9]"
    End With
    

' Removes formulas from workbook (excluding BZ to CH)

Dim smallrng As Range

    For Each smallrng In Range("H2:H70000,Q2:Q70000,R2:R70000,T2:T70000,AM2:AM70000,AP2:AP70000,AV2:AV70000,AW2:AW70000,AY2:AY70000").Areas

        With smallrng
            .Cells.Copy
            .Cells.PasteSpecial xlPasteValues
            .Cells(1).Select
        End With
        Application.CutCopyMode = False

Next smallrng

' Removes all created #N/As

Cells.Replace "#N/A", "", xlWhole

' Restores Automatic Calculation, ScreenUpdating and StatusBar

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

Application.DisplayStatusBar = True

End Sub

THank you

You do not need to attach massive data, just a sample with few lines which mimics your actual data. It is a simple-few lines of raw data and desired result. When I apply the code, it should work with any data as long as the format of data for which the code is designed to work has not changed.
The reason I always ask for a sample is to cut down all the misunderstanding and running around the circle.

Hi AB33,

I finally found a way to isolate enough lines to replicate the issue.

In the attached file, column CI is used as a check (when the macro is pressed, all values should turn to 0 via adjustments made in previous columns). Because some data is missing, you will see that the values don't change the first time you click. Yet if you click a second time, everything changes to values that are highly incorrect.

I can't isolate what seems to create the issue if the macro is run a second time so any help on this would be greatly appreciated.

Also, could you please let me know whether:

. there is a way to automatically adjust the range to the last line of data (which would vary every month) rather than hardcoding it in the vba
. there is a more efficient way to write the code to reduce the processing time over ~70,000 lines.

Thanks again for your help.
S

Hi SubWay,
Before I look at how to trim the code and cut some of the lines down, we need to get the code working. I think the issue you have is: There are lots of errors on the formula because the formulas do not have iferror line. Since the cells are linked by formulas and if there is a single error on a column that will affect the rest of the columns. You need to include iferror on each formula. I also believe that your Index and match formula is wrong. I have come up with my own formula for column H and you can follow the format of this formula for the rest of I and M function.

Hi AB33,

I haven't been able to find errors in the formula you mentioned but more than happy to include an IFERROR(___,"").

The only section of the file that is likely to return an error is "' Imports Min for start of year range", which is promptly followed by a section that addresses the said "errors" (which need to be loaded so it can trigger this follow-up section).

    With Sheets("Data").Range("AM2:AM" & Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row)
      On Error Resume Next
      .SpecialCells(xlCellTypeFormulas, 16).Select
      On Error GoTo 0
      Selection.FormulaR1C1 = "=RC[-31]"
    End With

I have double checked my index&match and confirm that it works as it is. I know I'm not capturing the column and define the lookup array to one column but it works well :-)

Sorry for not being clear before

The issue that I have is when I run the macro twice (inadvertently, which may happen if someone else is using this file).

The main aim of this spreadsheet is to fill the blanks in the data set that I have so theoretically once it has been run once, there is nothing left to do. Yet when I run it a second time, I have very strange values that are loaded.

To ensure your question on the vba you quoted:

step1: for all blank cells, import via the Index&Match
step2: when the Index&Match returns an error (as it should because data is missing somewhere else) then the default is to import the value across from the same line 31 columns prior.

I hope it makes sense

First, please move the code from sheet data in to a module. If you look at my code, the sheet name is fully referenced, while your code works on the active sheet, so it may be probable that you are not always running on the data sheet. It is difficult to see why are getting a strange result. please post the sample when you get this weird result so as to see why the code behaves in a such away.

Ok now I identified what precisely creates the issue

When the following code runs, it should select blank cells in column AP and enter a 0 value. The problem is that when the macro is run a second time (and there are no more blank cells), it replaces existing values with 0 instead of skipping them.

' Enter 0 for start of year SOH Nil

    With Range("AP2:AP60").Select
      On Error Resume Next
      Selection.SpecialCells(xlCellTypeBlanks).Select
      On Error GoTo 0
      Selection.FormulaR1C1 = "=0"
    End With

It seems to only do it with column AP even though I use the same "Selection.SpecialCells(xlCellTypeBlanks).Select" several times in the same macro. Any idea what is going on here?

Thanks,
Johann

Why are keep replacing the dynamic range I gave you with static data?

With Range("AP2:AP60")
      On Error Resume Next
      .SpecialCells(xlCellTypeBlanks) = 0
      On Error GoTo 0
    End With

So I think this is the first time but I'm giving up.

I can't explain why but the code you gave me (with the dynamic range) returns completely weird results (ie instead of importing numbers in blank cells it will make cells with value turn blank). THe code that I currently have (with the defined range) works well as long as I only run it only once. A second time just messes everything up.

Alternatively is there something simple to say "if there's isn't any blank cells in columns AP, AY, etc", then don't run the macro at all?

No,No!
First, I did not give you any code. It is your code, I just adjust the rows.
Second, the code on post 19, for example, does not fill 0 a second time if there are not blank cells. I have tested it. The code fills only when a cell is blank, so when it is first blank, it fills it with 0, but when you run the second time, the code skips all cells which are blank. Since we are testing more than a single cell and if some cells that are blank while others are not, the code errors, hence for tapping the error using resume next.
I suggest that you should isolate each range in to separate code and run a test so as to see which lines of the code are not working. So, put each range column on a separate code and run them.
I just can not replicate the issue you have. When I test and run the code, it works, but I do not know what do you mean by second time. Unless you show me when it errors, it is difficult to comment.

Thanks for sticking with me!

So I've done what you recommended and using the code you amended, mainly having ".SpecialCells (xlCellTypeBlanks)" instead of my initial "Selection.SpecialCells(xlCellTypeBlanks).Select" makes it that the formula is inserted in every single line instead of the blank ones. Hence why when I try to run with your proposal (that clearly defines the range) I get results that are all over the place.

My apologies for the confusion, a colleague suggested I change the "selection" part which I have now reversed.

I am now going crazy and will try to word my quest for a solution differently: is there something stopping this macro from running if there are no blank cells in the selected ranges? It runs very well the first time but then replaces existing values with formulas if run a second time.

If we can't add anything, I'll just make sure I am the only user and run it once only.

Also, is there a way to use the dynamic range you gave me for the last section (' Removes formulas from workbook (excluding BZ to CH)

Sub Fill_Gaps_Spreadsheet()

' Interrupts Automatic Calculation, ScreenUpdating and StatusBar

Application.Calculation = xlCalculationManual

Application.ScreenUpdating = False

Application.DisplayStatusBar = False

' Converts column D to number

    Range("D2:D70000").Select
    With Selection
        .NumberFormat = "General"
        .Value = .Value
    End With
    
    
' Imports Min for current range where SOH is Nil

With Sheets("Data").Range("H2:H" & Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row).Select
      On Error Resume Next
      Selection.SpecialCells(xlCellTypeBlanks).Select
      On Error GoTo 0
      Selection.FormulaR1C1 = "=INDEX(CurrentMinMax!R2C4:R50000C4,MATCH(RC[-7]&RC[-4],CurrentMinMax!R2C1:R50000C1,0))"
    End With
    
    
' Replaces current blank Unit Price with start of year Unit Price

With Sheets("Data").Range("Q2:Q" & Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row).Select
      On Error Resume Next
     Selection.SpecialCells(xlCellTypeBlanks).Select
      On Error GoTo 0
      Selection.FormulaR1C1 = "=RC[31]"
    End With
    
' Calculates Min Stock Value & SOH Value for current range

With Sheets("Data").Range("R2:R" & Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row).Select
      On Error Resume Next
      Selection.SpecialCells(xlCellTypeBlanks).Select
      On Error GoTo 0
      Selection.FormulaR1C1 = "=RC[-1]*RC[-10]"
    End With
    
With Sheets("Data").Range("T2:T" & Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row).Select
      On Error Resume Next
      Selection.SpecialCells(xlCellTypeBlanks).Select
      On Error GoTo 0
      Selection.FormulaR1C1 = "=RC[-3]*RC[-9]"
    End With
   

' Imports Min for start of year range

With Sheets("Data").Range("AM2:AM" & Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row).Select
      On Error Resume Next
      Selection.SpecialCells(xlCellTypeBlanks).Select
      On Error GoTo 0
      Selection.FormulaR1C1 = "=INDEX(OldMinMax!R2C4:R129556C4,MATCH(RC[-38]&RC[-35],OldMinMax!R2C1:R129556C1,0))"
    End With

' Imports Min for start of year range where old min is not available

With Sheets("Data").Range("AM2:AM" & Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row).Select
      On Error Resume Next
      Selection.SpecialCells(xlCellTypeFormulas, 16).Select
      On Error GoTo 0
      Selection.FormulaR1C1 = "=RC[-31]"
    End With

  
' Replaces blank Unit Price for start of year range with Unit Price from current range

With Sheets("Data").Range("AV2:AV" & Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row).Select
      On Error Resume Next
     Selection.SpecialCells(xlCellTypeBlanks).Select
      On Error GoTo 0
      Selection.FormulaR1C1 = "=RC[-31]"
    End With
    
    
' Enter 0 for start of year SOH Nil

With Sheets("Data").Range("AP2:AP" & Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row).Select
      On Error Resume Next
      Selection.SpecialCells(xlCellTypeBlanks).Select
      On Error GoTo 0
      Selection.FormulaR1C1 = "=0"
    End With
    
' Calculates Min Stock Value & SOH Value for start of year range

With Sheets("Data").Range("AW2:AW" & Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row).Select
      On Error Resume Next
      Selection.SpecialCells(xlCellTypeBlanks).Select
      On Error GoTo 0
      Selection.FormulaR1C1 = "=RC[-1]*RC[-10]"
    End With

With Sheets("Data").Range("AY2:AY" & Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row).Select
      On Error Resume Next
      Selection.SpecialCells(xlCellTypeBlanks).Select
      On Error GoTo 0
      Selection.FormulaR1C1 = "=RC[-3]*RC[-9]"
    End With
    

' Removes formulas from workbook (excluding BZ to CH)

Dim smallrng As Range

    For Each smallrng In Range("H2:H70000,Q2:Q70000,R2:R70000,T2:T70000,AM2:AM70000,AP2:AP70000,AV2:AV70000,AW2:AW70000,AY2:AY70000").Areas

        With smallrng
            .Cells.Copy
            .Cells.PasteSpecial xlPasteValues
            .Cells(1).Select
        End With
        Application.CutCopyMode = False

Next smallrng

' Removes all created #N/As

Cells.Replace "#N/A", "", xlWhole

' Restores Automatic Calculation, ScreenUpdating and StatusBar

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

Application.DisplayStatusBar = True

End Sub

Subway,
The thread has been open for over three weeks now and have not made any progress, in fact we are now gone back to square one. It might be better for someone else with fresh eyes to have a go. You need to re-start a new thread and hopefully others might be able to sort it out for you.