How to make a cell result copy itself into subsequent cells "X" number of times

[bran987]

Say I have (cell A1) 10 salespeople and they each sell a (A2) $100/month membership, so I have (A3) $1,000 monthly revenue.

(A4) is an assumption about how many months the average person will stay a member.

So if A4 = 6, (6 month membership assumption) how would I get Excel to output $1,000 (the result from A3) into cells B3 through B8

but then for instance if A4 = 3 (3 month membership assumption) it would only output the the $1,000 result from A3 into cells B3 through B5

Does that make sense?

So like A1 through A4 of...

10
$100
$1,000
6

would output $1,000 into B3 through B8

but

10
$100
$1,000
3

would output $1,000 only to B3 to B5

Thanks if that makes sense!

 

[Trebor76]

Hi bran987,

Try this event macro* on the sheet in question:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lngEndRow As Long
    
    lngEndRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    If lngEndRow >= 3 Then Range("B3:B" & lngEndRow).ClearContents
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
        Range("B3:B" & 3 + Range("A4").Value - 1).Value = Range("A3").Value
    
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

End Sub

HTH

Robert

* To install this macro follow these four steps:

1. Copy my code above to the clipboard (Ctrl + C)
2. Right click on the tab you wish the code to run on and from the shortcut menu select View Code
3. Paste in my code (Ctrl + V) from step 1
4. From the File menu click on Close and Return to Microsoft Excel