Problem Statement
Now this time we have an interesting challenge, what we want to extract is, Nth text from a statement. For this I have created one user defined function. you have to pass two arguments, one text which you are looking for and second statement.
For Example:
In the below screenshot worksheets are not sorted in any order.
|
After running macro, they will be arranged in systematic manner
|
SOLUTION
Sub Arrange_Sheets()
Dim Sort_Descending As Boolean
Dim count_Sheets As Integer
Dim i As Integer
Dim j As Integer
No_of_Sheets = Sheets.Count
'Change Flag As appropriate
Sort_Descending = False
For i = 1 To No_of_Sheets
For j = 1 To i
If Sort_Descending = True Then
If UCase(Sheets(i).Name) > UCase(Sheets(j).Name) Then
Sheets(i).Move Before:=Sheets(j)
End If
End If
If Sort_Mode_Descending = False Then
If UCase(Sheets(i).Name) < UCase(Sheets(j).Name) Then
Sheets(i).Move Before:=Sheets(j)
End If
End If
Next j
Next i
End Sub
Dim Sort_Descending As Boolean
Dim count_Sheets As Integer
Dim i As Integer
Dim j As Integer
No_of_Sheets = Sheets.Count
'Change Flag As appropriate
Sort_Descending = False
For i = 1 To No_of_Sheets
For j = 1 To i
If Sort_Descending = True Then
If UCase(Sheets(i).Name) > UCase(Sheets(j).Name) Then
Sheets(i).Move Before:=Sheets(j)
End If
End If
If Sort_Mode_Descending = False Then
If UCase(Sheets(i).Name) < UCase(Sheets(j).Name) Then
Sheets(i).Move Before:=Sheets(j)
End If
End If
Next j
Next i
End Sub
STEPS TO PERFORM
1. Go to developer Tab else Press Alt + F11
2.Click on Visual Basic icon
3.Go to Insert click on Module
4.Paste below code in standard module
0 responses on "Find nth word from text string-EXCEL/VBA"