Drop-down in FIND dialog

[MikeF]

Have some code - as below - that looks up the first occurence of a specific
string in Column D, then acts on it.

hat I'm more than desperate for is a drop-down that enables me to CHOOSE
which string to find in the line Cells.Find(What:="-02"

The drop-down list is "-01" / "-02" / "-03" / and so on, anywhere up to
"-99", and comes from the range "AuditIDs".

It can be a UserForm, or even the Find dialog box [must be the one that
comes up with CTRL-F, as xlDialogFormulaFind.show does not work], or even an
InputBox, as long as whatever value I choose, or even type, fills in the
What:= in the following code.

Have tried a lot of different things, and am stumped.

Disregard the rest of the sub, it's working perfectly, other than of course,
the objective herein re a drop-down for Cells.Find(What:=

Thanx sincerely in advance.
Regards,
- Mike


Sub FindMyCell()

Dim myCell As Range

Range("D7").Select
Cells.Find(What:="-02", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Set myCell = ActiveCell
If Len(myCell.Offset(0, 3)) = 0 Then
Set myCell = ActiveCell.Offset(2, 0)
Else: Set myCell = ActiveCell
End If

myCell.Activate
End Sub

 

[OssieMac]

Hi Mike,

I am assuming that AudiIds is a named range. If so then create a dropdown
list from it.

Select any vacant cell
Select Data -> Validation
On the Settings tab in Allow field select List
In the Source field insert =AuditIDs (Equal sign essential)
Click OK.

Now each time you select that cell the drop down arrow will appear and you
can select a value.

Change your code as follows. I have used Sheet1 cell A1 for the drop down.
Edit this to suit your worksheet.

Your find code will produce an error if it does not find the target. Of
course if you know that it will always find the target then this might not
course you a problem but if you want some help improving this then let me
know.

Also it is possible to force the code to execute each time you make a
selection from the dropdown so if you want that then let me know.

The following is the code to incorporate the dropdown result into your code.

Sub FindMyCell()

Dim myCell As Range
Dim strTofind As String

strTofind = Sheets("Sheet1").Range("A1")

Range("D7").Select
Cells.Find(What:=strTofind, After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:= _
False, SearchFormat:=False).Activate
Set myCell = ActiveCell
If Len(myCell.Offset(0, 3)) = 0 Then
Set myCell = ActiveCell.Offset(2, 0)
Else: Set myCell = ActiveCell
End If

myCell.Activate
End Sub

 

[MikeF]

Thanx for the reply.
This doesn't exactly work.
The drop-down needs to appear *as the code is running*.
It will run multiple times, and I need to choose the audit number on the
fly, not pre-set it.
Is that possible?
Thanx again.
- Mike

Hi,

Here the code.

You can do the dropdown by putting a Validation on a cell.
Select a cell (in this code I use "K1")
Menu: Data->Validation
Allow: "List"
Source: Select your range (AuditIDs)

Then here the code:
The code assume your dropdown(validation cell) is K1

Sub FindMyCell()
Dim myCell As Range
Dim StrID As String

StrID = Range("K1")

Columns("D").Select
Set myCell = Selection.Find(What:=StrID, After:=ActiveCell,
LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False)

If Not myCell Is Nothing Then
myCell.Offset(0, 3).Select
If Len(myCell.Offset(0, 3)) = 0 Then
Set myCell = ActiveCell.Offset(2, 0)
Else: Set myCell = ActiveCell
End If
myCell.Activate
Else
MsgBox "No Match."
End If
End Sub

Charlie
'Opener Consulting Home' (http://www.openerconsulting.com)

Have some code - as below - that looks up the first occurence of a
specific
string in Column D, then acts on it.

hat I'm more than desperate for is a drop-down that enables me to
CHOOSE
which string to find in the line Cells.Find(What:="-02"

The drop-down list is "-01" / "-02" / "-03" / and so on, anywhere up
to
"-99", and comes from the range "AuditIDs".

It can be a UserForm, or even the Find dialog box [must be the one
that
comes up with CTRL-F, as xlDialogFormulaFind.show does not work], or
even an
InputBox, as long as whatever value I choose, or even type, fills in
the
What:= in the following code.

Have tried a lot of different things, and am stumped.

Disregard the rest of the sub, it's working perfectly, other than of
course,
the objective herein re a drop-down for Cells.Find(What:=

Thanx sincerely in advance.
Regards,
- Mike


Sub FindMyCell()

Dim myCell As Range

Range("D7").Select
Cells.Find(What:="-02", After:=ActiveCell, LookIn:=xlValues, LookAt:=
_
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Set myCell = ActiveCell
If Len(myCell.Offset(0, 3)) = 0 Then
Set myCell = ActiveCell.Offset(2, 0)
Else: Set myCell = ActiveCell
End If

myCell.Activate
End Sub

 

[MikeF]

OssieMac,
Thanx for the reply, both to you and Charlie who also proposed a similiar
solution.
Contrary to what I just sent to Charlie, your solution do work.

I just wasn't prepared to pre-set the audit number prior to initiating the
sub, but again --- that does work, and certainly will suffice.

But ideally, the drop-down box would appear/pop-up *as the code is running*,
specifically upon hitting the "Cells.Find(What:" line, thereby enabling
the user to choose the AuditID "on the fly".

Can this be accomplished?

Regards,
- Mike

 

[MikeF]

Charlie,
Pardon me, in an attempt to clarify ...

The FindMyCell() sub is a small but ultimately important part of a much
larger sub that performs numerous tasks on various parts of an audit.

When I initiate that larger sub, the objective is that a UserForm will
appear [or anything of that nature which allows me to choose the audit
number], ask WHICH audit to perform those tasks on, I tell it via a drop-down
which lists the available audits [again -- 01/02/03 and so on], and it will
then go ahead with its tasks, appropriately starting at "myCell" because the
smaller FindMyCell() sub has properly defined where the myCell "anchor" is.

Let me know if that helps, and thanx again.
Regards,
- Mike

Not sure what you mean by *as the code is running*

Do you mean the macro need to be run throught the complete list of
Audit number in one call?

Right now this won't do much, your macro only select a cell.

Charlie
'Opener Consulting Home' (http://www.openerconsulting.com)

Thanx for the reply.
This doesn't exactly work.
The drop-down needs to appear *as the code is running*.
It will run multiple times, and I need to choose the audit number on
the
fly, not pre-set it.
Is that possible?
Thanx again.
- Mike

Hi,

Here the code.

You can do the dropdown by putting a Validation on a cell.
Select a cell (in this code I use "K1")
Menu: Data->Validation
Allow: "List"
Source: Select your range (AuditIDs)

Then here the code:
The code assume your dropdown(validation cell) is K1

Sub FindMyCell()
Dim myCell As Range
Dim StrID As String

StrID = Range("K1")

Columns("D").Select
Set myCell = Selection.Find(What:=StrID, After:=ActiveCell,
LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False)

If Not myCell Is Nothing Then
myCell.Offset(0, 3).Select
If Len(myCell.Offset(0, 3)) = 0 Then
Set myCell = ActiveCell.Offset(2, 0)
Else: Set myCell = ActiveCell
End If
myCell.Activate
Else
MsgBox "No Match."
End If
End Sub

Charlie
'Opener Consulting Home' ('Opener Consulting Home' (http://www.openerconsulting.com))


MikeF;175971 Wrote:
Have some code - as below - that looks up the first occurence of a
specific
string in Column D, then acts on it.

hat I'm more than desperate for is a drop-down that enables me to
CHOOSE
which string to find in the line Cells.Find(What:="-02"

The drop-down list is "-01" / "-02" / "-03" / and so on, anywhere up
to
"-99", and comes from the range "AuditIDs".

It can be a UserForm, or even the Find dialog box [must be the one
that
comes up with CTRL-F, as xlDialogFormulaFind.show does not work], or
even an
InputBox, as long as whatever value I choose, or even type, fills in
the
What:= in the following code.

Have tried a lot of different things, and am stumped.

Disregard the rest of the sub, it's working perfectly, other than of
course,
the objective herein re a drop-down for Cells.Find(What:=

Thanx sincerely in advance.
Regards,
- Mike


Sub FindMyCell()

Dim myCell As Range

Range("D7").Select
Cells.Find(What:="-02", After:=ActiveCell, LookIn:=xlValues, LookAt:=
_
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Set myCell = ActiveCell
If Len(myCell.Offset(0, 3)) = 0 Then
Set myCell = ActiveCell.Offset(2, 0)
Else: Set myCell = ActiveCell
End If

myCell.Activate
End Sub


--
Charlie
------------------------------------------------------------------------
Charlie's Profile: 'The Code Cage Forums - View Profile: Charlie' (http://www.thecodecage.com/forumz/member.php?userid=89)
View this thread: 'Drop-down in FIND dialog - The Code Cage Forums' (http://www.thecodecage.com/forumz/showthread.php?t=48685)

 

[MikeF]

Is there any way I can "pop-up" a UserForm with comboBox for drop-down, and
tie that to StrID?

Hi,

This will call an Pop up box to ask the user and input:
StrID = InputBox("Enter an Audit ID number:")
Unfortunately, it's not a dropdown, they need to type it.


Sub FindMyCell()
Dim myCell As Range
Dim StrID As String

StrID = InputBox("Enter an Audit ID number:")


Columns("D").Select
Set myCell = Selection.Find(What:=StrID, After:=ActiveCell,
LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False)

If Not myCell Is Nothing Then
myCell.Offset(0, 3).Select
If Len(myCell.Offset(0, 3)) = 0 Then
Set myCell = ActiveCell.Offset(2, 0)
Else: Set myCell = ActiveCell
End If
myCell.Activate
Else
MsgBox "No Match."
End If
End Sub

OssieMac,
Thanx for the reply, both to you and Charlie who also proposed a
similiar
solution.
Contrary to what I just sent to Charlie, your solution do work.

I just wasn't prepared to pre-set the audit number prior to initiating
the
sub, but again --- that does work, and certainly will suffice.

But ideally, the drop-down box would appear/pop-up *as the code is
running*,
specifically upon hitting the "Cells.Find(What:" line, thereby
enabling
the user to choose the AuditID "on the fly".

Can this be accomplished?

Regards,
- Mike

Hi Mike,

I am assuming that AudiIds is a named range. If so then create a dropdown
list from it.

Select any vacant cell
Select Data -> Validation
On the Settings tab in Allow field select List
In the Source field insert =AuditIDs (Equal sign essential)
Click OK.

Now each time you select that cell the drop down arrow will appear and you
can select a value.

Change your code as follows. I have used Sheet1 cell A1 for the drop down.
Edit this to suit your worksheet.

Your find code will produce an error if it does not find the target. Of
course if you know that it will always find the target then this might not
course you a problem but if you want some help improving this then let me
know.

Also it is possible to force the code to execute each time you make a
selection from the dropdown so if you want that then let me know.

The following is the code to incorporate the dropdown result into your code.

Sub FindMyCell()

Dim myCell As Range
Dim strTofind As String

strTofind = Sheets("Sheet1").Range("A1")

Range("D7").Select
Cells.Find(What:=strTofind, After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:= _
False, SearchFormat:=False).Activate
Set myCell = ActiveCell
If Len(myCell.Offset(0, 3)) = 0 Then
Set myCell = ActiveCell.Offset(2, 0)
Else: Set myCell = ActiveCell
End If

myCell.Activate
End Sub