Solved

VBA code gives error on the range.resize

Posted on 2016-10-05
3
107 Views
Last Modified: 2016-10-05
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
Comment
Question by:excelismagic
  • 2
3 Comments
 
LVL 30

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41830177
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
 
LVL 3

Author Closing Comment

by:excelismagic
ID: 41830202
thank you so much Neeraj. very much appreciated.
0
 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41830304
You're welcome. Glad to help.
0

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question