Results 1 to 10 of 16

Thread: Speed up Loop VBA

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Senior Member
    Join Date
    Oct 2011
    Posts
    135
    Rep Power
    13

    Speed up Loop VBA

    Hi,

    I have a column with numeric data and a function of a search for unique data would assign the position.
    The code should work with 200,000 rows and 20,000 different values (all listed)

    Unique list:
    123.20
    11.40
    34.00
    12.10

    Column G
    123.20
    11.40
    123.20
    34.00
    11.40
    12.10
    ---------------
    Unique list:
    123.20 |1
    11.40 |2
    123.20 |1
    34.00 |3
    11.40 |2
    12.10 |4

    I attach an example to improve.

    Code:
    Sub Add_Value()
        Dim row As Long, descr As String
        Application.ScreenUpdating = False
        row = 2
        Do While Cells(row, "G").Value <> ""
            Select Case Cells(row, "G").Value
                Case 123.20: descr = 1
                Case 11.40: descr = 2
                Case 34.00: descr = 3
                Case 12.10: descr = 4
                'Ecc...
    '            Case Else: descr = ""
            End Select
            Cells(row, "H").Value = descr
            row = row + 1
        Loop
        Application.ScreenUpdating = True
        
    End Sub
    Last edited by PcMax; 04-07-2012 at 04:21 PM.

Similar Threads

  1. Replies: 1
    Last Post: 06-12-2013, 07:42 PM
  2. VBA Trick of the Week :: Slicing an Array Without Loop - Application.Index
    By Transformer in forum Tips, Tricks & Downloads (No Questions)
    Replies: 0
    Last Post: 06-12-2013, 04:40 PM
  3. Vlookup Multiple Values By Adding Formula With Loop In VBA
    By Safal Shrestha in forum Excel Help
    Replies: 15
    Last Post: 04-22-2013, 04:49 PM
  4. Replies: 2
    Last Post: 04-16-2013, 01:36 PM
  5. Speed up excel to word VBA
    By bcostin in forum Excel Help
    Replies: 3
    Last Post: 05-22-2012, 10:49 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •