2

I have written a macro that can successfully loop through a folder, copy and paste the information into a new workbook, and insert three formulas. I'm having problems, though, with the index functions in some macros I call not displaying correctly.

Sub LoopAllExcelFilesInFolder()

Application.ScreenUpdating = False

Dim MyFolder As String
Dim MyFile As String
MyFolder = "C:\Users\myname\Desktop\Test Files"
MyFile = Dir(MyFolder & "\*.xlsx")

'This is where my loop code starts
Do While MyFile <> "" 
Workbooks.Open Filename:=MyFolder & "\" & MyFile, UpdateLinks:=0
Sheets("Report").Activate
Sheets("Report").Cells.Select
Application.CutCopyMode = False
Application.DisplayAlerts = False
Selection.Copy
ActiveWorkbook.Close True
Windows("Database Loop Test.xlsm").Activate
Sheets("PORT").Activate
Range("A1").Select
ActiveSheet.Paste
'It is successfully pasted to the desired workbook

'Here I call macros that insert sum, mid, and index functions. Sum and mid work but index doesn't
Call icvba
Call iovba
Call idvba

MyFile = Dir
Loop
End Sub

The weird thing is, when I check the index functions after I run the macro, they are all correct. Instead of showing the correct numbers, it shows up as #N/A. Here is the code for the macros I am calling. The code is the same for all three; only the worksheet is being changed.

Sub icvba()

Worksheets("COMMIT").Activate

Dim source As Worksheet
Dim detntn As Worksheet
Dim EmptyColumn As Long
Dim LastRow As Long

Set source = Sheets("vlookup")
Set detntn = Sheets("COMMIT")

LastColumn = detntn.Cells(1, detntn.Columns.Count).End(xlToLeft).Column
LastRow = Worksheets("COMMIT").Range("A:A").Rows.Count


'This if statement inputs the troublesome index function
If detntn.Range("A2") <> "" Then
EmptyColumn = LastColumn + 1
detntn.Cells(3, EmptyColumn).Formula = "=INDEX(PORT!$S$5:$S$4000,MATCH(COMMIT!$G3,PORT!$G$5:$G$4000,0))"
LastRow = ActiveSheet.UsedRange.Rows.Count
detntn.Cells(3, EmptyColumn).AutoFill    destination:=detntn.Range(detntn.Cells(3, EmptyColumn), detntn.Cells(LastRow,    EmptyColumn))
End If


'This if statement inputs the mid function
If detntn.Range("A2") <> "" Then
detntn.Cells(2, EmptyColumn).Formula = "=MID(PORT!$A$2,7,50)"
End If


'This if statement inputs a sum function
If detntn.Range("A2") <> "" Then
Worksheets("vlookup").Activate
ActiveSheet.Range("A1").Select
Selection.Copy
Worksheets("COMMIT").Activate
detntn.Cells(1, EmptyColumn).Select
Selection.PasteSpecial Paste:=xlAll
End If

Columns(EmptyColumn).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False

End Sub

Additionally, when I call the icvba, iocba, idvba macros individually, they work perfectly. It is only when I call them inside of my loop function that they stop working.

This is the first loop I have written with VBA, so I might be missing something simple. I just can't figure out where I'm going wrong. Any help would be much appreciated!

mynameisgooch
  • 95
  • 1
  • 1
  • 8
  • You specify the sheets but not the book. Edit: also, your code is very confusing: `Set detntn = Sheets("COMMIT") | LastColumn = detntn.Cells | LastRow = Worksheets("COMMIT")` – findwindow Jun 27 '16 at 15:44
  • 1
    Also, it's highly suggested to [avoid using `.Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) – BruceWayne Jun 27 '16 at 15:45
  • Hard to tell where all your worksheets are located - which workbook is your "PORT" sheet located? – dbmitch Jun 27 '16 at 15:57
  • @findwindow Sorry if my code is confusing. I've been coding in VBA for less than a week – mynameisgooch Jun 27 '16 at 16:02
  • @dbmitch the PORT worksheet is in the workbook that I am pasting all of my information into – mynameisgooch Jun 27 '16 at 16:03
  • Right. You're doing things you don't understand. After you set an object then you can refer to it like you did here `detntn.Cells` so you can do the same here `LastRow = Worksheets("COMMIT")`. Make sense? Edit: read Batman's link. – findwindow Jun 27 '16 at 16:04
  • @findwindow Yeah it makes sense I'm just really sloppy/inefficient with this right now – mynameisgooch Jun 27 '16 at 16:21
  • @mynameisgooch I get that - what I mean is - what workbook NAME is PORT located in? It has to be active and open – dbmitch Jun 27 '16 at 16:21
  • @dbmitch PORT is in the active and open workbook "Database Loop Test.xlsm" – mynameisgooch Jun 27 '16 at 16:28

1 Answers1

0

Sounds like the formulas just haven't calculated - Try putting this just before copying doing the pastespecial at the end:

Not_Calculated: 
Application.Wait(Now + TimeValue("0:00:04")) if not
Application.CalculationState = xlDone then goto Not_Calculated

That basically pauses the Macro from going any further for 4 seconds to allow the calculation complete and if it still hasn't wait another 4 seconds

Jeremy
  • 1,337
  • 3
  • 12
  • 26
  • Yeah but that's why I think it might be a calc issue (not that the calculation is wrong) - Think of the Macro and the worksheet formulas as two separate things that are running concurrently, and (assuming there is a lot of rows) the macro inputs the formulas and does an auto fill on them the worksheet mightn't have calculated all the formulas by the time the macro is ready to copy and paste them (the index formula is the most complicated of your three formulas) - My suggestion is that the macro does an if the formulas have been calculated, then proceed – Jeremy Jun 27 '16 at 16:05
  • @Jeremy I did try your solution, but unfortunately I ran into the same result – mynameisgooch Jun 27 '16 at 16:13