I need to expand these cells, (it's always +16), how can i do it?

=IF('01'!B1=1444093;"Standard";"Turbo")
=IF('01'!B17=1444093;"Standard";"Turbo")
=IF('01'!B33=1444093;"Standard";"Turbo")

, when i expand it, it should continue like this:

=IF('01'!B49=1444093;"Standard";"Turbo")
=IF('01'!B65=1444093;"Standard";"Turbo")

and so on..

i don't think (AFAIK) you can do it with a formula. you'd have to
write a macro to insert that formula (with the correct row number)
every 16 rows for as far down as you needed.
hth
susan


On Apr 2, 2:46 pm, "TripleX" wrote:
I need to expand these cells, (it's always +16), how can i do it?

=IF('01'!B1=1444093;"Standard";"Turbo")
=IF('01'!B17=1444093;"Standard";"Turbo")
=IF('01'!B33=1444093;"Standard";"Turbo")

, when i expand it, it should continue like this:

=IF('01'!B49=1444093;"Standard";"Turbo")
=IF('01'!B65=1444093;"Standard";"Turbo")

and so on..

Assuming you do not need thousands of these formulas then this formula should
work for you...

=IF(INDIRECT("'01'!B" & (ROW()-1)*16+1)=1444093,"Standard","Turbo")

Note that the formula is volatile so that it recalcs every time. That means
that it has lots of overhead assocaited with it so you do not want thousands
of these formulas in your spread sheet. If the preforamce starts to drop off
significantly then you might need to rethink this solution...
--
HTH...

Jim Thomlinson


"TripleX" wrote:

I need to expand these cells, (it's always +16), how can i do it?

=IF('01'!B1=1444093;"Standard";"Turbo")
=IF('01'!B17=1444093;"Standard";"Turbo")
=IF('01'!B33=1444093;"Standard";"Turbo")

, when i expand it, it should continue like this:

=IF('01'!B49=1444093;"Standard";"Turbo")
=IF('01'!B65=1444093;"Standard";"Turbo")

and so on..

this is what i've come up with so far.
the only part i have to research is parsing out the formula string
to get the actual row number of the formula entered to equal
startrow............
susan
xxxxxxxxxxxxxxx

Option Explicit

Sub expanding_numbers()

Dim StartRow As Long
Dim myFormula As String
Dim myColumn As Range
Dim ws As Worksheet
Dim rRow As Range

Set ws = ActiveWorkbook.ActiveSheet
StartRow = InputBox("What row would you like this cycle to start on?")
myFormula = InputBox("Please enter the formula you would like" _
& "distributed every 16 rows.")

Set myColumn = ws.Range("d:d")

'now you have to pull apart the formula they entered & find
'the first number after ! and change that to startrow's
'value & each time change myformula to equal startrow.

Do Until StartRow = 20000
For Each rRow In myColumn
'myFormula = parsed string with correct startrow
StartRow = myFormula
StartRow = StartRow + 16
Next rRow
Loop

End Sub

xxxxxxxxxxxxxxxxxxxxx

On Apr 2, 3:03 pm, "Susan" wrote:
i don't think (AFAIK) you can do it with a formula. you'd have to
write a macro to insert that formula (with the correct row number)
every 16 rows for as far down as you needed.
hth
susan

On Apr 2, 2:46 pm, "TripleX" wrote:



I need to expand these cells, (it's always +16), how can i do it?

=IF('01'!B1=1444093;"Standard";"Turbo")
=IF('01'!B17=1444093;"Standard";"Turbo")
=IF('01'!B33=1444093;"Standard";"Turbo")

, when i expand it, it should continue like this:

=IF('01'!B49=1444093;"Standard";"Turbo")
=IF('01'!B65=1444093;"Standard";"Turbo")

and so on..- Hide quoted text -

- Show quoted text -

Note that the formula is volatile so that it recalcs every time. That
means
that it has lots of overhead assocaited with it so you do not want
thousands
of these formulas in your spread sheet. If the preforamce starts to drop
off
significantly then you might need to rethink this solution...

The problem is that I do need about 4500 (1100*4 columns) of these
formulas...
But maybe there is some other way to do this, so this is the link to the xls
file i made, if you could look at it, maybe you'll see a better way to do
it..
http://rapidshare.com/files/24018157/example.rar

TNX!

i made a couple of little changes, but i've never worked with parsing
out a string & i can't figure it out today............
maybe somebody else can help with that part??
:)
susan

Option Explicit

Sub expanding_numbers()

Dim StartRow As Long
Dim myFormula As String
Dim myColumn As Range
Dim TargetColumn As Range
Dim ws As Worksheet
Dim rRow As Range
Dim myNewRow As Long
Dim sArray() As String

Set ws = ActiveWorkbook.ActiveSheet
StartRow = InputBox("What row would you like this cycle to start on?")
myFormula = InputBox("Please enter the formula you would like" _
& "distributed every 16 rows.")

Set myColumn = ws.Range("d:d")
Set TargetColumn = ws.Range("b:b")

'now you have to pull apart the formula they entered & find
'the first number after ! and change that to startrow's
'value & each time change myformula to equal startrow.

Do Until StartRow = 20000 Or TargetColumn.Value = ""
For Each rRow In myColumn
' sArray = Split(myFormula, "/")
' Then sArray(0) = "text up until /+1"
' sArray(1) = "next 2 numbers"
' sArray(2) = "rest of formula after row numbers"
' sarray(1) = startrow
'myformula = sarray(0) & startrow & sarray(2)
'parsed out string with startrow
StartRow = myFormula
StartRow = StartRow + 16
Next rRow
Loop

End Sub





On Apr 2, 3:31 pm, "Susan" wrote:
this is what i've come up with so far.
the only part i have to research is parsing out the formula string
to get the actual row number of the formula entered to equal
startrow............
susan
xxxxxxxxxxxxxxx

Option Explicit

Sub expanding_numbers()

Dim StartRow As Long
Dim myFormula As String
Dim myColumn As Range
Dim ws As Worksheet
Dim rRow As Range

Set ws = ActiveWorkbook.ActiveSheet
StartRow = InputBox("What row would you like this cycle to start on?")
myFormula = InputBox("Please enter the formula you would like" _
& "distributed every 16 rows.")

Set myColumn = ws.Range("d:d")

'now you have to pull apart the formula they entered & find
'the first number after ! and change that to startrow's
'value & each time change myformula to equal startrow.

Do Until StartRow = 20000
For Each rRow In myColumn
'myFormula = parsed string with correct startrow
StartRow = myFormula
StartRow = StartRow + 16
Next rRow
Loop

End Sub

xxxxxxxxxxxxxxxxxxxxx

On Apr 2, 3:03 pm, "Susan" wrote:



i don't think (AFAIK) you can do it with a formula. you'd have to
write a macro to insert that formula (with the correct row number)
every 16 rows for as far down as you needed.
hth
susan

On Apr 2, 2:46 pm, "TripleX" wrote:

I need to expand these cells, (it's always +16), how can i do it?

=IF('01'!B1=1444093;"Standard";"Turbo")
=IF('01'!B17=1444093;"Standard";"Turbo")
=IF('01'!B33=1444093;"Standard";"Turbo")

, when i expand it, it should continue like this:

=IF('01'!B49=1444093;"Standard";"Turbo")
=IF('01'!B65=1444093;"Standard";"Turbo")

and so on..- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

"Susan" wrote in message
oups.com...

Tnx Susan, I don't understand that much, but I'll certaintly try your way..

Hi TripleX -

=IF(OFFSET('01'!$B$1,(ROW()-0-1)*16,0,1,1)=1444093,"Standard","Turbo")

Copy this to Row 1 and 'expand' downward. Note that if you want to start
your list of these formulas in any other row than row 1, say for example row
10, then:

1. Copy the formula to cell A10 (or any other column in row 10).
2. Change the characters "-0" to "-9" to correct for the 'distance' from row
1.
3. Copy (expand) the formula downward.

---
Jay


"TripleX" wrote:

I need to expand these cells, (it's always +16), how can i do it?

=IF('01'!B1=1444093;"Standard";"Turbo")
=IF('01'!B17=1444093;"Standard";"Turbo")
=IF('01'!B33=1444093;"Standard";"Turbo")

, when i expand it, it should continue like this:

=IF('01'!B49=1444093;"Standard";"Turbo")
=IF('01'!B65=1444093;"Standard";"Turbo")

and so on..

"Jay" wrote in message
...
Hi TripleX -

=IF(OFFSET('01'!$B$1,(ROW()-0-1)*16,0,1,1)=1444093,"Standard","Turbo")

It's returning me an error, somethings wrong about "$B$1,".

Sub Increment_Formula_Steps()
'copy a formula down with steps in cell references
'select range first with formula in active cell
Dim StepSize As Variant
Dim NumCopies As Integer
Dim cell As Range
StepSize = InputBox("Step?") ''e.g., 16
If StepSize < "" Then
NumCopies = Selection.Rows.Count
Application.ScreenUpdating = False
For Each cell In Selection.Columns(1).Cells
cell.Copy cell.Offset(StepSize)
cell.Offset(StepSize).Cut cell.Offset(1)
Next
End If
End Sub


Gord Dibben MS Excel MVP

On Mon, 2 Apr 2007 20:46:50 +0200, "TripleX" wrote:

I need to expand these cells, (it's always +16), how can i do it?

=IF('01'!B1=1444093;"Standard";"Turbo")
=IF('01'!B17=1444093;"Standard";"Turbo")
=IF('01'!B33=1444093;"Standard";"Turbo")

, when i expand it, it should continue like this:

=IF('01'!B49=1444093;"Standard";"Turbo")
=IF('01'!B65=1444093;"Standard";"Turbo")

and so on..

Sorry as a policy I don't open files submitted because of viruses and such.
It is not that I don't trust you... It is just that I don't trust anyone...
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

Assuming you do not need thousands of these formulas then this formula should
work for you...

=IF(INDIRECT("'01'!B" & (ROW()-1)*16+1)=1444093,"Standard","Turbo")

Note that the formula is volatile so that it recalcs every time. That means
that it has lots of overhead assocaited with it so you do not want thousands
of these formulas in your spread sheet. If the preforamce starts to drop off
significantly then you might need to rethink this solution...
--
HTH...

Jim Thomlinson


"TripleX" wrote:

I need to expand these cells, (it's always +16), how can i do it?

=IF('01'!B1=1444093;"Standard";"Turbo")
=IF('01'!B17=1444093;"Standard";"Turbo")
=IF('01'!B33=1444093;"Standard";"Turbo")

, when i expand it, it should continue like this:

=IF('01'!B49=1444093;"Standard";"Turbo")
=IF('01'!B65=1444093;"Standard";"Turbo")

and so on..

Hi TripleX -

The formula I supplied works fine under my test conditions. Make sure the
worksheet is actually named '01' with no leading or trailing spaces and no
quotes, just 01.

If that doesn't work, repost the formula that is producing the error and
we'll take another look at it.
--
Jay


"TripleX" wrote:


"Jay" wrote in message
...
Hi TripleX -

=IF(OFFSET('01'!$B$1,(ROW()-0-1)*16,0,1,1)=1444093,"Standard","Turbo")

It's returning me an error, somethings wrong about "$B$1,".

It's working now, I've made a little changes, You've posted this formula
=IF(OFFSET('01'!$B$1,(ROW()-0-1)*16,0,1,1)=1444093,"Standard","Turbo")

but instead of commas there should be, I forgot the word, but there should
be this sign " ; "

Big tnx to all of you for your help, Jay solution is working great..