Array Validation | [A-Z] Sort + Remove Duplicates

SoniboiTM

New Member
Joined
Jul 25, 2020
Messages
22
Office Version
  1. 2019
Platform
  1. Windows
  2. Web
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

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?
Array.jpg


Thanks in advance for your help.
 
Last edited:

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

SoniboiTM

New Member
Joined
Jul 25, 2020
Messages
22
Office Version
  1. 2019
Platform
  1. Windows
  2. Web
Here's how you provided help for me. I can concentrate now on my other stuff.
I still have two (2) more concerns but I will try to solve it first. Then? Hope it will be okay to ask your help again... : )

Now, am not worried about losing data.

Because it is the program itself that will, ...

- format existing worksheets automatically
- validate the data
- update data every time I click on each worksheet
- apply conditional formatting on each worksheet (changing colors, alignments, table borders, etc)
- apply proper alignment and computations
- prepare report
- verify if the necessary worksheet(s) is/are available (I only need one general worksheet to be updated and the program will do the rest [note: still working in some areas])
- merge and unmerge rows and columns
- create named range (Name Manager)
- etc., etc., etc.

That's all spells accuracy.
 

Attachments

  • Untitled.png
    Untitled.png
    106.6 KB · Views: 5

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,216
Office Version
  1. 365
Platform
  1. Windows
If you get stuck, just start a new thread. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,191
Messages
5,546,476
Members
410,742
Latest member
WalterSil
Top