Need macro to add numbers in rows

[-]

Hi,

I hope someone can help.
I need a macro to do the following:

Range A1:A1000
Jump to next row which is not empty
Jump one row backwards
Write the number "1"

Jump to next row which is not empty
Jump one row backwards
Write the number "2"

and so on until selected ranged is completed

Then I need another macro that will be able to find and delete all of the
numbers entered.


Regards,
Kaj Pedersen

 

[-]

I hope someone can help.
I need a macro to do the following:

Range A1:A1000
Jump to next row which is empty
Jump one row forwards
Write the number "1"

Jump to next row which is empty
Jump one row forwards
Write the number "2"

and continue this way until selected ranged is completed

Then I need another macro that will be able to find and delete all of the
numbers entered.


Regards,
Kaj Pedersen

 

[isabelle]

hi Kaj Pedersen,

without macro you can copy this formula in cell A1 then fill down
=IF(MOD(ROW(),2)=1,CEILING(ROW()/2,1),"")

isabelle

Le 2013-11-01 05:57, (e-mail address removed) a écrit :

 

[-]

=IF(MOD(ROW(),2)=1,CEILING(ROW()/2,1),"")

---------------------------------------------------------------------------------

Hi,

Unfortunately, this seems not to be usable in my case.
Pulling down this formula also deletes existing data and this is not what I
want.

Therefore, I think the only way is to let a macro do the job.

Still hoping for a solution

Regards,
Kaj Pedersen

 

[Claus Busch]

Hi Kaj,

Am Fri, 1 Nov 2013 09:57:30 GMT schrieb (e-mail address removed):

Range A1:A1000
Jump to next row which is empty
Jump one row forwards
Write the number "1"

Jump to next row which is empty
Jump one row forwards
Write the number "2"

what should happen if the row below the empty row has any entry?

Then I need another macro that will be able to find and delete all of the
numbers entered.

Why do you want to enter numbers if you then want to delete them?

Regards
Claus B.

 

[-]

Hi Claus Busch,

what should happen if the row below the empty row has any entry?

Answer: Overwrite

Why do you want to enter numbers if you then want to delete them?

Answer: I wan't the possibility to renumber from the beginning (A1) in case
a new row is inserted or if a row is deleted.
Perhaps this can be done another way?

Kaj Pedersen

 

[Claus Busch]

Hi Kaj,

Am Fri, 1 Nov 2013 18:48:51 GMT schrieb (e-mail address removed):

Answer: I wan't the possibility to renumber from the beginning (A1) in case
a new row is inserted or if a row is deleted.
Perhaps this can be done another way?

write in A1:
=ROW()
and copy down


Regards
Claus B.

 

[-]

Hi,

I think I have not explained clearly, what I want the macro to do:

Here is an example of the spreadsheet.:


A B C D
1
2 1
3 text
4 text
5
6 2
7 text
8
9 3
10 text
11
12 4
13 text
14 text
15



The row before a number is always blank and should also be left blank after
the macro running.
Text may be on 2 or 3 rows after each other. This text must not be deleted.
The only thing I want to renew (overwrite) is the numbers
The numbers are consecutive (From 1 to ?)

Hope this will help to find a solution.

Kaj Pedersen

 

[Claus Busch]

Hi Kaj,

Am Fri, 1 Nov 2013 20:17:10 GMT schrieb (e-mail address removed):

A B C D
1
2 1
3 text
4 text
5
6 2
7 text
8
9 3
10 text
11
12 4
13 text
14 text
15

try:

Sub Test()
Dim LRow As Long
Dim rngC As Range
Dim i As Long

LRow = Cells(Rows.Count, 1).End(xlUp).Row
For Each rngC In Range("A1:A" & LRow)
If IsNumeric(rngC) And Not IsEmpty(rngC) Then
i = i + 1
rngC = i
End If
Next
End Sub


Regards
Claus B.

 

[-]

Hi Claus,

Your proposal was fantastic.
It works to my full satisfaction. Thank you very much.

After this I realized, I do NOT need another macro to delete the numbers
again.
Once again Thank you.

Regards,
Kaj Pedersen