"End With" error & other VBA code critiques

sadsadsaxofsht

New Member
Joined
Dec 7, 2017
Messages
1
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 a moderator:

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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-announcements/515787-how-post-your-vba-code.html
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top