"End With" error & other VBA code critiques
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: "End With" error & other VBA code critiques

  1. #1
    New Member
    Join Date
    Dec 2017
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default "End With" error & other VBA code critiques

     
    Hello,

    I am trying to prep a batch of files in a folder for use in a database, so my code (pasted in whole below) attempts to 1) create a number of columns with ID codes for each row, 2) clean up the existing files' field headings, 3) delete unnecessary rows, and 4) compile them all into one worksheet.

    I'm a novice to VBA and programming in general, so my main method of developing programs is mostly through copying-and-pasting from google (lmao), which absolutely explains the errors.

    The first error (of many to come, I'm sure) is an "end with" error. Any pointers on how to solve that?

    Additionally, could someone take the time to point out any glaring inconsistencies or inefficiencies in this code? I know that might be a bit of a tall order, but I would really appreciate the criticism!

    Thanks!

    Here's the code:
    Code:
    Sub combiningFiles()
    
    
    'IDing all files
    Dim wb As Workbook
    Dim strFile As String, strDir As String
    Dim headers() As Variant
    headers() = Array("Data Number", "Date-Time", "Temperature", "Relative Humidity", "Concentration")
    strDir = "C:\Users\..."
    strFile = Dir(strDir & "*.xlsx")
    
    
    Do While strRile <> ""
    
    
        Set wb = Workbooks.Open(Filename:=strDir & strFile, Local:=True)
        
        With wb
            'Inserting a column at column i
            Range("I1").EntireColumn.Insert
    
    
            'Filling new column with pollutant ID
            Dim LastRow As Long
            LastRow1 = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            Range("I3:I" & LastRow).Value = "CO2"
            
            'Inserting a column at column j
            Range("J1").EntireColumn.Insert
    
    
            'Filling new column with location ID
            LastRow2 = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            Range("J3:J" & LastRow).Value = "E"
            
            'Inserting a column at column j
            Range("K1").EntireColumn.Insert
    
    
            'Filling new column with room ID
            LastRow3 = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            Range("K3:K" & LastRow).Value = "Living Room"
            
            'Inserting a column at column l
            Range("L1").EntireColumn.Insert
    
    
            'Filling new column with home ID
            LastRow4 = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            Range("L3:L" & LastRow).Value = "Living Room"
            
            'Inserting a column at column m
            Range("M1").EntireColumn.Insert
    
    
            'Filling new column with home ID
            LastRow5 = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            Range("M3:M" & LastRow).Value = Mid(FilePath, InStrRev(FilePath, "") + 12, InStrRev(FilePath, ".") - InStrRev(FilePath, "") - 12)
    
    
            'Simplify field headings
            .Rows(2).Value = "" 'This will clear out row 2
            For i = LBound(headers()) To UBound(headers())
            .Cells(2, 2 + i).Value = headers(i)
            
            'Delete first row
            Rows(1).Delete
            
            'Compile all files in a folder into one spreadsheet
            Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
            For Each Sheet In ActiveWorkbook.Sheets
            Sheet.Copy After:=ThisWorkbook.Sheets(1)
        Next Sheet
            Workbooks(Filename).Close
            Filename = Dir()
        Set wb = Nothing
        strFile = Dir
        End With
        
    Loop
    
    
    End Sub
    Last edited by RoryA; Dec 7th, 2017 at 09:25 AM. Reason: Code tags

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    44,439
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    4 Thread(s)

    Default Re: "End With" error & other VBA code critiques

      
    Welcome to the Board!

    Sometimes those errors messages are a bit misleading. All loops and With statements need to be closed. You are actually missing the closing "Next" statement for your "For i..." loop (you have two "For" statements, but only one "Next" statement).

    Note that this things are much easier for us to see if you use Code tags so the indenting and spacing on your code isn't lost when you paste it here.
    See: https://www.mrexcel.com/forum/board-...-vba-code.html
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com