Run time error 1004 - Autofill method of range class failed.

[danbates]

Hi,

Please can some help me?

I have the following code that works fine until I try it towards the end of the range.

If Not Intersect(Target, Range("D23:BM23")) Is Nothing Then   
 If Target.Value = "Y" Then
    Target.Offset(, 1) = "C"
    Target.Offset(, 1).AutoFill Range(Target.Offset(, 1), "BM" & Target.Row)
End If
End If

So basically it is for a monthly check to be carried out at work. Once the check has been done that cell is entered with a Y and the rest of the range automatically enter a C and then it doesn't need to be touched again until the next month.

But if the check hasn't been done before column BL which is the day shift on the last day of the month, it comes up with the error in the title.

When I end the error message the code has done what it should of done, so does anyone know why the error message is popping up?

Any help would be much appreciated.

Thanks

Dan

 

[steve the fish]

See if this works for you:

Set Rng = Intersect(Target, Range("D23:BL23"))

If Not Rng Is Nothing Then
    For Each c In Rng
        If UCase(c.Value) = "Y" Then
            Application.EnableEvents = False
                Range(Cells(23, c.Column + 1), Cells(23, "BM")) = "C"
            Application.EnableEvents = True
        End If
    Next
End If

 

[danbates]

Hi,

Thank you for your reply but it's popping up with an compile error: variable not defined and it is highlighting the c in this row

For Each c In rng

thanks again

Dan

 

[steve the fish]

Ok you need this just after the worksheet_change line:

Dim rng As Range, c As Range

 

[danbates]

Hi,

It's put the Y in with no error message but it hasn't completed the range with C. (ie BM23 should have a C)

I have just noticed the range you have put is D23:BL23.

Just in case it was a typo I changed it to D23:BM23 but it's the same outcome.

Thanks again

Dan

 

[steve the fish]

The BL was deliberate as you are offsetting one cell from the Y. What is it doing? Nothing? Couple of thing to do. Where have you placed the code? It should be in the appropriate sheet module. Secondly press ALT-F11. In the immediate window type:

Application.EnableEvents=True

Press enter. Does it now work?

 

[Yongle]

Try this

If Not Intersect(Target, Range("D23:BM23")) Is Nothing Then
    If Target.Value = "Y" Then
        Application.EnableEvents = False
        Target.Offset(, 1) = "C"
        Target.Offset(, 1).AutoFill Range(Target.Offset(, 1), "BM" & Target.Row)
        Application.EnableEvents = True
    End If
End If

 

[steve the fish]

Try this

If Not Intersect(Target, Range("D23:BM23")) Is Nothing Then
    If Target.Value = "Y" Then
        Application.EnableEvents = False
        Target.Offset(, 1) = "C"
        Target.Offset(, 1).AutoFill Range(Target.Offset(, 1), "BM" & Target.Row)
        Application.EnableEvents = True
    End If
End If

That wont work as per the opening post because Target.Offset(,1) and "BM" & Target.Row will be the same cell if BL23 is changed.

 

[Yongle]

@steve the fish - agreed

 

[danbates]

Hi Steve,

It's doing nothing after I enter the Y. Everything is still enabled as well and it is in the correct sheet module.

I have just altered my date on my laptop and again it's only entering the Y whereas with my original code worked fine until today.

Yongle - thanks for your reply but adding the EnableEvents into my original code hasn't changed anything either. It just doesn't want to fill out the C in column BM.

Thanks again

Dan