Hi all
I am not an experienced writer of VBA but I would like to capture m
next problem since this comes back quite a lot.

As you can see in the attached Excel file I have several columns o
varying length. To the right of each column there is an empty colum
(coloured yellow).

What I need to do is calculate the rank of a number in the column nex
to the yellow cells (e.g. range c8..c11) As you can see I need t
repeat this in this file alone several times, and this is only 1 o
several 100s of files!

I know what steps to take when doing it by hand but how can I captur
the next actions in VBA?

1. type =Rank(
2. go to the top cell of the range I need to calculate the rank an
select this cell and type ";"
3. go to the same cell, type "." and go to the bottom cell of th
needed range (simply "end-down" won't do the trick since sometimes th
range of cells has blanks)
4. make the selected cells "absolute" by typing F4
5. "enter"
6. copy the range with the ranks which I've just made and make th
values absolute through "edit-paste special and choosing values"

This last procedure is needed because I need the outcome in a
different program

I hope it is not too much asked but thanks for your helping hand

Hei

--
Hei
-----------------------------------------------------------------------
Hein's Profile: http://www.excelforum.com/member.php...fo&userid=2474
View this thread: http://www.excelforum.com/showthread.php?threadid=53134

Hi Hein,

I believe that attachments are stripped when sent to this forum. At least I
haven't seen any for months and it seems I read a similar reply to an
attachment post.
If you know how you could post a link. I don't know so can't help you
there. Some folk post a screen shot with a link and I don't know how to do
that either.

But, to your problem.
Turn on the macro recorder and do all the stuff you listed and take a look
at that. If the code needs tinkering with, you can post the code along with
details.

HTH
Regards,
Howard

"Hein" wrote in message
...

Hi all
I am not an experienced writer of VBA but I would like to capture my
next problem since this comes back quite a lot.

As you can see in the attached Excel file I have several columns of
varying length. To the right of each column there is an empty column
(coloured yellow).

What I need to do is calculate the rank of a number in the column next
to the yellow cells (e.g. range c8..c11) As you can see I need to
repeat this in this file alone several times, and this is only 1 of
several 100s of files!

I know what steps to take when doing it by hand but how can I capture
the next actions in VBA?

1. type =Rank(
2. go to the top cell of the range I need to calculate the rank and
select this cell and type ";"
3. go to the same cell, type "." and go to the bottom cell of the
needed range (simply "end-down" won't do the trick since sometimes the
range of cells has blanks)
4. make the selected cells "absolute" by typing F4
5. "enter"
6. copy the range with the ranks which I've just made and make the
values absolute through "edit-paste special and choosing values"

This last procedure is needed because I need the outcome in an
different program

I hope it is not too much asked but thanks for your helping hand

Hein


--
Hein
------------------------------------------------------------------------
Hein's Profile:
http://www.excelforum.com/member.php...o&userid=24744
View this thread: http://www.excelforum.com/showthread...hreadid=531342

This is not just a "forum", accessible via a web page. it's also a
"newsgroup", part of Usenet. Each individual news server has its own
management which makes its own policies about whether to accept
attachments, how long to keep old messages on the server, whether to
accept new groups, and so on.

http://www.smr-usenet.com/tech/how.shtml

Steven


Je Sun, 9 Apr 2006 15:24:59 -0700, "L. Howard Kittle"
skribis:

Hi Hein,

I believe that attachments are stripped when sent to this forum. At least I
haven't seen any for months and it seems I read a similar reply to an
attachment post.
If you know how you could post a link. I don't know so can't help you
there. Some folk post a screen shot with a link and I don't know how to do
that either.

But, to your problem.
Turn on the macro recorder and do all the stuff you listed and take a look
at that. If the code needs tinkering with, you can post the code along with
details.

HTH
Regards,
Howard

"Hein" wrote in message
...

Hi all
I am not an experienced writer of VBA but I would like to capture my
next problem since this comes back quite a lot.

As you can see in the attached Excel file I have several columns of
varying length. To the right of each column there is an empty column
(coloured yellow).

What I need to do is calculate the rank of a number in the column next
to the yellow cells (e.g. range c8..c11) As you can see I need to
repeat this in this file alone several times, and this is only 1 of
several 100s of files!

I know what steps to take when doing it by hand but how can I capture
the next actions in VBA?

1. type =Rank(
2. go to the top cell of the range I need to calculate the rank and
select this cell and type ";"
3. go to the same cell, type "." and go to the bottom cell of the
needed range (simply "end-down" won't do the trick since sometimes the
range of cells has blanks)
4. make the selected cells "absolute" by typing F4
5. "enter"
6. copy the range with the ranks which I've just made and make the
values absolute through "edit-paste special and choosing values"

This last procedure is needed because I need the outcome in an
different program

I hope it is not too much asked but thanks for your helping hand


--
Steven M - lid
(remove wax and invalid to reply)

A fool and his money are soon elected. -- Will Rogers

Hi Steve and Howard

I believe I forgot to attach the file, sorry for that (hope it will
work?!)


I tried to do it by recording the actions but I didn't get the results
I wanted.

Hopefully the attachment is coming through

Thanks Hein


+-------------------------------------------------------------------+
|Filename: Demo.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4611 |
+-------------------------------------------------------------------+

--
Hein
------------------------------------------------------------------------
Hein's Profile: http://www.excelforum.com/member.php...o&userid=24744
View this thread: http://www.excelforum.com/showthread...hreadid=531342