Hi I have following code to search and for specific words and then delete the
rows between ( including them) those rows.
This macro works fine on a XP machine, but not working properly on Vista.

While debugging the code by €œstep into€�, I realized the €œWS. Find€� function
not finding the rows, even though those words are exist in the first column.
I am trying modify the code to other than €œWS.columns.find€� function, but
can you help me to find out why it is not working on Vista, the way it is
supposed to be?
thank you so much for your help
########
Public Sub Paste_Transpose_Specific()
Dim WS As Worksheet
Dim Trialrow, Summaryrow As Range
On Error Resume Next

For Each WS In ActiveWorkbook.Worksheets
With WS
Set Trialrow = WS.Columns(1).Find("Sample", Range("A1"),
SearchOrder:=xlByRows, SearchDirection:=xlNext)

Set Summaryrow = WS.Columns(1).Find("Total:", Range("A1"),
SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

If Not (Trialrow Is Nothing) And Not (Summaryrow Is Nothing) Then _
WS.Rows(Trialrow.Row & ":" & Summaryrow.Row).Delete


End With


Next WS
On Error GoTo 0

The problem probably isn't the difference between 2003 and 2007. The
find function uses the same function as the worksheet find (2003 menu
Edit - find). the find doesn't have a default value for some of the
parameters. the find uses whatever was last set in the worksheet. So
you probably just have to add all the parameters in the find function.
OPen up the worksheet find and look at the option. You will see that it
is set incrorrectly.

expression.Find(What, After, LookIn, LookAt, SearchOrder,
SearchDirection, MatchCase, MatchByte, SearchFormat)


Usually as a mimimum you need to specfiy the

LookIn:=xlvalues, lookat:=xlwhole, MatchCase:=False

Lookat can also be xlpart


The most common failure like this is the Part vs Whole


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=149293

additionally change this
Dim Trialrow, Summaryrow As Range

to
Dim Trialrow As Range, Summaryrow As Range


in your code, Trialrow is variant by default since you need to explicitly
set its type. However, it doesn't affect your code, I'm just being picky :)

"Sheela" wrote:

Hi I have following code to search and for specific words and then delete the
rows between ( including them) those rows.
This macro works fine on a XP machine, but not working properly on Vista.

While debugging the code by €œstep into€�, I realized the €œWS. Find€� function
not finding the rows, even though those words are exist in the first column.
I am trying modify the code to other than €œWS.columns.find€� function, but
can you help me to find out why it is not working on Vista, the way it is
supposed to be?
thank you so much for your help
########
Public Sub Paste_Transpose_Specific()
Dim WS As Worksheet
Dim Trialrow, Summaryrow As Range
On Error Resume Next

For Each WS In ActiveWorkbook.Worksheets
With WS
Set Trialrow = WS.Columns(1).Find("Sample", Range("A1"),
SearchOrder:=xlByRows, SearchDirection:=xlNext)

Set Summaryrow = WS.Columns(1).Find("Total:", Range("A1"),
SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

If Not (Trialrow Is Nothing) And Not (Summaryrow Is Nothing) Then _
WS.Rows(Trialrow.Row & ":" & Summaryrow.Row).Delete


End With


Next WS
On Error GoTo 0

Thank you so much for your help, it worked.
sheela
"joel" wrote:


The problem probably isn't the difference between 2003 and 2007. The
find function uses the same function as the worksheet find (2003 menu
Edit - find). the find doesn't have a default value for some of the
parameters. the find uses whatever was last set in the worksheet. So
you probably just have to add all the parameters in the find function.
OPen up the worksheet find and look at the option. You will see that it
is set incrorrectly.

expression.Find(What, After, LookIn, LookAt, SearchOrder,
SearchDirection, MatchCase, MatchByte, SearchFormat)


Usually as a mimimum you need to specfiy the

LookIn:=xlvalues, lookat:=xlwhole, MatchCase:=False

Lookat can also be xlpart


The most common failure like this is the Part vs Whole


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=149293

.

I did not know that defining variables in one row vs. separate rows makes a
difference.

I changed the all the definitions in my code each one in a separate line.
thank you.

sheela

"Patrick Molloy" wrote:

additionally change this
Dim Trialrow, Summaryrow As Range

to
Dim Trialrow As Range, Summaryrow As Range


in your code, Trialrow is variant by default since you need to explicitly
set its type. However, it doesn't affect your code, I'm just being picky :)

