Home |
Search |
Today's Posts |
#1
|
|||
|
|||
add "X" to cell based on cell value
I don't know how to accomplish this task:
While staying in a single column, I would like to enter a value like 12 and have it autofill eleven "x"s below the twelve indicating 12 rows including the value entered totals 12.. make sense? this needs to happen anyplace a value is entered in the column, that the number of "x"s need to fill in below less one. Row A 5 x - Autofills based on value above less that cell. x x x 3 - Same here and down the column x x 4 - same x x x Thanks in advance.. I have read several CountIf .. Data Validation .. just don't know how to start.. thanks thom |
#2
|
|||
|
|||
Hi Thom
you'll need code to do this - here's code that works on column A --- Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo err_handler If Target.Column = 1 Then If IsNumeric(Target.Value) Then For i = 1 To Target.Value - 1 Target.Offset(i, 0) = "x" Next End If End If err_handler: Application.EnableEvents = True End Sub --- to use it, right mouse click on the sheet tab of the sheet you want to use the code in, choose view code copy & paste the code into the right hand side of the screen use alt & f11 to switch back to your worksheet type a number in column A and see what happens NOTE there is no checking to ensure that data is not in the cells where the X will go, so maybe do this on a copy of the workbook first. Cheers JulieD "thom hoyle" wrote in message ... I don't know how to accomplish this task: While staying in a single column, I would like to enter a value like 12 and have it autofill eleven "x"s below the twelve indicating 12 rows including the value entered totals 12.. make sense? this needs to happen anyplace a value is entered in the column, that the number of "x"s need to fill in below less one. Row A 5 x - Autofills based on value above less that cell. x x x 3 - Same here and down the column x x 4 - same x x x Thanks in advance.. I have read several CountIf .. Data Validation .. just don't know how to start.. thanks thom |
#3
|
|||
|
|||
That was Great Julie.. thanks..
I would like to take it one more step... How do I target a Range in a column for this.. specifically I need this in H:15;H:204 thanks "JulieD" wrote: Hi Thom you'll need code to do this - here's code that works on column A --- Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo err_handler If Target.Column = 1 Then If IsNumeric(Target.Value) Then For i = 1 To Target.Value - 1 Target.Offset(i, 0) = "x" Next End If End If err_handler: Application.EnableEvents = True End Sub --- to use it, right mouse click on the sheet tab of the sheet you want to use the code in, choose view code copy & paste the code into the right hand side of the screen use alt & f11 to switch back to your worksheet type a number in column A and see what happens NOTE there is no checking to ensure that data is not in the cells where the X will go, so maybe do this on a copy of the workbook first. Cheers JulieD "thom hoyle" wrote in message ... I don't know how to accomplish this task: While staying in a single column, I would like to enter a value like 12 and have it autofill eleven "x"s below the twelve indicating 12 rows including the value entered totals 12.. make sense? this needs to happen anyplace a value is entered in the column, that the number of "x"s need to fill in below less one. Row A 5 x - Autofills based on value above less that cell. x x x 3 - Same here and down the column x x 4 - same x x x Thanks in advance.. I have read several CountIf .. Data Validation .. just don't know how to start.. thanks thom |
#4
|
|||
|
|||
Hi Thom
change the code to the following --- Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo err_handler If Intersect(Range("H15:H204"), Target) And IsNumeric(Target.Value) Then For i = 1 To Target.Value - 1 Target.Offset(i, 0) = "x" Next End If err_handler: Application.EnableEvents = True End Sub -- note, depending on your regional settings you may need to change H15:H204 or H15;H204 Cheers JulieD "thom hoyle" wrote in message ... That was Great Julie.. thanks.. I would like to take it one more step... How do I target a Range in a column for this.. specifically I need this in H:15;H:204 thanks "JulieD" wrote: Hi Thom you'll need code to do this - here's code that works on column A --- Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo err_handler If Target.Column = 1 Then If IsNumeric(Target.Value) Then For i = 1 To Target.Value - 1 Target.Offset(i, 0) = "x" Next End If End If err_handler: Application.EnableEvents = True End Sub --- to use it, right mouse click on the sheet tab of the sheet you want to use the code in, choose view code copy & paste the code into the right hand side of the screen use alt & f11 to switch back to your worksheet type a number in column A and see what happens NOTE there is no checking to ensure that data is not in the cells where the X will go, so maybe do this on a copy of the workbook first. Cheers JulieD "thom hoyle" wrote in message ... I don't know how to accomplish this task: While staying in a single column, I would like to enter a value like 12 and have it autofill eleven "x"s below the twelve indicating 12 rows including the value entered totals 12.. make sense? this needs to happen anyplace a value is entered in the column, that the number of "x"s need to fill in below less one. Row A 5 x - Autofills based on value above less that cell. x x x 3 - Same here and down the column x x 4 - same x x x Thanks in advance.. I have read several CountIf .. Data Validation .. just don't know how to start.. thanks thom |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Max and Min based on cell reference | Excel Discussion (Misc queries) | |||
I want to return "0" based on another cell | Excel Worksheet Functions | |||
make a cell empty based on condition | Charts and Charting in Excel | |||
I am trying to link based on a text value instead of cell position | Links and Linking in Excel | |||
How to populate column with formula based on value in cell | Excel Worksheet Functions |