add "X" to cell based on cell value

[thom hoyle]

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

[JulieD]

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" <thomhoyle@discussions.microsoft.com> wrote in message
news:C92C82F0-336B-4C8C-B322-1459A7EDD8CE@microsoft.com...
>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
>
>

[thom hoyle]

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" <thomhoyle@discussions.microsoft.com> wrote in message
> news:C92C82F0-336B-4C8C-B322-1459A7EDD8CE@microsoft.com...
> >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
> >
> >
>
>
>

[JulieD]

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" <thomhoyle@discussions.microsoft.com> wrote in message
news:ADE17E34-FD30-472F-83BF-2E5AED53F806@microsoft.com...
> 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" <thomhoyle@discussions.microsoft.com> wrote in message
>> news:C92C82F0-336B-4C8C-B322-1459A7EDD8CE@microsoft.com...
>> >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
>> >
>> >
>>
>>
>>