PDA

View Full Version : Application Defined or Object Defined error



SBrooky
09-14-2012, 06:22 AM
Hello,
I have a trouble with one of my loops I dont understand where i've gone wrong:
Option Explicit
Sub GetSummary()

Dim xRow&, vSF, xList&, xMonth&
Dim xMonthRange, xMDR, xDDR As Range
Dim xDirect$, InitialFoldr$
Dim wb As Workbook

Set wb = Workbooks("Main.xlsm")
Application.ScreenUpdating = False

InitialFoldr$ = "./" '<<< Startup folder to begin searching from

With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Please select a folder to list Files from"
.InitialFileName = InitialFoldr$
.Show
If .SelectedItems.Count <> 0 Then
xDirect$ = .SelectedItems(1) & "\"
End If
End With

If xDirect$ <> "" Then
With CreateObject("Scripting.FileSystemObject").GetFolder(xDirect$)
For Each vSF In .subfolders
Worksheets("Summary").Range("C5").Offset(xRow) = Mid(vSF, InStrRev(vSF, "\") + 1)
Worksheets("Summary").Range("AA5").Offset(xList) = vSF

Application.Workbooks.Open (vSF & "\Monthly engagement measure.xlsm"), ReadOnly:=True

'1:1 data
'create TL sheet
wb.Sheets.Add.Name = Mid(vSF, InStrRev(vSF, "\") + 1)

Set xMonthRange = Workbooks("Main.xlsm").Sheets("Summary").Range("E4")
Set xMDR = Workbooks("Main.xlsm").Sheets(Mid(vSF, InStrRev(vSF, "\") + 1)).Range("A1")
Set xDDR = Workbooks("Main.xlsm").Sheets(Mid(vSF, InStrRev(vSF, "\") + 1)).Range("A2:D50")

'Get the first month and put it in first column
Do Until xMonth = 15

wb.Sheets(Mid(vSF, InStrRev(vSF, "\") + 1)).Range(xMDR).Value = xMonthRange
wb.Sheets(Mid(vSF, InStrRev(vSF, "\") + 1)).Range(xDDR).Value = Workbooks("Monthly engagement measure.xlsm").Sheets(xMonthRange).Range("B6:E50").Value

xMonth = xMonth + 1
xMonthRange = xMonthRange.Offset(, 1)
xMDR = xMDR.Offset(, 5)
xDDR = xDDR.Offset(, 5)
Loop

xMonth = 0
xRow = xRow + 1
xList = xList + 1
Workbooks("Monthly engagement measure.xlsm").Close SaveChanges:=False
Next vSF
End With
I get the error on line:
wb.Sheets(Mid(vSF, InStrRev(vSF, "\") + 1)).Range(xMDR).Value = xMonthRange
Any ideas?

Kenneth Hobs
09-14-2012, 06:59 AM
Several things can cause that.
1. Your sheet name parsed does not exit.
2. Your range name does not exit.
3. Improper use of xMonthRange.

When dimming variables, if it is a variant, dim it as such. If it is a range, dim it as such.

Where is your Set command here?
xMonthRange = xMonthRange.Offset(, 1)

I don't reply on the default property for an object. This is clear:
MsgBox xRangeMonth.Value2

Use Debug.Print prior to that line to see what the sheet name and range addresses resolve to.
e.g.
[VBA]Debug.Print Mid(vSF, InStrRev(vSF, "\") + 1) /VBA]

I suspect that it is the xMonthRange issue though.

SBrooky
09-14-2012, 08:11 AM
Im sorry im not too sure I understand where ive gone wrong.

Set xMonthRange = Workbooks("Main.xlsm").Sheets("Summary").Range("E4")

In this cell (E4-T4) it says Sep-12, Oct-12, Nov-12, Dec-12 etc.

The cells are formatted as Text so not to convert it into a date etc.

SBrooky
09-14-2012, 08:14 AM
Ahh wait I think I see the issue im referencing one sheet then the range in a different one