UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> rank formula in VBA    
 
   
Hein
post Apr 11 2006, 10:40 AM
Post #1

UtterAccess Veteran
Posts: 406
From: The Netherlands



Hi
I am having trouble trying to capture a rather tedious work in VBA

As you can see in the attached Excel file I have several ranges of numbers 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 it with VBA?
e.g. how can I capture the action “end-down” in a rank-formula

Thanks for your helping hand

Hein
Attached File(s)
Attached File  Demo.zip ( 5.87K ) Number of downloads: 6
 
Go to the top of the page
 
+
4fit
post Apr 11 2006, 11:34 AM
Post #2

UtterAccess Veteran
Posts: 434
From: Graham, NC



.End(xlDown). But I don't believe that is going to help you much if you do it in the yellow columns because that would just take you to row 65536 since they are currently empty. Not quite sure I follow what you are after though.
Go to the top of the page
 
+
Hein
post Apr 11 2006, 12:00 PM
Post #3

UtterAccess Veteran
Posts: 406
From: The Netherlands



What I need is the rank of each number in e.g. range c8:c11 or in range c14:c62 etc.

I hoped to capture / record the making of a Rank formula but just recording this doesn't work, bit of code are left out.

What I would like in code is something like (various action for making the Rank formula):

=rank(RC[-1];RC[-1]: <<end-down>> ;1)

After this (or during the making of the previous step): make the reference range absolute (adding the $-signs in both column and row))
Last step is to copy this formula down for as many rows as there are rows in the reference range

Hope this is a help

Thanks Hein
Go to the top of the page
 
+
NateO
post Apr 11 2006, 02:02 PM
Post #4

Remembered
Posts: 5,055
From: Minneapolis, MN, USA



Hello,

What steps do you take to do this by hand? Here are mine:

CODE
Sub foobar()

Dim specRng As Range, cl As Range, tmpRng As Range

Dim i As Long

Application.ScreenUpdating = False

With Worksheets(1)

    .AutoFilterMode = False

    With Range("C:C")

        .AutoFilter Field:=1, Criteria1:=">0", Operator:=xlOr, _

            Criteria2:=vbNullString

        On Error Resume Next

        Set specRng = Intersect(.SpecialCells(xlBlanks).Offset(1), _

            .SpecialCells(xlConstants, xlNumbers))

        On Error GoTo 0

    End With

    If Not specRng Is Nothing Then

        For Each cl In specRng

            If Not IsEmpty(cl(2)) Then

                Set tmpRng = .Range(cl, cl.End(xlDown))

                Let i = tmpRng(1, 1).Row

                Let tmpRng.Offset(, 1).FormulaR1C1 = "=RANK(RC[-1],R" & _

                    i & "C3:R" & i + tmpRng.Rows.Count - 1 & "C3,1)"

            Else: cl(, 2) = 1

            End If

        Next

    End If

    .AutoFilterMode = False

End With

Application.ScreenUpdating = True

Set tmpRng = Nothing:   Set specRng = Nothing

End Sub

Hope this helps. (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif)
Go to the top of the page
 
+
Hein
post Apr 11 2006, 02:38 PM
Post #5

UtterAccess Veteran
Posts: 406
From: The Netherlands



Nate
Thanks for your input!
Looks more than impressive to me :-))

I've carefully copied your code onto the computer where I need the code (my internet computer is not my "work computer")
If I understand your code correctly it works (only?) on column C?

Trying to run the code it stops at "Operator:=x10r,"
Error message is Variable not defined.
When I Dim Operator as variant it still doesn't work

What can I do??

Thanks
Hein

Edit:
I've tried both using an O (letter) and an 0 (number zero) because I can't see the difference on my screen
But no matter what I change it keeps backfiring

Edited by: Hein on Tue Apr 11 16:00:28 EDT 2006.
Go to the top of the page
 
+
NateO
post Apr 11 2006, 03:02 PM
Post #6

Remembered
Posts: 5,055
From: Minneapolis, MN, USA



Hello,

You are welcome.

The 2nd letter is an L not a 1. That is: 'XLOR'.

Are you retyping that whole thing? (IMG:http://www.utteraccess.com/forum/style_emoticons/default/blush.gif)

Don't do that... Copy and paste it into a blank worksheet, then cut and paste it into a Module.

The code uses Column C to Populate Column D, in terms of filtering and identifying the ranges, not including text constants, e.g., '3000 m'. (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif)
Go to the top of the page
 
+
NateO
post Apr 11 2006, 03:04 PM
Post #7

Remembered
Posts: 5,055
From: Minneapolis, MN, USA



Actually, probably didn't need to filter in hindsight... The SpecialCells/Intersect combo should be good enough... (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif)
Go to the top of the page
 
+
Hein
post Apr 11 2006, 03:09 PM
Post #8

UtterAccess Veteran
Posts: 406
From: The Netherlands



Nate
Yes I'm retyping it
I have 1 computer only for the internet, the other is for everything else.
I use this because, despite having a virusscan, they still seem to creep in, so its for security reasons

Maybe I've mistyped the L and used an 1 in my respons to your threrad
It is definately a L in the code

But it keeps backfiring on the Operator bit: "Compile error Variable not defined" even though I've added
Dim operator as variant

Thanks again
Hein
Go to the top of the page
 
+
Hein
post Apr 11 2006, 03:19 PM
Post #9

UtterAccess Veteran
Posts: 406
From: The Netherlands



Nate
Apparently we're both typing at the same time
I've de-activated the line with " .autofilter etc " with a ' sign

Now it works perfectly!!
Muchos gracias, this saves me A LOT of time

For the other columns I've made seperate code, changing C:C to E:E, G:G etc
I've also changed C3 to C5 etc
These also work perfectly
Again: I'm impressed

Thanks
Hein
Go to the top of the page
 
+
NateO
post Apr 11 2006, 03:30 PM
Post #10

Remembered
Posts: 5,055
From: Minneapolis, MN, USA



You're welcome.

You can get rid of all of the Filtering code, that was my first though about working with the End Method, but it's not necessary, and yes, changing the column should work.

Operator is not a Variable, it's an argument for the Autofilter Function:

QUOTE
Function AutoFilter([Field], [Criteria1], [Operator As XlAutoFilterOperator = xlAnd], [Criteria2], [VisibleDropDown])
Member of Excel.Range

Perhaps you have a local-language version compatibility issue? (IMG:http://www.utteraccess.com/forum/style_emoticons/default/confused.gif)

In any event, don't need it here. (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif)
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 21st April 2014 - 07:38 AM