PDA

View Full Version : Case When error for set values



Johnatha
10-23-2014, 11:41 AM
HI!

I'm having a weird problem.. I'm trying to look through a number of different columns for a certain value. If that value is there, then I want to copy over a certain range of cells into a new workbook. Some columns might contain values while other columns don't, and vice versa.

I've discovered my problem... If the value (example, 32) is NOT in the first column being searched, the rest of my case statements don't work, and I get the error "Run-time error '1004' Application-defined or object-defined error". See a sample of my code below (I have 20 cases total, and several lines of code before/after this; it's just a sample)


Case 1
Set NewBook = Workbooks.Add
Workbooks.Open "M:\John\" & FName
With Worksheets(WorkSheetPull)
.AutoFilterMode = False
.Cells(1).AutoFilter Field:=4, Criteria1:=32
If .AutoFilter.Range.Rows.Count > 1 Then
.UsedRange.Columns(1).Offset(1).Resize(, 3).SpecialCells(12).Copy NewBook.Worksheets("Sheet1").Cells(1)
.UsedRange.Columns(4).Offset(1).Resize(, 2).SpecialCells(12).Copy NewBook.Worksheets("Sheet1").Cells(4)
End If
End With

Case 2
Workbooks(FName).Activate
With Worksheets(WorkSheetPull)
.AutoFilterMode = False
.Cells(1).AutoFilter Field:=6, Criteria1:=32
If .AutoFilter.Range.Rows.Count > 1 Then
.UsedRange.Columns(1).Offset(1).Resize(, 3).SpecialCells(12).Copy NewBook.Worksheets("Sheet1").Cells(1).End(xlDown).Offset(1)
.UsedRange.Columns(6).Offset(1).Resize(, 2).SpecialCells(12).Copy NewBook.Worksheets("Sheet1").Cells(4).End(xlDown).Offset(1)
End If
End With

Case 3
Workbooks(FName).Activate
With Worksheets(WorkSheetPull)
.AutoFilterMode = False
.Cells(1).AutoFilter Field:=8, Criteria1:=32
If .AutoFilter.Range.Rows.Count > 1 Then
.UsedRange.Columns(1).Offset(1).Resize(, 3).SpecialCells(12).Copy NewBook.Worksheets("Sheet1").Cells(1).End(xlDown).Offset(1, 0)
.UsedRange.Columns(8).Offset(1).Resize(, 2).SpecialCells(12).Copy NewBook.Worksheets("Sheet1").Cells(4).End(xlDown).Offset(1, 0)
End If
End With


If I set my criteria to a number that IS in the 1st column (example, 24) then it works!

I'm realy stuck, any thoughts would be helpful!

Thanks.

mancubus
10-23-2014, 11:24 PM
hi.

NewBook is set only in Case 1.
if it will be used for all cases, move it above Case 1 line.
otherwise copy and paste that line after relevant case lines.

SamT
10-24-2014, 09:39 AM
I think you can move all the below to before the Select Case

Set NewBook = Workbooks.Add
Workbooks.Open "M:\John\" & FName
With Worksheets(WorkSheetPull)

Johnatha
10-24-2014, 10:19 AM
Hi mancubus, SamT,

Thanks for the help but that still doesn't solve my problem :crying:

Even when I put that line before the case statement, for certain integer values (example, 32) it always gives me that error message on "Case 2" at line " .UsedRange.Columns(1).Offset(1).Resize(, 3).SpecialCells(12).Copy NewBook.Worksheets("Sheet1").Cells(1).End(xlDown).Offset(1)"

The only thing I can think of why is because the values (example, 32) are NOT in the first column, or the first Case statement. Any thoughts would help out a ton.

Thanks!

mancubus
10-24-2014, 04:34 PM
why not post entire code and workbook here?

SamT
10-24-2014, 08:02 PM
I wonder why people ALWAYS leave out the most important part of the code. In this case, the code that sets the Select Case variable variable.

I see the problem! In every case the criteria is hardcoded to "32," there fore you must insure that in every case the column has "32" in it.

Case Solved :D

I am totally guessing about what code you have and what is raising the error, but...

Set NewBook = Workbooks.Add
Workbooks.Open "M:\John\" & FName
With Worksheets(WorkSheetPull)
.AutoFilterMode = False

Select Case Johnatha
Case 1
If Not .Columns(4).Find(32) Is Nothing Then
.Cells(1).AutoFilter Field:=4, Criteria1:=32
If .AutoFilter.Range.Rows.Count > 1 Then
.UsedRange.Columns(1).Offset(1).Resize(, 3).SpecialCells(12).Copy NewBook.Worksheets("Sheet1").Cells(1)
.UsedRange.Columns(4).Offset(1).Resize(, 2).SpecialCells(12).Copy NewBook.Worksheets("Sheet1").Cells(4)
End If
End If
Case 2
If Not .Columns(6).Find(32) Is Nothing Then
'Etc