UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
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: 412
From: The Netherlands



Hi
I am having trouble trying to capture a rather tedious work in VBA
Is 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: 412
From: The Netherlands



What I need is the rank of each number in e.g. range c8:c11 or in range c14:c62 etc.
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()
im 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. wink.gif
Go to the top of the page
 
+
Hein
post Apr 11 2006, 02:38 PM
Post #5

UtterAccess Veteran
Posts: 412
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? 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'. 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... wink.gif
Go to the top of the page
 
+
Hein
post Apr 11 2006, 03:09 PM
Post #8

UtterAccess Veteran
Posts: 412
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: 412
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
How 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:

Perhaps you have a local-language version compatibility issue? confused.gif

In any event, don't need it here. 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: 25th October 2014 - 08:01 PM