running macro from 1 wks that manipulates another

[qerj]

I have been at this problem for too long...keep not getting the
results I need. I have a wks (ie: wks1) that has a commandbutton on
it that copies a 2nd wks (ie: wks2) to a 3rd (ie: wks3) so that I can
delete some rows not needed for a report and re-sort based on data in
1 column. The copy procedure works fine but when it looks thru the
data in column D for the string "series", it does not delete the rows.
The secondary procedure to sort works fine. What am I doing wrong?
Please Help...thx

here is the code for both steps:

Sub btnSortBySRC_Click()

Dim strMainWks As String
Dim strTmpWks As String
Dim wsCurWks As Worksheet
Dim rSortRange As Range

TakeFocusOnClick = False
strMainWks = "Main Data"
strTmpWks = "Sort Workspace by SRC"
ActiveWorkbook.Sheets(strMainWks).Activate
Sheets(strMainWks).Copy After:=Sheets(strMainWks)
ActiveSheet.Name = strTmpWks

Set wsCurWks = ActiveSheet

sDeleteSeriesHdr

wsCurWks.Columns("A:B").Delete 'delete 2 unwanted columns
Set rSortRange = wsCurWks.Range("A:U")

rSortRange.Sort Key1:=rSortRange.Columns(10), Order1:=xlAscending

End Sub

Sub sDeleteSeriesHdr() 'delete series header row

Dim rMyRange As Range
Dim rMyCell As Range
Dim rMyDeletion As Range
Application.ScreenUpdating = False
Set rMyRange = Range("d1:d150") ' check specific number of rows

For Each rMyCell In rMyRange
If InStr(rMyCell.Text, "Series") > 0 Then
'MsgBox ActiveCell.Text
If rMyDeletion Is Nothing Then
Set rMyDeletion = rMyCell
Else
Set rMyDeletion = Union(rMyCell, rMyDeletion)
End If
End If
Next
If Not (rMyDeletion Is Nothing) Then
rMyDeletion.EntireRow.Delete
End If
Application.ScreenUpdating = True

End Sub

 

[Dave Peterson]

Without testing, this line looks suspicious:
If InStr(rMyCell.Text, "Series") > 0 Then

This looks for an exact match.
If InStr(1, rMyCell.Text, "Series", vbTextCompare) > 0 Then
(I like to give the starting point, too.)


And a couple more things:

This line was a note to you.
TakeFocusOnClick = False

Rightclick on the commandbutton that runs this
(from the Control Toolbox toolbar, right?)
Select properties
and manually change that takefocusonclick property to false
(remove the line from your code.

And this line has an unqualified range. If you have the code under that
worksheet with the button, you may not get what you expect.

Set rMyRange = Range("d1:d150") ' check specific number of rows

Better to fully qualify it with the worksheet it belongs to:

One way is to pass it the worksheet when you call the function:
call sDeleteSeriesHdr(wscurwks)

In the sub:
Sub sDeleteSeriesHdr(wks As Worksheet)
....
Set rMyRange = wks.Range("d1:d150") ' check specific number of rows
....
end sub

 

[qerj]

Dave,

thx for the reply...it was the reference to the worksheet that fixed
it...passed the worksheet reference to the called procedure and it
worked like a charm. When I was debugging it, I had msgboxes that
gave me the impression that it did not need a specific reference to
the worksheet but...never take anything for granted...

Eric

 

[Dave Peterson]

I don't like a lot of things that are mandatory, but in this case, it seems
worth it to make the code work <vbg>.

Good thing you like it because, according to help, it is mandatory when you
specify the 4th argument.

from help on Instr
"The start argument is required if compare is specified."

compare is the 4th argument.

<<snipped>>

 

[qerj]

Tom...

saw your comment re: the start argument and the compare 4th
argument...glad I put both in ... I originally had no start but had
the compare in the general section as "Option Compare Text" but
changed it based on Dave's input...

I did check the help too ... you are correct!

Thx
Eric