How to identify a specific date in vba?

Asked

Viewed 1,019 times

1

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, will identify the month and year at the base and fill out the information there. But I don’t think the code is identifying the date. Follows 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
date excel-vba

  • is sure that the values of the variables a and b are interpolated correctly? I don’t know enough of VBA, but it seems that this is the problem

  • Hello Bruno. Thank you so much for your reply and willingness to help me! What do you mean by interpolated correctly? The variable a is the month and b is the year... so when I do "01/a/b" it should see or read 01/7/2017... in case the date I was trying to read...

  • 1

    Interpolation is exactly the substitution of variables inside the string. I’m not sure how this works in VBA, but I get the impression that "01 / a / b" will not fuck because the comparison will be with the literal values a and b. try something like: "01 / " & a & " / " & b, to concatenate the values before comparing.

  • According to this answer in the OS, there is no interpolation of variables and the best path is concatenation of the same values. https://stackoverflow.com/questions/28221349/vba-string-interpolation-syntax

1 answer

1

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

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

For example you can extract month and year:

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

And extract the number of days of that month:

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

Creating a loop with the number of days that month:

For i = 0 To intDaysInMonth - 1
End For

An example to look 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 number that corresponds to each Date since 01/01/1900 and sum with the Loop of each day that month contains:

LookingFor = CLng(mesAno) + i

After that you turn 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 Data Type or convert them to Long and work. For example, you tried to use a loop, so work in the format Long should facilitate this.

Browser other questions tagged date excel-vba

You are not signed in. Login or sign up in order to post.