• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 588
  • Last Modified:

VBA code gives error on the range.resize

I am using this code  and it gives error  Application defined or object defined error runtime error 1004

but if i remove the resize for example the first line FROM wb.Worksheets("PRODUCT").Range("C17").Resize(4, 0).Value = "NT" TO wb.Worksheets("PRODUCT").Range("C17").Value = "NT"  it works.  

i am wondering why using the resize do not work.  any idea?

Sub LoopAllExcelFilesInFolder()
'PURPOSE: To loop through all Excel files in a user specified folder and perform a set task on them
'SOURCE: www.TheSpreadsheetGuru.com

Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog

'Optimize Macro Speed
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual

'Retrieve Target Folder Path From User
  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
      .Title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
    End With

'In Case of Cancel
NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings

'Target File Extension (must include wildcard "*")
  myExtension = "*.xlsx"

'Target Path with Ending Extention
  myFile = Dir(myPath & myExtension)

'Loop through each Excel file in folder
  Do While myFile <> ""
    'Set variable equal to opened workbook
      Set wb = Workbooks.Open(Filename:=myPath & myFile)
    
    'Ensure Workbook has opened before moving on to next line of code
      DoEvents
    


     wb.Worksheets("PRODUCT").Range("C17").Resize(4, 0).Value = "NT"
   wb.Worksheets("PRODUCT").Range("C21").Resize(3, 0).Value = "ER"
  wb.Worksheets("PRODUCT").Range("B9").Value = "CE"
    wb.Worksheets("PRODUCT").Range("D23").Value = "Y"
   wb.Worksheets("PRODUCT").Range("E29").Resize(0, 1).Value = "N"
    wb.Worksheets("PRODUCT").Range("G29").Value = "B"
   wb.Worksheets("PRODUCT").Range("B33").Value = "C"
    
    'Save and Close Workbook
      wb.Close SaveChanges:=True
      
    'Ensure Workbook has closed before moving on to next line of code
      DoEvents

    'Get next file name
      myFile = Dir
  Loop

'Message Box when tasks are completed
  MsgBox "Task Complete!"

ResetSettings:
  'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

Open in new window

0
excelismagic
Asked:
excelismagic
  • 2
1 Solution
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
It should be Resize(4, 1) instead of Resize(4, 0). Resize is different than Offset. A range cannot be resized to the zero column.
0
 
excelismagicAuthor Commented:
thank you so much Neeraj. very much appreciated.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now