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

---
Denne e-mail er fri for virus og malware fordi avast! Antivirus beskyttelse er aktiveret.
http://www.avast.com

Misinformation - see this instead
 

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

---
Denne e-mail er fri for virus og malware fordi avast! Antivirus beskyttelse er aktiveret.
http://www.avast.com

Misinformation - see this instead
 

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, a écrit :
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

---
Denne e-mail er fri for virus og malware fordi avast! Antivirus beskyttelse er aktiveret.
http://www.avast.com

Misinformation - see this instead
 

On 1-Nov-2013, isabelle wrote:

=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

---
Denne e-mail er fri for virus og malware fordi avast! Antivirus beskyttelse er aktiveret.
http://www.avast.com

Misinformation - see this instead
 

Hi Kaj,

Am Fri, 1 Nov 2013 09:57:30 GMT schrieb :

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Misinformation - see this instead
 

Hi Claus Busch,


On 1-Nov-2013, Claus Busch wrote:

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

---
Denne e-mail er fri for virus og malware fordi avast! Antivirus beskyttelse er aktiveret.
http://www.avast.com

Misinformation - see this instead
 

Hi Kaj,

Am Fri, 1 Nov 2013 18:48:51 GMT schrieb :

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Misinformation - see this instead
 

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

---
Denne e-mail er fri for virus og malware fordi avast! Antivirus beskyttelse er aktiveret.
http://www.avast.com

Misinformation - see this instead
 

Hi Kaj,

Am Fri, 1 Nov 2013 20:17:10 GMT schrieb :

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Misinformation - see this instead
 

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

---
Denne e-mail er fri for virus og malware fordi avast! Antivirus beskyttelse er aktiveret.
http://www.avast.com