Converts a string of items into range, with custom separator.
Opposite of Range2ANStr
Function ANStr2Range(ANString, ToCol, Optional Shee = "Active", Optional Wb = "This", Optional StartFromRow = 1, Optional Sepa = "|")
' Fill in a column from a string with list (ANStr type of string)
' Will clear entire column before filling it
' Will also put "Title" at row 1
' Returns number of items
'
' ##NOT Tested## '
If WB = "This" Then WB = ThisWorkbook.Name
If WB = "Active" Then WB = ActiveWorkbook.Name
If Shee = "Active" Then Shee = Workbooks(WB).Worksheets(1).Name
Workbooks(WB).Worksheets(Shee).Range(ToCol & 1).Entirecolumn.clearcontents
Workbooks(WB).Worksheets(Shee).Range(ToCol & 1).Value = "Title"
X1 = 1
For Each ANs1 in Split(ANString, Sepa)
If ANs1 > "" Then
Workbooks(WB).Worksheets(Shee).Range(ToCol & 1).Offset(X1).Value = ANs1
X1 = X1 + 1
End If
Next
ANStr2Range = X1 - 1
End Function
' Fill in a column from a string with list (ANStr type of string)
' Will clear entire column before filling it
' Will also put "Title" at row 1
' Returns number of items
'
' ##NOT Tested## '
If WB = "This" Then WB = ThisWorkbook.Name
If WB = "Active" Then WB = ActiveWorkbook.Name
If Shee = "Active" Then Shee = Workbooks(WB).Worksheets(1).Name
Workbooks(WB).Worksheets(Shee).Range(ToCol & 1).Entirecolumn.clearcontents
Workbooks(WB).Worksheets(Shee).Range(ToCol & 1).Value = "Title"
X1 = 1
For Each ANs1 in Split(ANString, Sepa)
If ANs1 > "" Then
Workbooks(WB).Worksheets(Shee).Range(ToCol & 1).Offset(X1).Value = ANs1
X1 = X1 + 1
End If
Next
ANStr2Range = X1 - 1
End Function
ANString, ToCol, Optional Shee = "Active", Optional Wb = "This", Optional StartFromRow = 1, Optional Sepa = "|"
Views 91
Downloads 35
CodeID
DB ID
ANmarAmdeen
607
Revisions
v1.0
Friday
November
11
2022