Hi
I want to sort a range say a1 to j15 containing various details of one's
mutual fund portfolio like company name, fund name, fund type, no of units
held, NAV , dividend received etc.
I have created sorting code from excel but is it possible to reduce this
code. I am using a separate function for sorting as shown below & passing
it 2 parameters viz. name of the sheet containing data & how to sort the
data i.e. by value or by fund name.
Also if anyone can explain the 2nd part of the sorting code & is it
necessary to do this part, I would be very thankful.
-------------------------------------------------------------------------------------------------------------------------
Sub sorting_Needs()
Dim i As String, j As Integer
abb: i = InputBox("Do you want to" & Chr(10) _
& "1. sort data by value" & Chr(10) _
& "2. Sort data by name", "Choice", 1)
Select Case i
Case Is = "1"
j = sort_data("Sheet1", Range("I:I"))
Case Is = "2"
j = sort_data("Sheet1", Range("b:b"))
Case Else
i = MsgBox("Please select a valid choice", vbRetryCancel, "Error
Massage")
If i = 4 Then
GoTo abb
End If
End Select
End Sub
Public Function sort_data(sh As String, col As Range)
'activate the data sheet
Sheets(sh).Activate
'select the entire data table in the sheet
Range("A1").Select
Selection.CurrentRegion.Select
'code generated by excel for sorting a range
ActiveWorkbook.Worksheets(sh).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(sh).Sort.SortFields.Add Key:=col _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
'2nd part
With ActiveWorkbook.Worksheets(sh).Sort
.SetRange Range("A1:j12")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Function
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
