UserForm assigns cell address to Variable

[MikeF]

UserForm.show is frmAuditID.

Column D contains a few hundred instances of "AuditID-01" and then
"AuditID-02", sorted in ascending order [ie all instances of 01 are first].
Sometimes there are many more, ie AuditID-03 and so on, but always sorted in
ascending order.
A list of these, however many there may be, appears in frmAuditID.

The objective is to have frmAuditID appear at the start of a sub, I choose -
say "AuditID-02" - and it assigns the cell address of wherever the *first*
occurence of "AuditID-02" is in column d to a variable named "myCell".
Sometimes "AuditID-02" will be in cell d44, sometimes in cell d233,
sometimes, in d411, and so on.

Note - the "finding" part of this can be manually accomplished by selecting
cell d7, CTRL-F, search sheet/columns/values and then just punching -01 or
-02 or -03, etc ....

** Once myCell is given the appropriate address, another subroutine will
then run, using the base address of "myCell" to navigate from.

Any assistance will be *sincerely* appreciated.

Thanx in advance,
- Mike

 

[JLGWhiz]

Maybe this:

Sub findIt()
Dim lr As Long, srchRng As Range, myCell As String
lr = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
Set srchRng = ActiveSheet.Range("D2" & lr)
myCell = srchRng.Find("AuditID=02", LookIn:=xlValues).Address
End Sub

 

[JLGWhiz]

You didn't specify what kind of control you would use on the UserForm so I
did not make the title line a click event. But if you use the code behind
the form you will need to change the title line to Private Sub
SomeControl_Click()

 

[MikeF]

Thanx for the reply.

the UserForm has a comboBox on it, that looks up the *unique* values in all
of column D.
So dropping down the comboBox [cboAuditID] has a list of one, two, three or
however many AuditID's in it, ie AuditID-01 / AuditID-02 / and so on.

The intention is when I drop down the comboBox, and then choose an AuditID
from its list, Excel will take me to the first occurence of that AuditID and
assign its cell address to the variable myCell.

Hope I've been clear enough [!!], this one's a little hard to convey.
Thanx again.
- Mike

You didn't specify what kind of control you would use on the UserForm so I
did not make the title line a click event. But if you use the code behind
the form you will need to change the title line to Private Sub
SomeControl_Click()

UserForm.show is frmAuditID.

Column D contains a few hundred instances of "AuditID-01" and then
"AuditID-02", sorted in ascending order [ie all instances of 01 are first].
Sometimes there are many more, ie AuditID-03 and so on, but always sorted in
ascending order.
A list of these, however many there may be, appears in frmAuditID.

The objective is to have frmAuditID appear at the start of a sub, I choose -
say "AuditID-02" - and it assigns the cell address of wherever the *first*
occurence of "AuditID-02" is in column d to a variable named "myCell".
Sometimes "AuditID-02" will be in cell d44, sometimes in cell d233,
sometimes, in d411, and so on.

Note - the "finding" part of this can be manually accomplished by selecting
cell d7, CTRL-F, search sheet/columns/values and then just punching -01 or
-02 or -03, etc ....

** Once myCell is given the appropriate address, another subroutine will
then run, using the base address of "myCell" to navigate from.

Any assistance will be *sincerely* appreciated.

Thanx in advance,
- Mike