Autofil from a variable cell

[AndrewMB]

OK, should be a quick one.

I have Jan, Feb, Mar etc going down over (12 rows) and to the right of each of those there is a price stated. One price per month.

If the prices changed in say April, I would want to change May to Dec, but leave Jan to April as they were.

I can find the correct cell and reflect the price change within it, but then want to use vba to autofil downwards.

The Dec cell (the last one) is fixed, always. But depending upon what month the price change occurs, the start cell for the autofil will be variable.

So how do I tell vba to start the autofil in the active cell that I'm in and tell it to stop at Dec?

 

[AndrewMB]

bump...

 

[mikerickson]

If January is row 1, put the price in F1 (if that's the correct column)
Then put =F1 in cell F2 (the Feb. price) and drag down.

Manually enter the price change into the F cell of the appropriate row and all subsequent months will have that price value.

 

[AndrewMB]

sorry, i should have explained better, i need to do this via vba as part of a larger macro.

the user will input the price change via an input box and then the price change needs to happen automatically. but depending upon what month the price is changed the starting cell for the autofil will change and so will be variable, but down to december (a fixed cell) so i know the ending point for the autofil, just not the starting point. (i know the starting coloumn obviously thats fixed, but the row will change)

 

[mikerickson]

I'm not quite sure what you are looking for, but here are two ways (assuming Price in column F). The first uses the mouse to indicate the month. The second routine asks the user to enter a number 1-12 to indicate the month.

Sub test()
    Dim newPrice As Double
    Dim startMonth As Range
    newPrice = Application.InputBox("Enter new Price", Type:=1)
    If newPrice = 0 Then Exit Sub: Rem cancel
    
    On Error Resume Next
    Set startMonth = Application.InputBox("Select the start month with the mouse", Type:=8)
    On Error GoTo 0
    If startMonth Is Nothing Then
        Exit Sub: Rem canceled
    Else
        If startMonth.Row > 12 Then Exit Sub: Rem invalid entry
    End If
 
    Range(startMonth.EntireRow.Range("F1"), Range("F12")).Value = newPrice

End Sub

Sub test2()
    Dim newPrice As Double
    Dim startMonth As Double
    
    newPrice = Application.InputBox("Enter new Price", Type:=1)
    If newPrice = 0 Then Exit Sub: Rem cancel
    
    On Error Resume Next
    startMonth = Application.InputBox("Enter month 1-12", Type:=1)
    If startMonth < 1 Or 12 < startMonth Then Exit Sub: Rem invalid entry
    
    With Range("F:F")
        Range(.Cells(startMonth, 1), .Cells(12, 1)).Value = newPrice
    End With
End Sub

 

[AndrewMB]

Many thanks, I was able to use that and adapt it.


Thank you!