MACRO

  • Thread starter Thread starter SHWETA GOEL
  • Start date Start date
S

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
 
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
 
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
 
Hi ,

U were right , now i got desired output...
Thanks a lot....
u r gr8 buddy
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top