error 1004 Select method of Range class failed
 

If Sheet5 is not the active sheet then the Select will fail, since a
cell can only be selected if the parent sheet is active.

I rearranged what you did a bit to make the dependency on Sheet5 a
little clearer and eliminate some unnecessary intermediate variables:

With Worksheets("Sheet5")
Set rng = .Columns(1).Cells.Find(What:=ExcelDate, _
After:=.Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
If rng Is Nothing Then
Set rng = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
rng.Value = ExcelDate
End If
With rng.Offset(0, 1).Resize(1, 2)
.Item(1).Value = dblPlannedScrapValueAdded
.Item(2).Value = dblActualScrapValueAdded
End With
End With

Note that you almost never need to select or activate a range in
order to work with it. Using the range object directly makes your
code smaller, faster and, IMO, easier to maintain.

In article ,
"Matt." wrote:

Hi all!

This is a stupid problem, but I'm banging my head.

I want to search a column for a value in a variable (ExcelDate). If the
value is not found, I want to go to the last row in the spreadsheet and
insert some data. If it is found, I want to overwrite the values with some
data.

My Select method is failing with the error in the subject line.

Any help appreciated.

cheers,
Matt.

Set rng = Worksheets("Sheet5").Cells.Find(What:=ExcelDate,
After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False)
If rng Is Nothing Then
Worksheets("Sheet5").Cells(1, 1).Select ''''' this line fails
Selection.End(xlDown).Activate
intCurRow = ActiveCell.Row
intCurCol = ActiveCell.Column
Worksheets("Sheet5").Cells(intCurRow, 1).Value = ExcelDate
Worksheets("Sheet5").Cells(intCurRow, 2).Value =
dblPlannedScrapValueAdded
Worksheets("Sheet5").Cells(intCurRow, 3).Value =
dblActualScrapValueAdded
Else
intCurRow = rng.Row
intCurCol = rng.Column
Worksheets("Sheet5").Cells(intCurRow, 2).Value =
dblPlannedScrapValueAdded
Worksheets("Sheet5").Cells(intCurRow, 3).Value =
dblActualScrapValueAdded
End If

error 1004 Select method of Range class failed
 

Thank you J.E.

Before I read your solution, my solution to the problem is below.

I will attempt to use the objects more efficiently at a later date.

Thanks again!

cheers,
Matt.

Set rng = Worksheets("Sheet5").Cells.Find(What:=ExcelDate,
After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False)
If rng Is Nothing Then
Worksheets("Sheet5").Activate
Cells(1, 1).Select
If Cells(1, 1).Value 0 Then
If Cells(2, 1).Value 0 Then
Selection.End(xlDown).Activate
Cells(ActiveCell.Row + 1, 1).Select
Else
Cells(2, 1).Select
End If
End If
intCurRow = ActiveCell.Row
intCurCol = ActiveCell.Column
Worksheets("Sheet5").Cells(intCurRow, 1).Value = ExcelDate
Worksheets("Sheet5").Cells(intCurRow, 2).Value =
dblPlannedScrapValueAdded
Worksheets("Sheet5").Cells(intCurRow, 3).Value =
dblActualScrapValueAdded
Else
intCurRow = rng.Row
intCurCol = rng.Column
Worksheets("Sheet5").Cells(intCurRow, 2).Value =
dblPlannedScrapValueAdded
Worksheets("Sheet5").Cells(intCurRow, 3).Value =
dblActualScrapValueAdded
End If


"J.E. McGimpsey" wrote in message
...
If Sheet5 is not the active sheet then the Select will fail, since a
cell can only be selected if the parent sheet is active.

I rearranged what you did a bit to make the dependency on Sheet5 a
little clearer and eliminate some unnecessary intermediate variables:

With Worksheets("Sheet5")
Set rng = .Columns(1).Cells.Find(What:=ExcelDate, _
After:=.Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
If rng Is Nothing Then
Set rng = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
rng.Value = ExcelDate
End If
With rng.Offset(0, 1).Resize(1, 2)
.Item(1).Value = dblPlannedScrapValueAdded
.Item(2).Value = dblActualScrapValueAdded
End With
End With

Note that you almost never need to select or activate a range in
order to work with it. Using the range object directly makes your
code smaller, faster and, IMO, easier to maintain.

In article ,
"Matt." wrote:

Hi all!

This is a stupid problem, but I'm banging my head.

I want to search a column for a value in a variable (ExcelDate). If the
value is not found, I want to go to the last row in the spreadsheet and
insert some data. If it is found, I want to overwrite the values with
some
data.

My Select method is failing with the error in the subject line.

Any help appreciated.

cheers,
Matt.

Set rng = Worksheets("Sheet5").Cells.Find(What:=ExcelDate,
After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False)
If rng Is Nothing Then
Worksheets("Sheet5").Cells(1, 1).Select ''''' this line fails
Selection.End(xlDown).Activate
intCurRow = ActiveCell.Row
intCurCol = ActiveCell.Column
Worksheets("Sheet5").Cells(intCurRow, 1).Value = ExcelDate
Worksheets("Sheet5").Cells(intCurRow, 2).Value =
dblPlannedScrapValueAdded
Worksheets("Sheet5").Cells(intCurRow, 3).Value =
dblActualScrapValueAdded
Else
intCurRow = rng.Row
intCurCol = rng.Column
Worksheets("Sheet5").Cells(intCurRow, 2).Value =
dblPlannedScrapValueAdded
Worksheets("Sheet5").Cells(intCurRow, 3).Value =
dblActualScrapValueAdded
End If