A couple of easy questions...

[Jim Berglund]

1. I am using 2 worksheets in the same Excel XP file, (YTD BD, and Analyzer)
and swiching back and forth between them. I frequently get Error 1004 codes
when I'm trying to select a range. Could someone please tell me why?

The example follows the second question... (the error occurs between the
asterisks at the second select line). The problem began when I attempted to
merge two subroutines.
__________________________________________________________________________
2. If I wanted to use the following statement using 'With' and 'End With'
(and I really don't know why I would), is it necessary (or advisable) to
include the Sort statement between these?


Sheets("YTD BD").Range(Cells(2, 1), Cells(numberofRows,
numberofColumns)).Select
ActiveSheet.Range("A1", Cells(numberofRows, numberofColumns)).Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
as in...

With ActiveSheets
.Range(Cells(2, 1), Cells(numberofRows, numberofColumns)).Select

' should I put the 'End With' here?...
ActiveSheet.Range("A1", Cells(numberofRows, numberofColumns)).Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'or here?...


Many Thanks,

Jim Berglund

_________________________________________________________________________

Private Sub CommandButton4_Click() 'Load Files
Dim i, j, k, x, y, numberofRows As Integer, numberofColumns, nextCol As
Integer

Application.ScreenUpdating = False

'Clear Data Sheet - YTD BD
Workbooks("Replacement Strategy4.xls").Activate
Sheets("YTD BD").Activate
ActiveSheet.Columns("AA").Select
Selection.Clear
Selection.Delete Shift:=xlToLeft

'Import Data from Cognos-generated .xls files
Workbooks.Open Filename:= _
"D:\My Documents\ENRV5608\Reports\Equipment
Replacement\EMMSReplacement3(EqptData).xls"
Workbooks("EMMSReplacement3(EqptData).xls").Activate
Sheets("Equipment Data").Activate
ActiveSheet.Range("A1").Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
numberofColumns = ActiveCell.CurrentRegion.Columns.Count
With ActiveSheet
.Range(.Cells(1, 1), .Cells(numberofRows, numberofColumns)).Select
End With

nextCol = numberofColumns + 2
Selection.Copy
Windows("Replacement Strategy4.xls").Activate
Sheets("YTD BD").Activate
ActiveSheet.Range("A1").Select
ActiveSheet.Paste

Workbooks.Open Filename:= _
"D:\My Documents\ENRV5608\Reports\Equipment Replacement\EMMS Unit
Maintenance Costs(Breakdown)LTD.xls"
Sheets("Sheet1").Activate
ActiveSheet.Range("A1").Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
numberofColumns = ActiveCell.CurrentRegion.Columns.Count
With ActiveSheet
.Range(.Cells(1, 1), .Cells(numberofRows, numberofColumns)).Select
End With
Application.CutCopyMode = False
Selection.Copy
Windows("Replacement Strategy4.xls").Activate
Sheets("YTD BD").Activate
ActiveSheet.Columns(nextCol).Select
ActiveSheet.Paste
nextCol = nextCol + numberofColumns + 1

Workbooks.Open Filename:= _
"D:\My Documents\ENRV5608\Reports\Equipment Replacement\EMMS Unit
Maintenance Costs(Breakdown)YTD.xls"
Sheets("Sheet1").Activate
ActiveSheet.Range("A1").Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
numberofColumns = ActiveCell.CurrentRegion.Columns.Count
With ActiveSheet
.Range(.Cells(1, 1), .Cells(numberofRows, numberofColumns)).Select
End With
Application.CutCopyMode = False
Selection.Copy
Windows("Replacement Strategy4.xls").Activate
Sheets("YTD BD").Activate
ActiveSheet.Columns(nextCol).Select
ActiveSheet.Paste
nextCol = nextCol + numberofColumns + 1

Workbooks.Open Filename:= _
"D:\My Documents\ENRV5608\Reports\Equipment Replacement\Unit
Condition Report.xls"
Sheets("Sheet1").Activate
ActiveSheet.Range("A1").Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
numberofColumns = ActiveCell.CurrentRegion.Columns.Count
With ActiveSheet
.Range(.Cells(1, 1), .Cells(numberofRows, numberofColumns)).Select
End With
Application.CutCopyMode = False
Selection.Copy
Windows("Replacement Strategy4.xls").Activate
Sheets("YTD BD").Activate
ActiveSheet.Columns(nextCol).Select
ActiveSheet.Paste
nextCol = nextCol + numberofColumns + 1

Workbooks.Open Filename:= _
"D:\My Documents\ENRV5608\Reports\Equipment Replacement\EMMS LTD
Downtime.xls"
Sheets("Sheet1").Activate
ActiveSheet.Range("A1").Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
numberofColumns = ActiveCell.CurrentRegion.Columns.Count
With ActiveSheet
.Range(.Cells(1, 1), .Cells(numberofRows, numberofColumns)).Select
End With
Application.CutCopyMode = False
Selection.Copy
Windows("Replacement Strategy4.xls").Activate
Sheets("YTD BD").Activate
ActiveSheet.Columns(nextCol).Select
ActiveSheet.Paste
nextCol = nextCol + numberofColumns + 2
ActiveSheet.Rows("1:1").Select
Selection.Interior.ColorIndex = xlNone

Sheets("YTD BD").Select
ActiveSheet.Range("A63999").Select
Selection.Copy
ActiveSheet.Range("A1:AJ484").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

'Close original data files
Windows("EMMSReplacement3(EqptData).xls").Activate
ActiveWorkbook.Close
Windows("EMMS Unit Maintenance Costs(Breakdown)LTD.xls").Activate
ActiveWorkbook.Close
Windows("EMMS Unit Maintenance Costs(Breakdown)YTD.xls").Activate
ActiveWorkbook.Close
Windows("Unit Condition Report.xls").Activate
ActiveWorkbook.Close
Windows("EMMS LTD Downtime.xls").Activate
ActiveWorkbook.Close
'***************************************************************************
***********
Sheets("YTD BD").Activate 'Normalize
ActiveSheet.Range("A1").Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
numberofColumns = ActiveCell.CurrentRegion.Columns.Count
'The following is the problem line *******
Sheets("YTD BD").Range(Cells(2, 1), Cells(numberofRows,
numberofColumns)).Select
ActiveSheet.Range("A1", Cells(numberofRows, numberofColumns)).Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'***************************************************************************
************

ActiveSheet.Range(Cells(1, numberofColumns + 2), Cells(1,
numberofColumns + 2)).Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
ActiveSheet.Range(Cells(2, numberofColumns + 2), Cells(numberofRows,
numberofColumns + 6)).Select
Selection.Sort Key1:=Range(Cells(1, numberofColumns + 2), Cells(1,
numberofColumns + 2)), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

For i = 2 To numberofRows

With Worksheets("YTD BD").Range("A:A")
Set c = .Find(Cells(i, numberofColumns + 2).Value,
LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
j = Mid(c.Address, 4)
Do
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
Range(Cells(i, numberofColumns + 3), Cells(i,
numberofColumns + 6)).Select
Selection.Copy
Range(Cells(j, numberofColumns + 20), Cells(j,
numberofColumns + 23)).Select
ActiveSheet.Paste

End If
End With

Next

Range(Cells(numberofColumns + 8), Cells(numberofColumns + 8)).Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
Range(Cells(2, numberofColumns + 8), Cells(numberofRows,
numberofColumns + 12)).Select
Selection.Sort Key1:=Range(Cells(numberofColumns + 8),
Cells(numberofColumns + 12)), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

For i = 2 To numberofRows

With Worksheets("YTD BD").Range("A:A")
Set c = .Find(Cells(i, numberofColumns + 8).Value,
LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
j = Mid(c.Address, 4)
Do
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
Range(Cells(i, numberofColumns + 9), Cells(i,
numberofColumns + 12)).Select
Selection.Copy
Range(Cells(j, numberofColumns + 24), Cells(j,
numberofColumns + 27)).Select
ActiveSheet.Paste

End If
End With

Next

Range(Cells(numberofColumns + 14), Cells(numberofColumns +
14)).Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
Range(Cells(2, numberofColumns + 14), Cells(numberofRows,
numberofColumns + 15)).Select
Selection.Sort Key1:=Range(Cells(numberofColumns + 14),
Cells(numberofColumns + 14)), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

For i = 2 To numberofRows

With Worksheets("YTD BD").Range("A:A")
Set c = .Find(Cells(i, numberofColumns + 14).Value,
LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
j = Mid(c.Address, 4)
Do
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
Cells(i, numberofColumns + 15).Select
Selection.Copy
Cells(j, numberofColumns + 28).Select
ActiveSheet.Paste

End If
End With

Next

Range(Cells(numberofColumns + 17), Cells(numberofColumns +
17)).Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
Range(Cells(2, numberofColumns + 17), Cells(numberofRows,
numberofColumns + 18)).Select
Selection.Sort Key1:=Range(Cells(numberofColumns + 17),
Cells(numberofColumns + 17)), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

For i = 2 To numberofRows

With Worksheets("YTD BD").Range("A:A")
Set c = .Find(Cells(i, numberofColumns + 17).Value,
LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
j = Mid(c.Address, 4)
Do
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
Cells(i, numberofColumns + 18).Select
Selection.Copy
Cells(j, numberofColumns + 29).Select
ActiveSheet.Paste

End If
End With

Next
Sheets("YTD BD").Activate

'Create new Headings
Range(Cells(1, numberofColumns + 15), Cells(1, numberofColumns +
15)).Select
Application.CutCopyMode = False
Selection.Copy
Cells(1, numberofColumns + 28).Select
ActiveSheet.Paste
Range(Cells(1, numberofColumns + 18), Cells(1, numberofColumns +
18)).Select
Application.CutCopyMode = False
Selection.Copy
Cells(1, numberofColumns + 29).Select
ActiveSheet.Paste
Range(Cells(1, numberofColumns + 9), Cells(1, numberofColumns +
12)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Cells(1, numberofColumns + 24), Cells(1, numberofColumns +
27)).Select
ActiveSheet.Paste
Range(Cells(1, numberofColumns + 3), Cells(1, numberofColumns +
6)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Cells(1, numberofColumns + 20), Cells(1, numberofColumns +
23)).Select
ActiveSheet.Paste
ActiveSheet.Range("A1").Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
Range(Cells(1, numberofColumns + 1), Cells(numberofRows, numberofColumns
+ 19)).Select
Selection.Delete Shift:=xlToLeft


'Format Columns
' Columns("V:V").Select
' Selection.Copy
' Range(Cells(1, numberofColumns + 1), Cells(numberofRows,
numberofColumns + 20)).Select
' Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
' SkipBlanks:=False, Transpose:=False
' Application.CutCopyMode = False


'Insert and number a new top row
ActiveSheet.Range("A1").Select
numberofColumns = ActiveCell.CurrentRegion.Columns.Count
Rows("1:1").Select
Selection.Insert Shift:=xlDown
For i = 1 To numberofColumns
Cells(1, i).Value = i
Next

'Copy data into the Analyzer
Range(Cells(1, 1), Cells(numberofRows, numberofColumns)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

Selection.Copy
Sheets("Analyzer").Select
ActiveSheet.Range("A1").Select
ActiveSheet.Paste
Sheets("YTD BD").Select

'Fill Variables
'Set Data for SuperTypes
Sheets("YTD BD").Select
ActiveSheet.Columns("BA:BK").Clear 'Clear a WorkSpace

ActiveSheet.Columns("H:H").Select 'Equipment SuperTypes
Selection.Copy
Range("BA1").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
'Delete unnecessary Rows

'Sort Data and Create a new column with unique Supertype names
Selection.Sort Key1:=Range("BA1"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Range("BA1:BA2").Select
Selection.Delete Shift:=xlUp
i = 1
j = 1

Range("BA1").Select
numberofRows = Cells(1, 53).CurrentRegion.Rows.Count
Range("BA1").Select
Selection.Copy
Cells(j, 54).Select
ActiveSheet.Paste
Application.CutCopyMode = False

For i = 1 To numberofRows
If Cells(i, 53).Value = Cells(j, 54).Value Then
i = i + 1
Else
Cells(i + 1, 53).Select
Selection.Copy
Cells(j + 1, 54).Select
ActiveSheet.Paste
Application.CutCopyMode = False
i = i + 1
j = j + 1
End If
Next
Range("BC1").Select
numberofRows = Cells(1, 54).CurrentRegion.Rows.Count
Range(Cells(1, 54), Cells(1 + numberofRows, 54)).Select
Selection.Copy
Sheets("Analyzer").Activate
ActiveSheet.Cells(4, 115).Select
ActiveSheet.Paste
Application.CutCopyMode = False

'Copy (7) Supertype Names into the Appropriate Columns



With ActiveSheet
.Range("CX12D108").Select
Selection.ClearContents
i = 102
j = 4
For i = 102 To 109
Cells(12, i).Value = Cells(j, 115).Value
j = j + 1
Next
End With
'Set Data for Facilities
Sheets("YTD BD").Select
ActiveSheet.Columns("W:W").Select 'Facilities
Selection.Copy
Range("BD1").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False
Selection.Sort Key1:=Range("BD1"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("BD1").Select
Selection.Delete Shift:=xlUp
i = 1
j = 1

Range("BD1").Select
numberofRows = Cells(1, 56).CurrentRegion.Rows.Count
Range("BD1").Select
Selection.Copy
Cells(1, 57).Select
ActiveSheet.Paste
Application.CutCopyMode = False

For i = 1 To numberofRows
If Cells(i, 56).Value = Cells(j, 57).Value Then
i = i + 1
Else
Cells(i + 1, 56).Select
Selection.Copy
Cells(j + 1, 57).Select
ActiveSheet.Paste
Application.CutCopyMode = False
i = i + 1
j = j + 1
End If
Next
Range("BE1").Select
numberofRows = Cells(1, 57).CurrentRegion.Rows.Count
Range(Cells(1, 57), Cells(1 + numberofRows, 57)).Select
Selection.Copy
Sheets("Analyzer").Activate
ActiveSheet.Cells(6, 110).Select
ActiveSheet.Paste
Application.CutCopyMode = False


'Set Data for Equipment Types
Sheets("YTD BD").Select
ActiveSheet.Columns("G:G").Select
Selection.Copy
Range("BG1").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("BG2").Select
Selection.Delete Shift:=xlUp
Application.CutCopyMode = False

Selection.Sort Key1:=Range("BG3"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

i = 1
j = 1

Range("BH1").Select
numberofRows = Cells(1, 59).CurrentRegion.Rows.Count
Range("BH1").Select
Selection.Copy
Cells(1, 60).Select
ActiveSheet.Paste
Application.CutCopyMode = False

For i = 1 To numberofRows
If Cells(i, 59).Value = Cells(j, 60).Value Then
i = i + 1
Else
Cells(i + 1, 59).Select
Selection.Copy
Cells(j + 1, 60).Select
ActiveSheet.Paste
Application.CutCopyMode = False
i = i + 1
j = j + 1
End If
Next
Range("BH1").Select
numberofRows = Cells(1, 60).CurrentRegion.Rows.Count
Range(Cells(2, 60), Cells(1 + numberofRows, 60)).Select
Selection.Copy

Sheets("Analyzer").Activate
ActiveSheet.Cells(5, 112).Select
ActiveSheet.Paste
Application.CutCopyMode = False
'End Sub
Sheets("Analyzer").Activate
ActiveSheet.Range("CX13D23").Select
Selection.ClearContents
ActiveSheet.Range("DG5").Select

i = 5 'First Row for Supertypes
k = 13 'First Row of Eqpt Types
j = 102 'Columns of Eqpt Types
numberofRows = ActiveCell.CurrentRegion.Rows.Count

For j = 102 To 108
For i = 5 To numberofRows
If Cells(i, 111).Value = Cells(12, j).Value Then
Cells(k, j).Value = Cells(i, 112).Value
k = k + 1
End If
Next
k = 13
i = 5
Next

ActiveSheet.Range("DI5I15").Select 'Select Create Equipment Type List
Selector
Selection.Copy
ActiveSheet.Range("DL5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

ActiveSheet.Range("DF5F29").Select 'Select Create Facility List
Selector
Selection.Copy
ActiveSheet.Range("DM4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

End Sub

 

[Dave Peterson]

Without looking at all your code, it appears that your procedure may be behind a
worksheet.

If that's true, then an unqualified range will refer to the sheet holding the
code--not the activesheet.

This is different than the behavior you've seen in a general module.

That's why you'll want to fully qualify each range reference. And if find it
much easier to use the with/end with structure than doing all that typing:

sheets("ytd bd").select
sheets("ytd bd").range(sheets("ytd bd").cells(2,1), _
sheets("ytd bd").cells(numberofrows,numberofcolumns)).select

(you'll have to select "ytd bd" before you select anything on that sheet--but
it's not usually necessary to select anything.)

I think that this is easier to read/update (and copy to other procedures!):

with sheets("ytd bd")
.select
.range(.cells(2,1),.cells(numberofrows,numberofcolumns)).select
end with

but you could sort without selecting:

with sheets("ytd bd").range(.cells(2,1),.cells(numberofrows,numberofcolumns))
.sort key1:=.columns(1), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
end with

And if you ever run this code on versions of excel before 2002, you'll want to
drop the dataoption1 portion. (It'll cause errors in earlier versions, since it
was added in xl2002.)

 

[Jim Berglund]

Thanks, Dave. That makes sense. I'll give it a try...

Jim Berglund

 

[Jim Berglund]

It works! Thanks a lot!

BTW. I had not used the "." before Cells. That was a surprise. What's the
rule for doing that, please?

Jim

Thanks, Dave. That makes sense. I'll give it a try...

Jim Berglund

Without looking at all your code, it appears that your procedure may be behind a
worksheet.

If that's true, then an unqualified range will refer to the sheet

holding

the
code--not the activesheet.

This is different than the behavior you've seen in a general module.

That's why you'll want to fully qualify each range reference. And if

find

it
much easier to use the with/end with structure than doing all that typing:

sheets("ytd bd").select
sheets("ytd bd").range(sheets("ytd bd").cells(2,1), _
sheets("ytd bd").cells(numberofrows,numberofcolumns)).select

(you'll have to select "ytd bd" before you select anything on that sheet--but
it's not usually necessary to select anything.)

I think that this is easier to read/update (and copy to other procedures!):

with sheets("ytd bd")
.select
.range(.cells(2,1),.cells(numberofrows,numberofcolumns)).select
end with

but you could sort without selecting:

with sheets("ytd bd").range(.cells(2,1),.cells(numberofrows,numberofcolumns))
.sort key1:=.columns(1), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
end with

And if you ever run this code on versions of excel before 2002, you'll want to
drop the dataoption1 portion. (It'll cause errors in earlier versions, since it
was added in xl2002.)

attempted

to

'***************************************************************************'*************************************************************************** Type

 

[Tom Ogilvy]

cells is a range object. Same object, same rules.

--
Regards,
Tom Ogilvy

It works! Thanks a lot!

BTW. I had not used the "." before Cells. That was a surprise. What's the
rule for doing that, please?

Jim

Thanks, Dave. That makes sense. I'll give it a try...

Jim Berglund
be
behind a holding find versions,
since it 1004
codes attempted
__________________________________________________________________________

advisable)

to

Orientation:=xlTopToBottom,

_

Orientation:=xlTopToBottom,

_
_________________________________________________________________________

'*************************************************************************** Orientation:=xlTopToBottom,

_

'*************************************************************************** Orientation:=xlTopToBottom,

_

Orientation:=xlTopToBottom,

_

Orientation:=xlTopToBottom,

_

Orientation:=xlTopToBottom,

_

Operation:=xlNone,

_

Orientation:=xlTopToBottom,

_

Orientation:=xlTopToBottom,

_

Orientation:=xlTopToBottom,

 

[Tom Ogilvy]

Or maybe you forgot it. Is that you (back in 1998) using it?

--
Regards,
Tom Ogilvy

------------------------

Message 2 in thread
From: Thomas Ogilvy ([email protected])
Subject: Re: Altzheimer's code effect - Deleting a Row


View this article only
Newsgroups: microsoft.public.excel.programming
Date: 1998/06/05


Jim,
There doesn't appear to be anything wrong with your code. It appears you
have a non-numerical value in your data in column 1. When you get the type
mismatch, hit the debug button. Then go to the immediate window and type in
?rw.address - the address will indicate which row has the non-integer
address. Otherwise you can dimension your current variable to variant. You
might then want to dimension response as variant, or you could not delete a
row with a non-integer (passes isnumeric() test) value.

HTH,
Tom Ogilvy

Jim Berglund wrote in message ...

The following code used to work. Now I get a type Mismatch error on line 7*

Dim response As Integer, current As Integer
Worksheets("SavedData").Activate
Range("A2:BA1000").Select
ActiveCell.CurrentRegion.Select
response = InputBox("Enter Item Number to Delete", "Delete Row")
For Each rw In Sheets("SavedData").Cells(1,
1).CurrentRegion.Rows
* current = rw.Cells(1, 1).Value
If current = response Then rw.Delete
Next
Can anyone please explain why and how to fix it?

Thanks,
Jim Berglund

It works! Thanks a lot!

BTW. I had not used the "." before Cells. That was a surprise. What's the
rule for doing that, please?

Jim

Thanks, Dave. That makes sense. I'll give it a try...

Jim Berglund
be
behind a holding find versions,
since it 1004
codes attempted
__________________________________________________________________________

advisable)

to

Orientation:=xlTopToBottom,

_

Orientation:=xlTopToBottom,

_
_________________________________________________________________________

'*************************************************************************** Orientation:=xlTopToBottom,

_

'*************************************************************************** Orientation:=xlTopToBottom,

_

Orientation:=xlTopToBottom,

_

Orientation:=xlTopToBottom,

_

Orientation:=xlTopToBottom,

_

Operation:=xlNone,

_

Orientation:=xlTopToBottom,

_

Orientation:=xlTopToBottom,

_

Orientation:=xlTopToBottom,