Write dates (month/year) between two dates

Asked

Viewed 190 times

1

Hello,

I have two dates:

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

I would like to know how I can have a macro that writes the date between them adding these dates in two new lines and look in the price table, the price for that month. I’m using VLOOKUP for this, but I think there’s 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.

excel macro

  • There are many ways to accomplish this, could you better organize your example? With code Vlookup what is used? And with the example of input and output of clearer functions, because I do not know if I understood correctly.

1 answer

0

Try using the function below to create the desired structure for the first line and then adapt the code to run with all lines of 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

Write the formula you want, saving the macro to know how you do via vba and then add in the above function to add the desired formula.

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

I hope I’ve helped!

Browser other questions tagged excel macro

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