Enter same number in specific number of cells

[pyxiidust]

Good morning,

I would like to enter the same number in a specific amount of cells, i.e. the number "14" in A4 needs to be entered down the next 17 cells. Is there an easier way than just doing the math in my head?

Julie

 

[Ams_A]

I would need to know that A5:A21 would equal 17 cells.

1) Click on the cell you want to start with
2) On the Home tab, under Editing group,Click on Fill and select Series
3) You will get a dialog box in which you should select Columns in the In Series option
4) Set Value should be 1 (usually shows up as 1 by defualt) and the Stop value should be the one you want (17 in your case)
5) Then select those 1 to 17 values (Click on the first cell and then hold down the shift key and click on the last cell which is 17 in your case.
6) Next with the range selected enter the number and press Ctrl + Enter

That should be the way I could come across for you if don't want to count the number of cells
Hope that Helps.

 

[Jonmo1]

The formula I posted IS using a specific number.... 17..

 

[pyxiidust]

Press ALT+F11... this will take you into the VB editor... locate the Immediate Window (it should be showing at the lower part of the screen (if you do not see it, press CTRL+G to make it appear)... then put this inside that window...

Range("I4").Resize(17) = "Yes"

The blue highlighted 17 is the total number of cells you want to fill (including the starting cell itself) and, with the text cursor located anywhere on that line, press the Enter key. Press ALT+Q to return to the worksheet.

Yep... that worked... only I put:

Range("A4).Resize(17) = "14"

Thank you!

 

[Jonmo1]

Yep... that worked... only I put:

Range("A4).Resize(17) = "14"

Thank you!

Glad you got it sorted..And it is a good solution.

Just FYI, that's not EXACTLY the same as what you were looking for in the post..
You were saying A4 AND the next 17 cells, for a total of 18 cells including A4.
That would end up as A4:A21

This
Range("A4).Resize(17) = "14"
Is a total of 17 cells, including A4, and ends up as A4:A20


I know this is just symantecs, I'm not trying to be thesicious.
But it's an important distinction to understand.

 

[Rick Rothstein]

Yep... that worked... only I put:

Range("A4).Resize(17) = "14"

Sorry about the "Yes" instead of 14... I ended up copy/pasting my test code and forgot to modify it for your specific question.

Another way, maybe a little easier to work with... type the following into the Name Box (the field to the left of the Formula Bar)...

=i4:index(i:i,4+17)

and hit Enter (the 17 is the total number of cells you want to fill, change as needed, and note that you do not have to fill in the first cell beforehand). This will select the number of cells specified by the number highlighted in red... type the value you want to fill the cells with (14 for you example) into the Formula Bar and press CTRL+ENTER.

 

[Jonmo1]

Yet another cool trick Rick.

type the following into the Name Box (the field to the left of the Formula Bar)...

=i4:index(i:i,4+17)

 

[Rick Rothstein]

type the following into the Name Box (the field to the left of the Formula Bar)...

=i4:index(i:i,4+17)

Yet another cool trick Rick.

Thanks! Interestingly enough, the equal sign appears not to be required.

 

[pyxiidust]

Sorry about the "Yes" instead of 14... I ended up copy/pasting my test code and forgot to modify it for your specific question.

Another way, maybe a little easier to work with... type the following into the Name Box (the field to the left of the Formula Bar)...

=i4:index(i:i,4+17)

and hit Enter (the 17 is the total number of cells you want to fill, change as needed, and note that you do not have to fill in the first cell beforehand). This will select the number of cells specified by the number highlighted in red... type the value you want to fill the cells with (14 for you example) into the Formula Bar and press CTRL+ENTER.

Haha!!! VERY cool!

 

[pyxiidust]

Glad you got it sorted..And it is a good solution.

Just FYI, that's not EXACTLY the same as what you were looking for in the post..
You were saying A4 AND the next 17 cells, for a total of 18 cells including A4.
That would end up as A4:A21

This
Range("A4).Resize(17) = "14"
Is a total of 17 cells, including A4, and ends up as A4:A20


I know this is just symantecs, I'm not trying to be thesicious.
But it's an important distinction to understand.

You are right, on all accounts.

 

[Jonmo1]

I'm sorry again, but I just can't help thinking this simple task is being made more difficult than it really is.

If you left click and hold on A4
Then drag your mouse down, the Name Box (which Rick cleverly used before) automatically and dynamicly shows you how many rows/columns you have selected.
Just move your mouse down till the Name Box Says 17R X 1C (or 18r?).
Release the mouse, and press CTRL+D