Fill rows based on number ented
Fill rows based on number ented
(OP)
I would like to know if the following is possible and if it is how to do it:
Enter a number: 3
This would then "fill" a selected row or certian columns in a row that many times.
Basically instead of copying say, row 2, 3 times it would "fill" row 2, 3 times.
Example:
Row 1: Enter number of fill rows= 3
Row 2: Monday
Row 3: Tuesday
Row 4: Thursday
Row 5: Friday
Thanks!
Enter a number: 3
This would then "fill" a selected row or certian columns in a row that many times.
Basically instead of copying say, row 2, 3 times it would "fill" row 2, 3 times.
Example:
Row 1: Enter number of fill rows= 3
Row 2: Monday
Row 3: Tuesday
Row 4: Thursday
Row 5: Friday
Thanks!
EIT





RE: Fill rows based on number ented
EIT
RE: Fill rows based on number ented
What does "fill" mean to you?
Your example uses 3 as the number of "filled" rows, then goes on to show 4 additional rows.
What do Monday, Tuesday, Thursday and Friday have to do with anything?
You want to "fill" certain rows or certain columns. Rows when? Columns when? What rows or columns?
RE: Fill rows based on number ented
=====================================
(2B)+(2B)' ?
RE: Fill rows based on number ented
=====================================
(2B)+(2B)' ?
RE: Fill rows based on number ented
I think maybe once "fill" is understood it will make more sense.
If you type Monday into row 1 and drag it down (3) cells the rows below are "filled" with Tuesday, Wednesday, Thursday.
So instead of manual needing to click and drag/fill these cells I would like this to be done "automatically".
Attached is an example, I'm not sure if it will help but basically in the current spreadsheet you must "fill" a certain number of rows. I would like this to be done "automatically"
EIT
RE: Fill rows based on number ented
=====================================
(2B)+(2B)' ?
RE: Fill rows based on number ented
I will take a look.
I know almost nothing about VBA but have wanted to learn. How can a view the "code/markup/routine" (not sure what this is called)?
EIT
RE: Fill rows based on number ented
Tools / Macro / VBA-Editor
In the "project" pane, double-click on module of interest, in this case it is titled "sheet1"
Code is as follows:
CODE
Dim row As Long
Dim col As Long
Dim RowsToClear As Long
RowsToClear = 100 ' How many rows to clear
ColsData = 5 ' Columns of data
If Target.Address = Range("Ncourses").Address Then
MsgBox "This will erase data below row 11... ok to continue or Ctl-Break to abort"
Range("ULHC").Offset(1, 0).Resize(RowsToClear, ColsData).Clear
For row = 2 To Range("Ncourses").Value
For col = 1 To 1
Range("ULHC").Offset(row - 1, col - 1) = row
Next col
For col = 2 To ColsData
Range("ULHC").Offset(0, col - 1).Copy Range("ULHC").Offset(row - 1, col - 1)
Next col
Next row
End If
End Sub
The contents (formula) of this first row get copied for the number of rows specified. The cell in the first column will always be integer 1, 2, 3... The cell in the 2nd through ColsData columns are copied formulas from row "1" (the row identified by ULHC).
=====================================
(2B)+(2B)' ?
RE: Fill rows based on number ented
Thanks again!
EIT
RE: Fill rows based on number ented
Say I wanted to make an x, y plot but wanted to change the number of points, basically the same concept as before. Essentially I want to apply this to be able to break a total length L in to segments and based on the number of segments that is how many rows would need to be executed. The more segments the more refined the curve.
1 x1 f(x1)
2 x2 f(x2)
3 x3 f(x3)
I ask because I would have thought there would be something already built into excel.
Thanks for you efforts.
EIT
RE: Fill rows based on number ented
So, it is expected that since you had 0 in the first row of column C, that's what you end up in all the rows below it. Likewise since you had 6 in the first row of column B, the value 6 gets copied into all the rows below it.
So you can see the first row forms a "template" for all the rest of the rows. The resolution of improper data in the remaining rows is to adjust the first row of data (template) to give a formula for what you want in remaining rows. Attached, I have adjusted columns C with an if statement and column B with a formula to give results which now match your original data. But I'm not familiar enough with your calc to know if the logic will work right for other sets of input data.
=====================================
(2B)+(2B)' ?
RE: Fill rows based on number ented
The independent variable X will be created starting at 0 in column B according to the values you specify in cells labeled "DeltaX" and "NumberOfPoints". NumberOfPoints plays the same role as previously played by NumberOfCourses and accordingly the data updates only at the time you change NumberOfCourses.
I created the plot using chart wizard. Then I went back and adjusted the chart's property for data range of source data to point toward I named range that I created... named "datablock". You can inspect that variable to see what it does, but it implements the idea of dynamic range..... adjust to the actual number of data present so the chart is looking at the right range of data.
There is a more elegant way to do it (next post).
=====================================
(2B)+(2B)' ?
RE: Fill rows based on number ented
I'll take a look at it.
Thanks again.
EIT
RE: Fill rows based on number ented
http:
It uses array formulas which eliminates the need to even put any data in the spreadsheet.
Attached is a file I created using this method (plots 4 curves u,v,u,z as function of x).
=====================================
(2B)+(2B)' ?
RE: Fill rows based on number ented
In the macro you posted above on 9 Dec 11 13:41 . How is the input box that is determining the number of rows to copy, definded in the macro? To elaborate: Say I insert a few rows above row 11. Now the input box is on row 15. However this does not falter the macro, only the message box message is incorrect. How does the macro 'know' which box is the input?
Also say I wanted to copy a column and a row is this possible?
Thanks again.
EIT
www.HowToEngineer.com
RE: Fill rows based on number ented
I'm not Pete, but if I understand you correctly your question seems simple enough to answer. The box is given a "name" and referred to by that name in the macro. In Excel 2010, go to Formulas->Name Manager. In early versions I think it's under Insert->Name. You'll see "Ncourses" refers to "=Sheet1!$B$9". Excel automatically updates formulas as the cells move around. Then, in the macro, the name is used instead of the cell reference:
CODE
Anyway, what I really wanted to post was a method for getting the other technique to work under Excel 2010. The other technique uses a written equation and on-the-fly generated values, and uses "Evaluate" to iterate the equation. It seems the "Evaluate" formula disappeared somewhere around 2007 and these tricks don't work.
After a bit of mucking around I've found a simple solution:
- Insert a udf (User-defined formula)
- In the Developer tab, click the Visual Basic button.
- From the Insert menu, select Module
- Paste the following code:
Function Eval(r As String) As Variant - Hit save and switch back to the sheet.
- In the Forumlas tab, click the Name Manager button
- Replace all occurrences of "Evaluate" with "Eval"
- Ok, refresh, viola.
HTHCODE
Eval = Evaluate(r)
End Function
RE: Fill rows based on number ented
I'm hoping to expand on this again.
In Pete's attachment on 9 Dec 11 13:41 I was wondering if there is anyway to preform a operation on the results after 'filling' the cells. For example I copy down 50 cells. At the end I would like to sum the contents of these columns or maybe certain rows or columns. Or perform operations on the results but the target cells would need to be dynamic as well.
Let me know if you'd like me to attach the example.
Actually I will attach tomorrow.
EIT
www.HowToEngineer.com
RE: Fill rows based on number ented
Alternatively you could enter your calc cells directly, putting them outside the range of cells that get cleared by the script (say to the right of column E). For the calc formula ranges, you would just specify more than enough cells, say (A11:A500) for example. If the formula doesn't automatically ignore empty cells (many will), you might have to include a IF EMPTY statement or something.