Fill rows based on number ented 2

[RFreund]

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!


EIT

 

[RFreund]

*Entered

EIT

 

[MintJulep]

No. It's not possible because you have not provided any logical basis to do anything.

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?

 

[electricpete]

Agreed. Please come up with a coherent statement of what you are trying to accomplish.

=====================================
(2B)+(2B)' ?

 

[electricpete]

Posting an example spreadsheet (maybe before/after the fill operation) may help us understand

=====================================
(2B)+(2B)' ?

 

[RFreund]

ok.ok. I hear ya.

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

 

[electricpete]

See attached for vba-solution triggered by worksheet change event

=====================================
(2B)+(2B)' ?

 

[RFreund]

Thanks pete!

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

 

[electricpete]

How to view the code in Excel 2003 (my version):
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:

Private Sub Worksheet_Change(ByVal Target As Range)
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

Note that to make it work there has to be defined range name "ULHC" (for upper left hand corner) pointing to the first column of the first row of data.

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)' ?

 

[RFreund]

Pete the only issue is that one of the columns is returning 0 instead of the function output. - See attached (Column C11, C12, etc.)

Thanks again!

EIT

 

[RFreund]

Also I wanted to ask if there is any other way to solve this problem?

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

 

[electricpete]

Pete the only issue is that one of the columns is returning 0 instead of the function output. - See attached (Column C11, C12, etc.)

All the macro does is an equivalent of a copy from the first row of data into N-1 rows below it (relative formula's are adjusted be excel in the copy process).

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)' ?

 

[electricpete]

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.

Attached I have used the exact same macro to accomplish what you're after.

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)' ?

 

[RFreund]

Wow, you are good.

I'll take a look at it.
Thanks again.

EIT

 

[electricpete]

Here is a link to the other more elegant method.

http://www.excelforum.com/excel-cha...h-from-an-equation-and-not-a-set-of-data.html

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)' ?

 

[RFreund]

Pete -

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

http://www.HowToEngineer.com

 

[LiteYear]

How is the input box that is determining the number of rows to copy, definded in the macro?

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:

Range("Ncourses").Value

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:
[ol]
[li]Insert a udf (User-defined formula)[/li]
[ol a]
[li]In the Developer tab, click the Visual Basic button.[/li]
[li]From the Insert menu, select Module[/li]
[/ol]
[li]Paste the following code:[/li]

Function Eval(r As String) As Variant
    Eval = Evaluate(r)
End Function

[li]Hit save and switch back to the sheet.[/li]
[li]In the Forumlas tab, click the Name Manager button[/li]
[li]Replace all occurrences of "Evaluate" with "Eval"[/li]
[li]Ok, refresh, viola.[/li]
[/ol]

HTH

 

[RFreund]

Thanks LiteYear.

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

http://www.HowToEngineer.com

 

[LiteYear]

A straightforward approach would be just to add in your calculation cells at the end of the VBA script that creates the data cells. So after the Next col/Next row loops, you'd just add Range("ULHC").Offset(0, Range("Ncourses").Value+1).Value = "SUM(blah blah)" or whatever.

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.