K
kronik
So..
I would like to make a macro that will do a number of things with
one-click:
1. Automatically fill down a range of rows with a formula; the number
of rows to fill down are the number of rows there are in the A column.
2. Sort the newly created column/range with expanded selection for all
columns.
3. Either clear the row or these calculations could take place in a
hidden column.
I received some assistance over at VBAX.. the code for the formula is
as follows :
Code:
--------------------
Option Explicit
Public Function RetrieveSplitItem(Text As String, Separator As String, Item As Variant, _
Optional CaseSen As Boolean = False)
' Function based on post by Brad Yundt
' http://www.experts-exchange.com/Appl..._21221177.html
' Returns a specified substring from a larger string (Text) separated by a specified
' character sequence (Separator)
Dim X As Variant
If CaseSen Then
X = Split(Text, Separator, -1, vbBinaryCompare)
Else
X = Split(Text, Separator, -1, vbTextCompare)
End If
If IsNumeric(Item) And (Item < 1 Or Item > (UBound(X) + 1)) Then
RetrieveSplitItem = CVErr(xlErrNA)
ElseIf Not IsNumeric(Item) And Item <> "L" And Item <> "l" Then
RetrieveSplitItem = CVErr(xlErrNA)
Else
If Item = "L" Or Item = "l" Then Item = UBound(X) + 1
RetrieveSplitItem = X(Item - 1)
End If
End Function
Public Function StripOutCharType(CheckStr As String, Optional KillNumbers As Boolean = True, _
Optional AllowedChar As String, Optional NeverAllow As String) As String
' Function by Patrick Matthews
' For the given string, the function removes all numeric characters (KillNumbers=True) or
' all non-numeric characters (KillNumbers=False). Use AllowedChar to build a string of override
' characters that are always allowed. For example, "$,." would indicate that the dollar sign,
' comma, and period should all be allowed, even if KillNumbers=False; likewise, "9" would indicate
' that nines should be kept even if KillNumbers=True. NeverAllow is a string of override
' characters that are never allowed. The "never allowed" characters are processed before the
' "always allowed" characters, and so if any characters are in both strings Never allow takes
' precedence
Dim Counter As Long
Dim TestChar As String
Dim TestAsc As Long
' Loop through characters
For Counter = 1 To Len(CheckStr)
' Get current character and its ANSI number
TestChar = Mid(CheckStr, Counter, 1)
TestAsc = Asc(TestChar)
' Test first to see if current character is never allowed
If InStr(1, NeverAllow, TestChar, vbTextCompare) > 0 Then
' do nothing
' If current character is in AllowedChar, keep it
ElseIf InStr(1, AllowedChar, TestChar, vbTextCompare) > 0 Then
StripOutCharType = StripOutCharType & TestChar
' If KillNumbers=True, test for not being in numeric range for ANSI
ElseIf KillNumbers Then 'only allow non-numbers
If TestAsc < 48 Or TestAsc > 57 Then
StripOutCharType = StripOutCharType & TestChar
End If
' If KillNumbers=False, test for being in numeric ANSI range
Else 'only allow numbers
If TestAsc >= 48 And TestAsc <= 57 Then
StripOutCharType = StripOutCharType & TestChar
End If
End If
Next
End Function
--------------------
Then the formula using this function is
Code:
--------------------
=RetrieveSplitItem(A1,"-",1)&"-"&StripOutCharType(RetrieveSplitItem(A1,"-",2))&TEXT(StripOutCharType(RetrieveSplitItem(A1,"-",2),FALSE),"00000")
--------------------
The sample data for this is found attached. Column E is the desired
result from the sort.
+-------------------------------------------------------------------+
|Filename: test.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4448 |
+-------------------------------------------------------------------+
I would like to make a macro that will do a number of things with
one-click:
1. Automatically fill down a range of rows with a formula; the number
of rows to fill down are the number of rows there are in the A column.
2. Sort the newly created column/range with expanded selection for all
columns.
3. Either clear the row or these calculations could take place in a
hidden column.
I received some assistance over at VBAX.. the code for the formula is
as follows :
Code:
--------------------
Option Explicit
Public Function RetrieveSplitItem(Text As String, Separator As String, Item As Variant, _
Optional CaseSen As Boolean = False)
' Function based on post by Brad Yundt
' http://www.experts-exchange.com/Appl..._21221177.html
' Returns a specified substring from a larger string (Text) separated by a specified
' character sequence (Separator)
Dim X As Variant
If CaseSen Then
X = Split(Text, Separator, -1, vbBinaryCompare)
Else
X = Split(Text, Separator, -1, vbTextCompare)
End If
If IsNumeric(Item) And (Item < 1 Or Item > (UBound(X) + 1)) Then
RetrieveSplitItem = CVErr(xlErrNA)
ElseIf Not IsNumeric(Item) And Item <> "L" And Item <> "l" Then
RetrieveSplitItem = CVErr(xlErrNA)
Else
If Item = "L" Or Item = "l" Then Item = UBound(X) + 1
RetrieveSplitItem = X(Item - 1)
End If
End Function
Public Function StripOutCharType(CheckStr As String, Optional KillNumbers As Boolean = True, _
Optional AllowedChar As String, Optional NeverAllow As String) As String
' Function by Patrick Matthews
' For the given string, the function removes all numeric characters (KillNumbers=True) or
' all non-numeric characters (KillNumbers=False). Use AllowedChar to build a string of override
' characters that are always allowed. For example, "$,." would indicate that the dollar sign,
' comma, and period should all be allowed, even if KillNumbers=False; likewise, "9" would indicate
' that nines should be kept even if KillNumbers=True. NeverAllow is a string of override
' characters that are never allowed. The "never allowed" characters are processed before the
' "always allowed" characters, and so if any characters are in both strings Never allow takes
' precedence
Dim Counter As Long
Dim TestChar As String
Dim TestAsc As Long
' Loop through characters
For Counter = 1 To Len(CheckStr)
' Get current character and its ANSI number
TestChar = Mid(CheckStr, Counter, 1)
TestAsc = Asc(TestChar)
' Test first to see if current character is never allowed
If InStr(1, NeverAllow, TestChar, vbTextCompare) > 0 Then
' do nothing
' If current character is in AllowedChar, keep it
ElseIf InStr(1, AllowedChar, TestChar, vbTextCompare) > 0 Then
StripOutCharType = StripOutCharType & TestChar
' If KillNumbers=True, test for not being in numeric range for ANSI
ElseIf KillNumbers Then 'only allow non-numbers
If TestAsc < 48 Or TestAsc > 57 Then
StripOutCharType = StripOutCharType & TestChar
End If
' If KillNumbers=False, test for being in numeric ANSI range
Else 'only allow numbers
If TestAsc >= 48 And TestAsc <= 57 Then
StripOutCharType = StripOutCharType & TestChar
End If
End If
Next
End Function
--------------------
Then the formula using this function is
Code:
--------------------
=RetrieveSplitItem(A1,"-",1)&"-"&StripOutCharType(RetrieveSplitItem(A1,"-",2))&TEXT(StripOutCharType(RetrieveSplitItem(A1,"-",2),FALSE),"00000")
--------------------
The sample data for this is found attached. Column E is the desired
result from the sort.
+-------------------------------------------------------------------+
|Filename: test.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4448 |
+-------------------------------------------------------------------+