• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Error in - Autofill

Hi All,

i have done a copy and paste macro file, iam getting error while autofill in column H
the query is will update blank cell at H5 as "NR" it need to auto fill till last row pls help on same. i enclosed the excel file and macro code for your reference.
iam getting this error
run time error '1004'
method 'range' of object'_global' failed

vba code
>>> as many times <<<
>>> use code - tags <<<

Code:
Option Explicit
Sub Main_Button1_Click()
Dim x As Workbook
Dim y As Workbook
Dim pat As Variant
Dim lr1, lr2 As Long

'x workbook is download file
'y workbook is macro file

With x.Sheets("Mass Product add")
    lr1 = .Range("DV" & .Rows.Count).End(xlUp).Row
End With

With y.Sheets("MARA")
    lr2 = .Range("A" & .Rows.Count).End(xlUp).Row
End With

If y.Sheets("MARA").Range("H5").Value = "" Then
With y.Sheets("Mara")
    lr2 = Range("A5" & Rows.Count).End(xlUp).Row
    .Range("H5").SpecialCells(xlBlanks).Value = "NR"
    Range("H5").AutoFill Destination:=Range("H5:H" & lr2)
End With
End If
Thanks
Jawahar
 

Attachments

Last edited by a moderator:

p45cal

Well-Known Member
replace:
Code:
With y.Sheets("MARA")
  lr2 = .Range("A" & .Rows.Count).End(xlUp).Row
End With

If y.Sheets("MARA").Range("H5").Value = "" Then
  With y.Sheets("Mara")
    lr2 = Range("A5" & Rows.Count).End(xlUp).Row
    .Range("H5").SpecialCells(xlBlanks).Value = "NR"
    Range("H5").AutoFill Destination:=Range("H5:H" & lr2)
  End With
End If
with just:
Code:
With y.Sheets("MARA")
  If .Range("H5").Value = "" Then
    lr2 = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("H5:H" & lr2).Value = "NR"
  End If
End With
 
Top