Can Excel find a cell based on two criteria?

[wojo]

My worksheet does not have column headings, but I do have unique data
in a particular row. The data is a Date.

The rows contain unique entries also (2 characters, always unique
example JO,WO,XX,RR,TV ETC..)

Is there a way, to have excel find the cell that would be in the column
that contains a date (entered by user)and the Row that contains the
initials (again entered by the user)?

If so, can a userform be created for the user to enter the information
(date mm/dd/yy) + (XX), along with the New data that goes into the
'intersecting' cell?
c d e
7/20/05 7/21/05 7/22/05
3 JO 6 6 14
4 WO 12 6 RDO
5 XX 7 12 12

I want an easy way for the user to find (example: E5) by entering
7/22/05, XX... then they could enter (example 6) new data for E5.

The worksheet has many dates and it would be easier than scrolling
through the worksheet.

If a userform can be created to modify the data in the worksheet, can
this userform be opened by Access? (We use this also, along with the
Excel program. Access is opened first and then they have to go to the
Excel program to make a change. It would be great if they could do it
all from one place.

Thanks for any insight you can give me. Thanks to Dave, I am learning
about userforms, but I haven't quite got it figured out yet.

Jo

 

[ScottO]

One way to approach it would be to use named ranges.
If you name all the columns with a "date like" reference (eg. the
column with data for 7/22/05 could be named Jul22_05) and name all
the rows with their 2 char unique ID (eg. "XX"), then you could enter
an intersection address in the name box (eg Jul22_05 XX - note the
space between Jul22_05 and XX) and the selection will move to the
intersection of the two ranges.
If the effort of naming all the ranges seems tedious, you can
automate the process by using Insert/Names/Create.
You could insert a row above your date heading and use a formula like
=text(C2,"Mmmdd_YY") and fill across the row, then select the entire
range and use the insert/names/create menu to do the work for you.
Hope this helps.
Rgds,
ScottO

| My worksheet does not have column headings, but I do have unique
data
| in a particular row. The data is a Date.
|
| The rows contain unique entries also (2 characters, always unique
| example JO,WO,XX,RR,TV ETC..)
|
| Is there a way, to have excel find the cell that would be in the
column
| that contains a date (entered by user)and the Row that contains the
| initials (again entered by the user)?
|
| If so, can a userform be created for the user to enter the
information
| (date mm/dd/yy) + (XX), along with the New data that goes into the
| 'intersecting' cell?
| c d e
| 7/20/05 7/21/05 7/22/05
| 3 JO 6 6 14
| 4 WO 12 6 RDO
| 5 XX 7 12 12
|
| I want an easy way for the user to find (example: E5) by entering
| 7/22/05, XX... then they could enter (example 6) new data for E5.
|
| The worksheet has many dates and it would be easier than scrolling
| through the worksheet.
|
| If a userform can be created to modify the data in the worksheet,
can
| this userform be opened by Access? (We use this also, along with
the
| Excel program. Access is opened first and then they have to go to
the
| Excel program to make a change. It would be great if they could do
it
| all from one place.
|
| Thanks for any insight you can give me. Thanks to Dave, I am
learning
| about userforms, but I haven't quite got it figured out yet.
|
| Jo
|

 

[Dave Peterson]

You could have a userform in excel that does the work, but since you really want
to do this in access, I think you should post in one of the Access newsgroups.

(I agree with you that one touch is better than many touches.)

 

[wojo]

Here's the problem with doing this in Access. I created this
worksheet, not knowing how to create a worksheet. My sheet was created
to look like a hardcopy paper that is currently in use. Our workforce
hates change. Because of this, there are empty columns and I have an
area at the top of the worksheet, that is "frozen" so the user can see
the calculations that result from changes made in the body of the
worksheet.

Because of the formula area (which I didn't know how to display any
other way for the user to see all the time... example I would have
preferred to put this at the lower section of the screen), I don't have
worksheet that can be imported/linked to Access, for input. If I do, I
don't know yet how, but I am learning.

Maybe using the Find function, in a macro... twice.... once for Date
(user must be able to make the input), select that column range,
now... find (Initials) and their cursor could end up in the right cell.

Can a macro "pause" for input, then continue, "pause" again for input?

JO

 

[Dave Peterson]

You could use application.match to look through the ranges to find a match.

But since you're suggesting a user form, you don't have to pause.

I created a small userform with a label (for error messages), two buttons
(cancel and ok) and two comboboxes--one for the dates and one for the
abbreviations.

I put a button from the forms toolbar on the worksheet and assigned this macro
to it:

Option Explicit
Sub showme()
UserForm1.Show
End Sub

(This goes in a general module.)

Then behind the userform, I had this code:

Option Explicit
Dim myDatesRng As Range
Dim myAbbrRng As Range
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()

Dim myRow As Long
Dim myCol As Long
Dim OkToContinue As Boolean

If Me.ComboBox1.Value = "" _
Or Me.ComboBox2.Value = "" _
Or Me.TextBox1.Value = "" Then
Me.Label1.Caption = "Please Enter all three values!"
Exit Sub
End If

myRow = Application.Match(Me.ComboBox2.Value, myAbbrRng, 0)
myCol = Application.Match(CLng(CDate(Me.ComboBox1.Value)), myDatesRng, 0)

myDatesRng.Cells(myRow + 1, myCol).Value = Me.TextBox1.Value

Me.Label1.Caption = ""

'clear the input areas?
Me.ComboBox1.Value = ""
Me.ComboBox2.Value = ""
Me.TextBox1.Value = ""

End Sub
Private Sub UserForm_Initialize()
Dim wks As Worksheet
Dim myCell As Range

Set wks = Worksheets("sheet1")

With wks
Set myDatesRng = .Range("c2", .Cells(2, .Columns.Count).End(xlToLeft))
Set myAbbrRng = .Range("B3", .Cells(.Rows.Count, "B").End(xlUp))
End With

For Each myCell In myDatesRng.Cells
With myCell
If Trim(.Value) = "" Then
'do nothing
Else
Me.ComboBox1.AddItem Format(.Value, "mmm dd, yyyy")
End If
End With
Next myCell

For Each myCell In myAbbrRng.Cells
With myCell
If Trim(.Value) = "" Then
'do nothing
Else
Me.ComboBox2.AddItem .Value
End If
End With
Next myCell

Me.ComboBox1.Style = fmStyleDropDownList
Me.ComboBox2.Style = fmStyleDropDownList

Me.Label1.Caption = ""

Me.CommandButton1.Cancel = True
Me.CommandButton2.Default = True

Me.Label1.ForeColor = &HFF&

End Sub

If you're new to macros, you may want to read David McRitchie's intro:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

And if you're new to userforms, you may want to read Debra Dalgleish's intro:
http://www.contextures.com/xlUserForm01.html

 

[wojo]

I should have tried the find "idea" before posting, it doesn't work,
like I wanted.

 

[wojo]

Thank goodness I am on vacation. I have all day to play with this and
figure it all out.
I'm off to learn more about user forms (thanks for the link).

I'll be back... JO

 

[Dave Peterson]

Find can have trouble with dates. If you convert it to a long, it sometimes
works better.

In fact, you might be getting the date as a string, so you have to convert it to
a date first, then to long (or try it just after converting to a date).