HELP!

[Hashe]

Can somebody help me with this code. Their is some mistake in it but I can't find it. If you need the complete code, just ask me.
Set rngCelcontroleren = Range("C1")

Do
rngCelcontroleren.Select
dteDatumControleren = rngCelcontroleren.Value
Set rngCelcontroleren = rngCelcontroleren.Offset(0, 1)

Loop Until dteGezochteDatum = dteDatumControleren
Set rngCelcontroleren = rngCelcontroleren.Offset(2, 0)
rngCelcontroleren.Copy (Worksheets("front").[J1])
Thanks

[mdmackillop]

Welcome to VBAX

Avoid Selection where you don't need it.



Set rngCelcontroleren = Range("C1")

'Could be an infinite loop
Do
dteDatumControleren = rngCelcontroleren.Value
Set rngCelcontroleren = rngCelcontroleren.Offset(0, 1)
Loop Until dteGezochteDatum = dteDatumControleren

Set rngCelcontroleren = rngCelcontroleren.Offset(2, 0)
rngCelcontroleren.Copy Worksheets("front").Range("J1")



PS, Please use meaningful titles to your questions

[Hashe]

What can I do make sure it won't be an infinite loop? I just want to find a date the same as my date I've put in dteGezochteDatum( in English: dteSearchedDate) . The date I search is in the first row and after that I have to control all the values in the cells of the colum where I found the correct date. Ex. Row "F". Do you know how to do that?

Sorry for my English

[GTO]

Greetings Hashe,

If you have not gotten this resolved, I would suggest you repost including at least the code to the current procedure, and in English would indeed make it easier on most.

Mark

[rbrhodes]

Hashe,

Perhaps a 'for each' with an exit line when found and a check of not found:


Sub FindDate()

'Variable for last used column
Dim LastColumn As Long

'Variable (object) for individual cells in search range
Dim cel As Range

'for testing
dteGezochteDatum = Range("A1")

'Get last Column to search
LastColumn = Range("IV1").End(xlToLeft).Column

'Set as C1 to last column used, row 1
Set rngCelcontroleren = Range(Cells(1, 3).Address, Cells(1, LastColumn).Address)

'Use "For Each"
For Each cel In rngCelcontroleren
'Test if date found
If dteGezochteDatum = cel.Value Then
'Yes. Continue with Copy
Exit For
ElseIf cel.Column = LastColumn Then
'Test if whole range searched
LastColumn = MsgBox("Date not found", vbInformation)
'Destroy object
Set cel = Nothing
'Bail
Exit Sub
End If
Next cel
cel.Offset(2, 0).Copy Worksheets("front").Range("J1")
'Destroy object
Set cel = Nothing
End Sub

[Hashe]

Ok this is my code till now because I don't know how to use the code I got from ...

' In cel J1, the number of the chambre will be put that's responsible for the acute pati?nts.
' Declare of variables
Dim dteSearchedDate, dteControlDate As Date
Dim intMonth As Integer
Dim intSearchedDate As Integer
Dim rngControlCel As Range

' What date are we searching for?
dteSearchedDate = Worksheets("front").[A1].Value

' In which worksheets is the information we need?
' What is the month we need?
intMonth = Month(dteSearchedDate)
' Go to the right worksheet
If intMonth >= 1 And intMonth <= 5 Then
Sheets("jan-mai").Select
' Search roomnumber of the acute pati?nts and put that in cel J1
Set rngControlCel = Range("C1")

'Could be an infinite loop
Do
dteControlDate = rngControlCel.Value
Set rngControlCel = rngControlCel.Offset(0, 1)
Loop Until dteSearchedDate = dteDatumControleren
' Go 2 rows down to copy the number of the chamber of the acute pati?nts
Set rngControlCel = rngControlCel.Offset(2, 0)
rngControlCel.Copy Worksheets("front").Range("J1")

Else
Sheets("jun-dec").Select
' Search roomnumber of the acute pati?nts and put that in cel J1
Set rngControlCel = Range("C1")

