Newbie syntax question
 

Hello there, im a newbie here so pardon me if my questions seem a bit
too easy ;)

I have quite a lot of data sheets that contains assorted data in blocks
of 14 lines, so I wish to make a macro that creates a new column in the
left and fills it with numbers from 1 to 14 (line 2 = 1, line 3 = 2 ...
line 15 = 14, line 16 = 1, line 17 = 2 ... ). This would allow me to use
autofilters to extract data.

Unfortunately, my current macro uses an archaic copy/paste rinse and
repeat process that takes quite a while to fill the column. I figure a
faster way to do it, algorithm-wise, would be:

"FOR (line=2) TO (line=max) FILL_CELL_WITH (MOD( linenumber-2 , 14 ) +1
)"

line 2 - MOD 0,14 = 0+1 = 1
line 3 - MOD 1,14 = 1+1 = 2
(...)
line 15 - MOD 13,14 = 13+1 = 14
line 16 - MOD 14,14 = 0+1 = 1
line 17 - MOD 15,14 = 1+1 = 2
(...)

(I hope you understand my attempt at generic algorithm language)

What i need is the syntax for this FOR command on VBA, and how i could
detect the "max line" in my data sheet. Unfortunately, each block of 14
lines contains blank spaces in them :(

And for the record, i know i could learn this studying the manual, but
my grad school is currently taking all of my precious time :(

Thanks in advance for the help ;)


--
Nodles
------------------------------------------------------------------------
Nodles's Profile: http://www.excelforum.com/member.php...o&userid=29391
View this thread: http://www.excelforum.com/showthread...hreadid=514378

Newbie syntax question
 

For Each sh In Activeworkbook.Worksheets
iLastRow =sh.Cells(sh.Rows.Count,"A").End(xlUp).Row
sh.columns(1).Insert
sh.Range("A2:A" & iLastRow).Formula = "=ROW(A1)"
sh.Range("A2:A" & iLastRow).Value = sh.Range("A2:A" &
iLastRow).Value
Next sh

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Nodles" wrote in
message ...

Hello there, im a newbie here so pardon me if my questions seem a bit
too easy ;)

I have quite a lot of data sheets that contains assorted data in blocks
of 14 lines, so I wish to make a macro that creates a new column in the
left and fills it with numbers from 1 to 14 (line 2 = 1, line 3 = 2 ...
line 15 = 14, line 16 = 1, line 17 = 2 ... ). This would allow me to use
autofilters to extract data.

Unfortunately, my current macro uses an archaic copy/paste rinse and
repeat process that takes quite a while to fill the column. I figure a
faster way to do it, algorithm-wise, would be:

"FOR (line=2) TO (line=max) FILL_CELL_WITH (MOD( linenumber-2 , 14 ) +1
)"

line 2 - MOD 0,14 = 0+1 = 1
line 3 - MOD 1,14 = 1+1 = 2
(...)
line 15 - MOD 13,14 = 13+1 = 14
line 16 - MOD 14,14 = 0+1 = 1
line 17 - MOD 15,14 = 1+1 = 2
(...)

(I hope you understand my attempt at generic algorithm language)

What i need is the syntax for this FOR command on VBA, and how i could
detect the "max line" in my data sheet. Unfortunately, each block of 14
lines contains blank spaces in them :(

And for the record, i know i could learn this studying the manual, but
my grad school is currently taking all of my precious time :(

Thanks in advance for the help ;)


--
Nodles
------------------------------------------------------------------------
Nodles's Profile:
http://www.excelforum.com/member.php...o&userid=29391
View this thread: http://www.excelforum.com/showthread...hreadid=514378

Newbie syntax question
 

Try:

Sub x()
For Each sh In ActiveWorkbook.Worksheets
ilastrow = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row
sh.Columns(1).Insert
For i = 2 To ilastrow
sh.Range("A" & i) = ((i - 2) Mod 14) + 1
Next i
Next sh

End Sub


HTH
"Bob Phillips" wrote:


For Each sh In Activeworkbook.Worksheets
iLastRow =sh.Cells(sh.Rows.Count,"A").End(xlUp).Row
sh.columns(1).Insert
sh.Range("A2:A" & iLastRow).Formula = "=ROW(A1)"
sh.Range("A2:A" & iLastRow).Value = sh.Range("A2:A" &
iLastRow).Value
Next sh

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Nodles" wrote in
message ...

Hello there, im a newbie here so pardon me if my questions seem a bit
too easy ;)

I have quite a lot of data sheets that contains assorted data in blocks
of 14 lines, so I wish to make a macro that creates a new column in the
left and fills it with numbers from 1 to 14 (line 2 = 1, line 3 = 2 ...
line 15 = 14, line 16 = 1, line 17 = 2 ... ). This would allow me to use
autofilters to extract data.

Unfortunately, my current macro uses an archaic copy/paste rinse and
repeat process that takes quite a while to fill the column. I figure a
faster way to do it, algorithm-wise, would be:

"FOR (line=2) TO (line=max) FILL_CELL_WITH (MOD( linenumber-2 , 14 ) +1
)"

line 2 - MOD 0,14 = 0+1 = 1
line 3 - MOD 1,14 = 1+1 = 2
(...)
line 15 - MOD 13,14 = 13+1 = 14
line 16 - MOD 14,14 = 0+1 = 1
line 17 - MOD 15,14 = 1+1 = 2
(...)

(I hope you understand my attempt at generic algorithm language)

What i need is the syntax for this FOR command on VBA, and how i could
detect the "max line" in my data sheet. Unfortunately, each block of 14
lines contains blank spaces in them :(

And for the record, i know i could learn this studying the manual, but
my grad school is currently taking all of my precious time :(

Thanks in advance for the help ;)


--
Nodles
------------------------------------------------------------------------
Nodles's Profile:
http://www.excelforum.com/member.php...o&userid=29391
View this thread: http://www.excelforum.com/showthread...hreadid=514378