TryingToLearn
When the mountain does not come to Mohammed…
Mohammed needs to come to the mountain
And after a two day break I finally got the breakthrough…
Somehow… I did not get the code fixed in such a way that it did what it was suppose to do. Unintented you gave me a clue that finally led to a working macro.
In the message from 24 Jan 2004 16:29 you wrote out in formula what you thought I was trying to achieve. That helped me to think in formula instead of code.
I created formula in 31 cells of each sheet similar to the following: =IF(AND(C3=A1,C15<>"NOT",C15<>"SICK",D15=""),"Match","NoMatch"). The formula where put in place on all sheets by a macro.
The vba-code for the search macro finally looked like this:
Sub Zoeken()
Dim What, When, Who As String
Dim sht As Worksheet
Dim Found As Range
Dim FirstAddress As String
Dim Response
What = InputBox("Vul hieronder de naam van de werkeenheid/afdeling in waarvoor je vervang nodig hebt:")
If What = "" Then Exit Sub
When = InputBox("Vul hier de dag van de maand in (enkel het nummer van de dag) waarvoor je vervang zoekt:")
If When = "" Then Exit Sub
Who = InputBox("Vul hier de functie in die benodigd is")
If Who = "" Then Exit Sub
Range("Toetsen!A200") = Who
For Each sht In Worksheets
sht.Activate
If UCase(Cells(When + 1, 1)) = "MATCH" Then
Set Found = sht.Cells.Find(What)
If Not Found Is Nothing Then
FirstAddress = Found.Address
Do
Found.Activate
Response = MsgBox("Doorgaan met zoeken?", vbYesNo)
If Response = vbNo Then Exit Sub
Set Found = Cells.FindNext(After:=ActiveCell)
On Error Resume Next
If Found.Address = FirstAddress Then Exit Do
Loop
End If
End If
Next sht
MsgBox "Zoeken afgerond!"
End Sub
When lookin for the string What ánd the word "MATCH" I put into use the problem that only one If Ucase line was executed.
You mentioned that you did not know what the line Set Found = sht.Range("D6").Find(What, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False) meant. Well, the idea was to narrow down the search to one specific cell. This worked as long as it was indeed one cell. In the proces however D6 was merged with some other cells and the code –even after some changes- did not work appropriate anymore. Changing the line back to Set Found = sht.Cells.Find(What) AND adding the line On Error Resume Next finally gave a working code-formula combination. I guess there is another way instead of the On Error Resume Next-line but for the moment it works well enough.
TryingToLearn,
thanks for the help and ideas. I would not have managed without your help.