'Could be an infinite loop
Do
dteControlDate = rngControlCel.Value
Set rngControlCel = rngControlCel.Offset(0, 1)
Loop Until dteSearchedDate = dteDatumControleren
' Go 2 rows down to copy the number of the chamber of the acute pati?nts
Set rngControlCel = rngControlCel.Offset(2, 0)
rngControlCel.Copy Worksheets("front").Range("J1")

End If


End Sub

[Hashe]

please help me

[mdmackillop]

Can you post a sample workbook?

[Hashe]

In the first file the code with the 'loop' is in the codeviewer of the sheet "front".In the second file stands the code with the 'for each' code. The second post is uploaded in the next post

Thanks for your help.

[Hashe]

This is the second file.

Again, thank you for helping me

[mdmackillop]

I've only looked at the first file.
The Sheet1 code has been changed to prevent looping when values are changed. Similar changes are needed for Sheet2.

If you are looking for a value, don't compare every cell, use Find instead and test for result. Also, your code was not correctly referencing Sheet1/2.


' In welk werkblad staat de informatie?
' Over welke maand gaat het?
intMonth = Month(dteGezochteDatum)
' Naar juiste werkblad gaan
If intMonth >= 1 And intMonth <= 5 Then
Set ws = Sheets("jan-mei")
Else
Set ws = Sheets("jun-dec")
End If

' Zaalnummer vvan de acute pati?nten zoeken en in cel J1 zetten

Set c = ws.Rows(1).Find(dteGezochteDatum)
If Not c Is Nothing Then
c.Offset(2).Copy (Worksheets("front").[J1])
End If



PS,
Always use Option Explicit

[Hashe]

Thanks, this really works very good. I've been searching for this for weeks. Now I have another problem. You know my program experience isn't so good right now but I'm working on it.

Now I have the number of the acute patients but in the picture in the attachment you can see that I need to add all the names from the people who work in chamber 1 on that day and you can see the number of that in column B, but I also have to check the codes that are in the column of the date we just found. So when the program sees this codes, "V1, L1 or D1" it should also add the name of the person who belongs with this name in the sheet "front".

I'm using an If -then structure but then I can only control the numbers in column B and not the ones in the column we just found.


Can you help me?

[mdmackillop]

Not sure if I follow, but try this


'as before
' Zaalnummer vvan de acute pati?nten zoeken en in cel J1 zetten

Set c = ws.Rows(1).Find(dteGezochteDatum)
c.Interior.ColorIndex = 6
If Not c Is Nothing Then
c.Offset(2).Copy (Worksheets("front").[J1])
End If

'Add this code

'Get V1, L1 and D1 names
Dim Rng As Range, r As Range
With ws
Set Rng = .Range(c, .Cells(Rows.Count, c.Column).End(xlUp))
For Each r In Rng
Select Case r
Case "V1", "L1", "D1"
Set tgt = Sheets("Front").Cells(Rows.Count, 1).End(xlUp).Offset(1)
.Cells(r.Row, 3).Copy
tgt.PasteSpecial xlValues
tgt.Offset(, 1) = r
End Select
Next r
End With

[Hashe]

Yes, I understand what your doing but how can I also control the numbers in kolumn B? I also need the names of the people who have number 1 next to their names in culumn B.

Thanks

[mdmackillop]

Can you repost your sample showing the output you are looking for with some comments to make things clear.

[Hashe]

Ok, I thank you for helping me.

In the attachment I wrote the whole explanation of the program. And in the word document you can see an example of what I mean

[Hashe]

This is the example

[mdmackillop]

I consider what you are asking is beyond the scope of the free assistance offered here. Refer to FAQ for further info.
Regards
MD

[Hashe]

I consider what you are asking is beyond the scope of the free assistance offered here. Refer to FAQ for further info.
Regards
MD

Yes I know, but do you maybe know how to find which names should be copied in the worksheet front. This is the only thing I need to know. I think that I can make the rest If I found that because the rest is almost the same like that.


Thanks