excel - VLOOKUP 并填写日期范围内的所有单元格

Question

我有两张纸:

- 预订从表单(car nr、registration nr、car model、starting date 和 return date)获取数据。 registration nr 的值和 car model通过 vlookup 来自 dispo)
- 处置 实际上只是一个日历，所有日期都在第一行和前四列中:car nr , registration nr , car model和 today 的日期.

我的程序必须将表单中的数据注册到“预订”(有效)中，然后从中找到“dispo”中对应于 starting date 的单元格和 car nr并在其中放一个“x”(实际上我希望在 starting date 和 return date) 之间的所有单元格中都有一个“x”。

现在，car nr固定为1。这是我的代码:

Private Sub cmdajout_Click()

Dim dispo As Worksheet
Dim booking As Worksheet
Dim db As Range
Dim ligne As Integer
Dim l As Long
Dim c As Long
Dim maxc As Long
Dim maxl As Long

Set dispo = ActiveWorkbook.Sheets("Dispo")
Set booking = ActiveWorkbook.Sheets("booking")
Set db = dispo.Range("A2:C35")

vehicule = 1

' find the first empty row
If Sheets("booking").Range("A2").Value = "" Then
ligne = 2
Else:
ligne = Sheets("booking").Range("A1").End(xlDown).Row + 1
End If

' copy the data from the form to "booking" and get by vlookup the missing data
booking.Range("A" & ligne).Value = vehicule
imma = Application.WorksheetFunction.VLookup(booking.Range("A" & ligne), db, 2, False)
booking.Range("B" & ligne).Value = imma
model = Application.WorksheetFunction.VLookup(booking.Range("A" & ligne), db, 3, False)
booking.Range("C" & ligne).Value = model
booking.Range("D" & ligne).Value = Format(txtdepart, "short date")
booking.Range("E" & ligne).Value = Format(txtfin, "short date")
booking.Range("F" & ligne).Value = txtclient
booking.Range("G" & ligne).Value = txtlocation
Unload Me

' book the date in the calendar "dispo"
dispo.Select
maxc = dispo.Range("A1").End(xlToRight).Column
maxl = dispo.Range("A1").End(xlDown).Row

For c = 5 To maxc
If dispo.Cells(1, c).Value = txtdepart Then 'it's here that i don't understant ... if i write = "05-01-2013" it finds it, but if i write = txtdepart (wich is where in the form i entered the date), it doesnt find
    For l = 2 To maxl
        If dispo.Range("A" & l).Value = vehicule Then 'same proble here ... it finds if i enter ="1" but not if i write = vehicule
        dispo.Cells(l, c).Value = "x"
        Else:
        End If
    Next l
Else:
End If
Next c
End Sub

有人帮我解决我的问题吗？

谁能给我代码让它在starting date之间的所有单元格中添加一个“x”和 return date ?

Answer 1

您正在不同类型/值之间创建条件(从逻辑上讲，不成立):dispo.Cells(1, c).Value = txtdepart如果两个变量具有相同的类型(和内容)，即 string = string 或 date = date，则将起作用。显然您期待日期，因此您可以使用以下代码来完全确定:

If Format(CDate(dispo.Cells(1, c).Value), "dd-MM-yyyy") = Format(CDate(txtdepart), "dd-MM-yyyy") Then 

等效问题

If dispo.Range("A" & l).Value = vehicule Then

两个成员必须相同。上面你写的是 vehicule = 1因此这个条件只会验证 dispo.Range("A" & l).Value等于 1。

不知道你想在 "x" 中写什么部分。