How to identify a specific date in VBA?

Question

How to identify a specific date in VBA?

Navigation

#1 by (0 votes)

0

I have a base with dates of several months and years, and a home screen where I update the submission date and enter the information.

I made a code that will identify the month and year of the home screen, identify the month and year at the base and fill in the information there. But I think the code is not identifying the date. Follow the code.

ub Abastecer_base_total()

Set inicio2 = Application.Sheets("2")
Set inicio3 = Application.Sheets("3")
Set inicio4 = Application.Sheets("4")
Set inicio5 = Application.Sheets("5")
Set inicio6 = Application.Sheets("6")
Set inicioDEPARA = Application.Sheets("DE PARA")
Set inicioBASETOTAL = Application.Sheets("Base total")

a = Month(inicio2.Cells(3, 4).Value)
b = Year(inicio2.Cells(3, 4).Value)

l = 3

Do Until inicioBASETOTAL.Cells(3, l).Value = "01 / a / b"

l = l + 1
Loop


End Sub

data excel-vba

asked by anonymous 28.08.2017 / 13:55

1 answer

Tomcat v8.5 does not boot, displays error: Child container failed during start I can not retrieve JSON in jQuery

score 0 · Answer 1

Since @Bruno Coimbra spoke about the interpolation, it is not possible. However it is possible to work with the date in Long format and then convert it to Date again.

The functions used are those of Type conversion : CLng() and CDate()

For example you can extract the month and year:

mesAno= (Right(ThisWorkbook.Sheets(1).Range("D3"), 7)) ' mesAno = 08-2017

And extract the number of days from that month:

 intDaysInMonth = day(DateSerial(Year(mesAno), Month(mesAno) + 1, 0))

Creating a loop with the number of days in that month:

For i = 0 To intDaysInMonth - 1
End For

An example to search for a specific month and year:

Dim monthYear As Date
Dim rng As Range, rng2 As Range, cellFound As Range
Dim ws As Worksheet
Dim i As Long, lastrow As Long

    Set ws = ThisWorkbook.Sheets(1)
    lastrow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row


    Set rng = ws.Range(ws.Cells(3, 4), ws.Cells(lastrow, 4))
    mesAno= (Right(ThisWorkbook.Sheets(1).Range("D3"), 7)) ' mesAno = 08-2017
    intDaysInMonth = day(DateSerial(Year(mesAno), Month(mesAno) + 1, 0))

  For i = 0 To intDaysInMonth - 1
        LookingFor = CLng(mesAno) + i
        LookingForString = CStr(CDate(LookingFor))
        With rng
            Set cellFound = .Find(what:=LookingForString, LookIn:=xlValues, MatchCase:=False)
                If Not cellFound Is Nothing Then
                    FirstAddress = cellFound.Address
                    Do
                        Debug.Print cellFound.Address
                        Set cellFound = .FindNext(cellFound)
                    Loop While Not cellFound Is Nothing And cellFound.Address <> FirstAddress
                 End If
        End With
    Next i

You first transform to Long and get an integer that corresponds to each Date as of 01/01/1900 and adds to the Loop for each day that month contains:

LookingFor = CLng(mesAno) + i

After this you transform to Date with CDate and then to String with CStr

LookingForString = CStr(CDate(LookingFor))

It is extensive and above basic level, but to work with Dates you can work with them in Type Data or convert them to Long and work. For example, you tried to use a loop, so working in Long format should make this easier.