VBA Script to search for a specific cell - Help needed

[Zeke XA3]

Hi there,

I have recently started a new job and trying to do some extra work t
get ahead. I have taken on some spreadsheet development and have bee
learning some basic macro and VBA details. I have just completed m
first Userform spreadsheet for my co-workers to enter details onto
spreadsheet in a nice uniform manner. However my next project is a bi
beyond what i have been able to teach myself.

I am trying to create a table for my co-workers to record day they ar
able to do overtime.

This will have the date along the top a row, and their names in
column. What i would like is for a user box in which they would ente
their name and date. At which point the VBA script would search th
column for their name and then the row for the date and in the cel
where they intersect insert a text (ie Overtime).

I have managed to find this bit of code on a forum that will search
column and allow me to put in available or not available for overtim
now I just need to add in the bit where it searches across for the dat
as well.

Code:


Code
-------------------

Sub Find_UserID()
Dim ThisCell As Range
Dim LookupValue As String
Dim SearchRange As Range
Set SearchRange = Sheets("Sheet1").Range("A1:A" & Sheets("Sheet1").Range("A65536").End(xlUp).Row)
LookupValue = Application.InputBox("What is the name of the person requesting Overtime?", "Person Lookup", , , , , , 2)
For Each ThisCell In SearchRange
If UCase(ThisCell.Value) = UCase(LookupValue) Then
Sheets("Sheet1").Range("B" & ThisCell.Row).Value = Application.InputBox("Avliable or Not?", "Enter text")
Exit Sub
End If
Next ThisCell
MsgBox LookupValue & " was not found!", vbOKOnly + vbCritical, LookupValue & " Not Found"
End Sub


-------------------




Any help in explaining how i can get it to search the row befor
submitting the application.inputbox value would be greatl
appreciated.

 

[Tom Ogilvy]

select all cells and turn on the macro recorder. then do Edit=>Find and
enter a users name and click find. Turn off the macro recorder.

Modify the recorded code to be something like this: (no activate

Dim sName as String, sDate as String
Dim rng as Range, rng1 as Range
sName = Userform2.Textbox1.Text
sDate = Userform2.Textbox2.Text
set rng = Worksheets("Data").cells.Find(What:=sName, . . .)
if not rng is nothing then
' now search for the date with similar code
set rng1 = Worksheets("Data").cells.Find(What:=sDate, . . .)
if not rng1 is nothing
set rng2 = Worksheets('Data").Cells(rng.Row, rng1.Column)
rng2.Value = "Overtime"
end if
end if

of course, if the data is in specific locations, you can reduce the extent
of the search, such as to search column 1 only

set rng1 = Worksheets("Data").columns(1).Find(What:=sDate, . . .)

Finding dates using the find command can be elusive. You might need to
experiment. If the dates are in a single row you can use

Dim rng4 as Range, res as Variant, rng as Range
set rng4 = Worksheets("Data").Range("A1:IV1")
res = Application.Match(clng(cdate(Userform2.Textbox2.Text)), _
rng4,0)
if not iserror(res) then
set rng1 = rng4(1,res)
' msgbox rng1.Address
set rng2 = Worksheets('Data").Cells(rng.Row, rng1.Column)
rng2.Value = "Overtime"
Else
msgbox Userform2.Textbox2.Text & " was not found"
end if