MACRO

[SHWETA GOEL]

Those columns are there but i removed just for convienence...
can u pls tell me how to write macro ...its really very urgent...

Thanks in advance

 

[Jacob Skaria]

Dear Shweta

Please try the below macro..If you are new to macros Set the Security level
to low/medium in (Tools|Macro|Security). 'Launch VBE using short-key Alt+F11.
Insert a module and paste the below code. Save. Get back to
Workbook...Tools|Macro|Run Macro1(). I assume your data starts from Row2..

B vendor
C invoice value
D is quanti
E is avg formula

The below macro will assign the forumla for RANK. Please try and feedback


Sub Macro1()
Dim lngRow, lngStartRange, lngLastUpdated
lngStartRange = 2

For lngRow = 2 To ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row + 1
If Trim(Range("B" & lngRow)) = "" Or (Trim(Range("A" & lngRow)) <> "" _
And lngRow <> 2) Then
If lngLastUpdated <> lngStartRange Then
lngLastUpdated = lngStartRange
Range("F" & lngStartRange & ":F" & lngRow - 1).Formula = "=RANK(E" & _
lngStartRange & ",$E$" & lngStartRange & ":$E$" & lngRow - 1 & ",1)"
End If
End If
If Trim(Range("A" & lngRow)) <> "" Then lngStartRange = lngRow
Next

End Sub



If this post helps click Yes

 

[SHWETA GOEL]

Hi,

After running this macro I am getting output like:

material vendor invoice quantity price rank
m1 v1 10 5 2 1
m1 v2 80 10 8 1
m1 v3 30 5 6 1
m1 v4 90 10 9 1
m2 v3 65 5 13 1
m2 v5 70 10 7 1


Regards
Shweta

 

[SHWETA GOEL]

Hi ,

U were right , now i got desired output...
Thanks a lot....
u r gr8 buddy

 

[Jacob Skaria]

I assume that Material will be referred only in the first row...as
below..Also going forward any questions on Programming or Macro you need to
post it under 'Excel Programming' and not under Excel General Questions.

material vendor invoice quantity price rank
m1 v1 10 5 2 1
v2 80 10 8 1
v3 30 5 6 1
v4 90 10 9 1
m2 v3 65 5 13 1
v5 70 10 7 1


If this post helps click Yes

 

[Jacob Skaria]

FYR..

http://www.microsoft.com/library/gallery/components/wn/3/locales/help/help_en-US.htm#RulesofConduct