Write dates (month / year) between two dates

Question

Write dates (month / year) between two dates

Navigation

#1 by (0 votes)

1

Hello,

I have two dates:

Initial     Before
Jan-11      Oct-10
Feb-11      Sep-10

I'd like to know how I can have a macro that writes the date between them by adding these dates to two new rows and look in the price table for the price for that month. I'm using VLOOKUP for this, but I think it has a more efficient way:

Initial     Before  Range   Price
Jan-11     Oct-10    Jan-11 0
                     Dec-10 12
                     Nov-10 11
                     Oct-10 10
Feb-11  Sep-10  Feb-11  0
                Jan-11  0
                Dec-10  12
                Nov-10  11
                Oct-10  10
                Sep-10  9

Thank you.

macro excel

asked by anonymous 30.10.2017 / 18:34

1 answer

System Settings in static class in the Entity Framework Example and illustration about redundant relationships (MER)

score 0 · Answer 1

Try using the function below to create the desired structure for the first line and then adapt the code to run with all the rows in your worksheet:

Sub Escrever_Data()

Dim DATA1 As String
Dim DATA2 As String

    ' Célula Inicial
    Range("A2").Select

    If Selection.Value = "" Then
        Exit Sub
    End If

    DATA1 = Selection.Value
    DATA2 = Selection.Offset(0, 1).Value

    ' Copia a primeira data
    Selection.Copy
    Selection.Offset(0, 2).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

    ' Verifica se DATA1 < DATA2
    If DateValue(DATA1) < DateValue(DATA2) Then
        MsgBox "Data Inicial menor do que data final", vbError, "Erro"
        Exit Sub
    End If

    ' Loop para copiar as demais datas
    Do While DateValue(DATA1) <> DateValue(DATA2)

        ' Reduz 1 mês da data inicial
        DATA1 = DateAdd("m", -1, DateValue(DATA1))

        ' Insere nova linha abaixo
        Selection.Offset(1, 0).Select
        Selection.EntireRow.Insert
        Selection.NumberFormat = "[$-416]mmm-yy;@"
        'Selection.NumberFormat = "[$-409]mmm-yy;@" ' Se formato em inglês
        ActiveCell.FormulaR1C1 = DateValue(DATA1)
    Loop
End Sub

Type the formula you want by saving the macro to find out how it does via vba and then add it in the function above to add the desired formula.

If you post your procv formula we can help you more easily.

I hope I have helped!