Problem running Find code for different sheet
 

Here is a macro that works. It will find a record in a sheet named
Official List via a PO# given by the user.


Sub FindFirst()
'This is for the PO/PL search via UserForm12. Clicking the OK button
'brings you here. If record found, it opens up UserForm13 to show
'that record. The "Find Another Record" button will also loop back
here.

Set rngToSearch = Sheets("Official List").Columns("J")
Set rngFound = rngToSearch.Find(What:=FindPOVal, _
LookIn:=xlValues)

If rngFound Is Nothing Then
MsgBox "This record was not found. You might want to check the
Deleted List. Go to the Navigator for 'Find Deleted Record' button."

Else
strFirst = rngFound.Address
rngFound.Select
Unload UserForm12
UserForm13.Show

End If

End Sub

In the Declarations in the same module, I have this:
Private rngToSearch As Range
Private rngFound As Range
Public strFirst As String
Public FindPOVal As String

I wanted to duplicate this proceedure in another sub that will find a
record in a sheet named Deleted List, that is in the same workbook. It
will then open up different userforms than is shown above. (a different
look at the records)
Here is the Find code to look for a record in the Deleted List:

Sub FindDeleted()
'Same routine as FindFirst, except that this searches the Deleted List.

Set rngToSearch = Sheets("Deleted List").Columns("J")
Set rngFound = rngToSearch.Find(What:=FindPOVal, _
LookIn:=xlValues)

If rngFound Is Nothing Then
MsgBox "The record you requested was not found on this list."

Else
strFirst = rngFound.Address
rngFound.Select '<<<error is at this line
Unload UserForm14
UserForm15.Show

End If

End Sub

As you can see, then only thing different is the sheet name, and the
userforms involved.

I get an error in the Else section in the line
rngFound.Select

Does this error have to do with the Declarations statements? Is there a
problem with using these same statements to execute the Find in both
subs?
Anybody have a solution? I'm sure it's something simple. But, probably
about something I haven't learned yet.
Thanks
J.O.

Problem running Find code for different sheet
 

Sub FindDeleted()
'Same routine as FindFirst, except that this searches the Deleted List.

Set rngToSearch = Sheets("Deleted List").Columns("J")
Set rngFound = rngToSearch.Find(What:=FindPOVal, _
LookIn:=xlValues)

If rngFound Is Nothing Then
MsgBox "The record you requested was not found on this list."

Else
strFirst = rngFound.Address
Application.Goto rngFound, True '<<<error is at this line
Unload UserForm14
UserForm15.Show

End If

End Sub

Use GoTo since Deleted List probably isn't the activesheet.

--
Regards,
Tom Ogilvy


"excelnut1954" wrote in message
oups.com...
Here is a macro that works. It will find a record in a sheet named
Official List via a PO# given by the user.


Sub FindFirst()
'This is for the PO/PL search via UserForm12. Clicking the OK button
'brings you here. If record found, it opens up UserForm13 to show
'that record. The "Find Another Record" button will also loop back
here.

Set rngToSearch = Sheets("Official List").Columns("J")
Set rngFound = rngToSearch.Find(What:=FindPOVal, _
LookIn:=xlValues)

If rngFound Is Nothing Then
MsgBox "This record was not found. You might want to check the
Deleted List. Go to the Navigator for 'Find Deleted Record' button."

Else
strFirst = rngFound.Address
rngFound.Select
Unload UserForm12
UserForm13.Show

End If

End Sub

In the Declarations in the same module, I have this:
Private rngToSearch As Range
Private rngFound As Range
Public strFirst As String
Public FindPOVal As String

I wanted to duplicate this proceedure in another sub that will find a
record in a sheet named Deleted List, that is in the same workbook. It
will then open up different userforms than is shown above. (a different
look at the records)
Here is the Find code to look for a record in the Deleted List:

Sub FindDeleted()
'Same routine as FindFirst, except that this searches the Deleted List.

Set rngToSearch = Sheets("Deleted List").Columns("J")
Set rngFound = rngToSearch.Find(What:=FindPOVal, _
LookIn:=xlValues)

If rngFound Is Nothing Then
MsgBox "The record you requested was not found on this list."

Else
strFirst = rngFound.Address
rngFound.Select '<<<error is at this line
Unload UserForm14
UserForm15.Show

End If

End Sub

As you can see, then only thing different is the sheet name, and the
userforms involved.

I get an error in the Else section in the line
rngFound.Select

Does this error have to do with the Declarations statements? Is there a
problem with using these same statements to execute the Find in both
subs?
Anybody have a solution? I'm sure it's something simple. But, probably
about something I haven't learned yet.
Thanks
J.O.

Problem running Find code for different sheet
 

Thanks, Tom
That did the trick.

I've used the Goto command with good results. However, I posted some
lines of code here way back when I 1st came here. And, it had some Goto
commands in it.
Someone who has helped quite a bit made a remark that, in effect, that
using the Goto was not a good thing.
Today, I was reading a text during lunch, and there was a section
refering to the Goto command. It, too gave a negative impression of
using it.

Do you, or anyone else, have any comments on it?

In simple coding, I can't see it causing any harm. I'm thinking that in
more complex coding, it might be something that could be an issue. I'm
not sure, given my current level of experience.

Is it considered sloppy coding to use Goto in complex macros?
Why?

Thanks,
J.O.

Problem running Find code for different sheet
 

Different GOTO. They are talking about GOTO as a flow mechanism withing
your code

if i 1 then goto 100
if j < i then goto 50


Application.Goto is completely different. It is like a combination of

Worksheets("Sheet1").Activate
Range("B9").Select

--
Regards,
Tom Ogilvy



"excelnut1954" wrote in message
oups.com...
Thanks, Tom
That did the trick.

I've used the Goto command with good results. However, I posted some
lines of code here way back when I 1st came here. And, it had some Goto
commands in it.
Someone who has helped quite a bit made a remark that, in effect, that
using the Goto was not a good thing.
Today, I was reading a text during lunch, and there was a section
refering to the Goto command. It, too gave a negative impression of
using it.

Do you, or anyone else, have any comments on it?

In simple coding, I can't see it causing any harm. I'm thinking that in
more complex coding, it might be something that could be an issue. I'm
not sure, given my current level of experience.

Is it considered sloppy coding to use Goto in complex macros?
Why?

Thanks,
J.O.