Filling a Column with differing number of 0s depending on a specified, changing value

GoDucks13

New Member
Joined
Mar 29, 2013
Messages
5
Hi everyone!

I have an excel spreadsheet with a column of revenue values for every customer to my website. I want to add a column of 0s below these revenue values for every customer that visited the website but did not purchase. Every month the number of visitors who do not purchase will change. Is there a way to automate this process so that if I plug in the number of non-purchasing visitors, excel will auto populate that many zeroes into the revenue column?

I think this will require a macro, but I am unfamiliar with macros. I know I can highlight the number of cells and autofill those cells with 0, but there will be tens of thousands of cells to be autofilled and ideally I would like to transfer this process to someone who is an excel novice. To make the process as simple as possible, inputting the number of non-purchasing visitors into a cell and having the program auto populate the cells with 0s would be best. Thank you!!!
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
This should do it

Sub FillInTheOs()
Dim x As Long
Dim y As Long
Dim LastRow As Long
'Where 1 = column A, change number to suit Revenue column
LastRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
' This refers to the cell where you input the O's number, again change Range to suit also the 1 in (LastRow,1)
x = Range("A1").Value
Cells(LastRow, 1).Resize(x).Value = 0

End Sub
 

GoDucks13

New Member
Joined
Mar 29, 2013
Messages
5
Great! Thanks for your reply! I'm unfamiliar with how to implement this code into excel, could you walk me through it or point me to an implementation guide on the web? Thanks again!
 

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
go to developer tab in excel and select visual basic.

On the left hand side should be a box with the file name etc and one of the branches should say worksheet

click on it and it will open a white box on the right hand side,

Paste the Macro in there.

Which column is revenue and what cell are you going to put the count in?

You also need to consider what will trigger the Macro.

I personally would put a button near the cell with the count saying something like " insert amount of Zero's in cell and press enter"
 

GoDucks13

New Member
Joined
Mar 29, 2013
Messages
5

ADVERTISEMENT

Column C is the revenue column. I can put the insert number of zeroes button anywhere, lets say A1 just for this example. So the code would look like this?

Sub FillInTheOs()
Dim x As Long
Dim y As Long
Dim LastRow As Long
LastRow = Cells(Rows.Count, C).End(xlUp).Row + C
x = Range("A1").Value
Cells(LastRow, A1).Resize(x).Value = 0


End Sub

I should have mentioned that the revenue column has a title at the top, and numbers aren't inputted until C3.
 

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
Sub FillInTheOs()
Dim x As Long
Dim y As Long
Dim LastRow As Long
LastRow = Cells(Rows.Count, 3).End(xlUp).Row +1
x = Range("A1").Value
Cells(LastRow, A1).Resize(x).Value = 0


End Sub

In the scenario above the employee would type the figure in A1, the macro will find the last row in column C and then fill zeros down the
amount in A1. The fact the count does not start untill row 3 is not relevant for this.
 

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396

ADVERTISEMENT

Sub FillInTheOs()
Dim x As Long
Dim y As Long
Dim LastRow As Long
LastRow = Cells(Rows.Count, 3).End(xlUp).Row +1
x = Range("A1").Value
Cells(LastRow, 3).Resize(x).Value = 0


End Sub


Where the number of zeros is places in cell A1 (I forgot to change the zeros column sorry)
 

GoDucks13

New Member
Joined
Mar 29, 2013
Messages
5
I entered the macro into the editor. However, when I enter a number into cell A1 nothing happens?
 

GoDucks13

New Member
Joined
Mar 29, 2013
Messages
5
ahhh just saw your updated post, its working perfectly! Last question, how do I get the macro to run automatically when a user enters a value into cell A1 and presses enter?
 

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
Can someone step in please,

I am really struggling to do a change event macro for this,

Every time i select the cell it runs before I input a new number and press enter.

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,129,803
Messages
5,638,441
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top