"Sheela" wrote:

Hi I have following code to search and for specific words and then delete the
rows between ( including them) those rows.
This macro works fine on a XP machine, but not working properly on Vista.

While debugging the code by €œstep into€�, I realized the €œWS. Find€� function
not finding the rows, even though those words are exist in the first column.
I am trying modify the code to other than €œWS.columns.find€� function, but
can you help me to find out why it is not working on Vista, the way it is
supposed to be?
thank you so much for your help
########
Public Sub Paste_Transpose_Specific()
Dim WS As Worksheet
Dim Trialrow, Summaryrow As Range
On Error Resume Next

For Each WS In ActiveWorkbook.Worksheets
With WS
Set Trialrow = WS.Columns(1).Find("Sample", Range("A1"),
SearchOrder:=xlByRows, SearchDirection:=xlNext)

Set Summaryrow = WS.Columns(1).Find("Total:", Range("A1"),
SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

If Not (Trialrow Is Nothing) And Not (Summaryrow Is Nothing) Then _
WS.Rows(Trialrow.Row & ":" & Summaryrow.Row).Delete


End With


Next WS
On Error GoTo 0

you can certainly dimension variables in a row..

DIM a as long, b as long, c as long

is the same as

DIM a as long
DIM b as long
DIM c as long

but
DIM a, b, c as long

only dims the last variable,c, explcitly as long. Both a and b are variants
since they are not explicitly typed.




"Sheela" wrote:

I did not know that defining variables in one row vs. separate rows makes a
difference.

I changed the all the definitions in my code each one in a separate line.
thank you.

sheela

"Patrick Molloy" wrote:

additionally change this
Dim Trialrow, Summaryrow As Range

to
Dim Trialrow As Range, Summaryrow As Range


in your code, Trialrow is variant by default since you need to explicitly
set its type. However, it doesn't affect your code, I'm just being picky :)

"Sheela" wrote:

Hi I have following code to search and for specific words and then delete the
rows between ( including them) those rows.
This macro works fine on a XP machine, but not working properly on Vista.

While debugging the code by €œstep into€�, I realized the €œWS. Find€� function
not finding the rows, even though those words are exist in the first column.
I am trying modify the code to other than €œWS.columns.find€� function, but
can you help me to find out why it is not working on Vista, the way it is
supposed to be?
thank you so much for your help
########
Public Sub Paste_Transpose_Specific()
Dim WS As Worksheet
Dim Trialrow, Summaryrow As Range
On Error Resume Next

For Each WS In ActiveWorkbook.Worksheets
With WS
Set Trialrow = WS.Columns(1).Find("Sample", Range("A1"),
SearchOrder:=xlByRows, SearchDirection:=xlNext)

Set Summaryrow = WS.Columns(1).Find("Total:", Range("A1"),
SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

If Not (Trialrow Is Nothing) And Not (Summaryrow Is Nothing) Then _
WS.Rows(Trialrow.Row & ":" & Summaryrow.Row).Delete


End With


Next WS
On Error GoTo 0

Patrick Molloy: I don't think Sheela was refering to the Dim statements.
Sheela was refering to the properties of the VBA Find. That setting
the properties in one Find to (ie xlpart) means that the following Find
statments would also use xlpart not xlwhole. VBA find is unlike other
methods that is doesn't use a default setting, instead it use the last
setting which could also include any time the FIND is used in the
worksheet menu.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=149293

<a href="http://www.thecodecage.com">Microsoft Office Help</a>

I think she was Patrick!
Paul

On Nov 2, 1:25*pm, joel wrote:
Patrick Molloy: I don't think Sheela was refering to the Dim statements.
Sheela was refering to the properties of the VBA Find. *That setting
the properties in one Find to (ie xlpart) means that the following Find
statments would also use xlpart not xlwhole. *VBA find is unlike other
methods that is doesn't use a default setting, instead it use the last
setting which could also include any time the FIND is used in the
worksheet menu.

--
joel
------------------------------------------------------------------------
joel's Profile:http://www.thecodecage.com/forumz/member.php?userid=229
View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=149293

<a href="http://www.thecodecage.com"MicrosoftOffice Help</a>