Problem: Array Manipulation
1. Requirements:
- B4: Keep as it is.
- Data source: from B4 up to first non-empty cells in column B.
- Remove non-security code data (enclosed in parenthesis, e.g., (deposit), (subscribe), (etc.))
- Remove the duplicate
- Array data - sort in ascending order.
- Print output data in D4, D5, etc. depends on how many items are there in array.
2. VBA Coding
3. Need to learn more technique in data manipulations, too. This time, on how to use an array effectively.
Any idea, sirs?
Thanks in advance for your help.
1. Requirements:
- B4: Keep as it is.
- Data source: from B4 up to first non-empty cells in column B.
- Remove non-security code data (enclosed in parenthesis, e.g., (deposit), (subscribe), (etc.))
- Remove the duplicate
- Array data - sort in ascending order.
- Print output data in D4, D5, etc. depends on how many items are there in array.
2. VBA Coding
VBA Code:
Sub RefreshTSData()
Dim vMyArray() As Variant
Dim vNames As Range
Dim vCell As Variant
Dim iCtr As Long
Set vNames = Sheets("J2-Trading Journal").Range("A1:A7") 'A7 = last row of non-empty cells
iCtr = 0
For Each vCell In vNames
If Mid(vCell.Value, 1, 1) = "(" Then
'do not add to array
Else
'validate: if duplicate value, do not add to array
'validate: if unique value, add to array
iCtr = iCtr + 1
vMyArray(iCtr) = vCell 'error: out of range
End If
'Debug.Print vMyArray(iCtr)
Next vCell
Range("C1:C5000").Value = vMyArray.Value 'Error
End Sub
3. Need to learn more technique in data manipulations, too. This time, on how to use an array effectively.
Any idea, sirs?
Thanks in advance for your help.
Last edited: