Searching array for date and changing offset colour depeding on combobox content?

[Simon Lloyd]

Hi all, i have a Userform3 that has 2 comboboxes on it one selects a
name the other selects a date, when clicking the command button how do
i get to search through arr = Array("Week1", "Week2", "Week3", "Week4",
"Week5", _
"Week6") to find the date and when it does offset(1,x) and change
colour lets say to Red where x would be dependent on the name in the
combobox e.g offset(1,1) would be Lauren offset(1,2) would be Emma and
offset(1,3) would be Cheryl, then once they have had their holiday and
the colour change is older than one week turn the cell colour back to
its original colour (they are different for each girl), The dates
appear only in column A in the places listed below:

Range("A1")
Range("A49")
Range("A97")
Range("A145")
Range("A193")

Probably trying to do too much here!, all help much appreciated!

Regards,
Simon

 

[RB Smissaert]

I am not sure what the precise problem is you are trying to solve, but this
will get you the array element that has the particular week:

dim i as long

for i = 0 to ubound(arr)
if arr(i) = "Week3" then
exit for
end if
next

msgbox "Week3 found in array element " & i

RBS

"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...

 

[Simon Lloyd]

Thanks for the reply RB i'm not trying to find a week (or sheet named
that week) in the array i want to search through the sheets in the
array looking for a date match to the one in combobox1 on my userform3
when it finds the match i need it to offset(1,x) and change colour lets
say to Red where x would be dependent on the name in the combobox2 e.g
offset(1,1) would be Lauren offset(1,2) would be Emma and offset(1,3)
would be Cheryl, then once they have had their holiday and the colour
change is older than one week turn the cell colour back to its original
colour (they are different for each girl), The dates appear only in
column A on each of the sheets in the places listed below:

Range("A1")
Range("A49")
Range("A97")
Range("A145")
Range("A193")

Any other ideas?

Regards,
Simon

 

[RB Smissaert]

So, what is the exact bit you are having a problem with?
Maybe have a look at the posting: Row number of a specific value in this
same NG, posted today.

RBS

"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...

 

[Simon Lloyd]

RB the posting didn't have anything relevant to what i was trying to
achieve, so here is what i have patched together to try to do the job,
it does search each worksheet one by one but does not find the value in
the combobox....but it does exist, can you help try and straighten this
out?

Regards,
Simon

Sub staffdates()

Dim wks As Worksheet
Dim rng As Range
Dim strFirst As String
Dim arr As Variant
Dim t1 As Variant
Dim r As Range
dv = ComboBox2.Value
sn = ComboBox1.Value
arr = Array("Week1", "Week2", "Week3", "Week4", "Week5", _
"Week6")
With Worksheets(arr)
Select Case sn
Case Is = "Lauren"
Set r = ActiveCell.Offset(1, 1)
Case Is = "Emma"
Set r = ActiveCell.Offset(1, 2)
Case Is = "Cheryl"
Set r = ActiveCell.Offset(1, 3)
End Select
End With
On Error GoTo XIT
Application.EnableEvents = False


For Each wks In Worksheets(arr)
wks.Visible = xlSheetVisible
Set rng = wks.Cells.Find(What:=dv, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)
If Not rng Is Nothing Then
strFirst = rng.Address
Do
wks.Select
rng.Select
r.Select
With Selection.Interior
..ColorIndex = 3
..Pattern = xlSolid
..PatternColorIndex = xlAutomatic
End With

If MsgBox("How about this one... " & rng.Text, _
vbYesNo, "Customer Found") = vbYes Then
Exit Sub
End If
Set rng = wks.Cells.FindNext(rng)
Loop Until rng.Address = strFirst
End If
wks.Visible = xlSheetHidden
Next wks
MsgBox PrompT:="Sorry, that was all of them", _
Buttons:=vbInformation, _
Title:="Search Complete"

XIT:
Application.EnableEvents = True

End Sub


Private Sub ComboBox2_Change()
ComboBox2 = Format(ComboBox2.Value, "dd/mmmm/yyyy")
End Sub

Private Sub CommandButton1_Click()
Call staffdates

End Sub

 

[RB Smissaert]

Not sure what is going on here:

rng.Select
r.Select

rng.Select won't do anything here as you are selecting another range
straight after.
Selecting ranges should be avoided in any case as you don't have to and it
will slow your code down.

RBS

"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...