Results 1 to 4 of 4

Replace loops with arrays

Threaded View

  1. #1
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Replace loops with arrays

    Hello everyone
    I have a code that has nested loops for columns and rows in sheet1 and for each cell check if it is greater than zero .. if the value is greater than a zero so the related data to be transferred
    In sheet1 I have fixed row (1) and fixed column (1) and in between the cells to be checked
    For example the cell A2 has value greater than zero so the related data would be:
    -----------------
    B1 in Sheet1 >> Column C in Sheet2
    A2 in Sheet1 >> Column I in Sheet2
    The cell itself in B2 >> Column L in Sheet2
    As for Sales ID in Sheet2 Would be sequence for Customers

    You can run the working code to see the expected results
    Sub Test()
        Dim r1, r2, c, t, e
        
        r1 = Sheets(1).Range("a" & Rows.Count).End(xlUp).Row
        r2 = Sheets(2).Range("a" & Rows.Count).End(xlUp).Row + 1
        Sheets(2).Range("A2:l" & r2) = ""
        Application.ScreenUpdating = False
        c = Sheets(1).Range("www1").End(xlToLeft).Column
        For t = 2 To c
            For e = 2 To r1
                If Sheets(1).Cells(e, t) > 0 Then
                    r2 = Sheets(2).Range("a" & Rows.Count).End(xlUp).Row + 1
                    Sheets(2).Range("a" & r2) = t - 1
                    Sheets(2).Range("c" & r2) = Sheets(1).Cells(1, t).Value
                    Sheets(2).Range("i" & r2) = Sheets(1).Cells(e, 1).Value
                    Sheets(2).Range("l" & r2) = Sheets(1).Cells(e, t).Value
                End If
            Next
        Next
        Application.ScreenUpdating = True
    End Sub
    Hope it is clear
    Attached Files Attached Files
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Loops and arrays
    By LadyB in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-12-2013, 04:28 PM
  2. Syntax for arrays and loops in VBA
    By DataMole in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-23-2012, 12:43 PM
  3. [SOLVED] Help with loops & arrays for encrypt/decrypt program
    By Strength.Honor in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-27-2012, 10:50 PM
  4. Need help with loops and arrays in VBA Macro
    By lealea1982 in forum Excel Programming / VBA / Macros
    Replies: 23
    Last Post: 08-26-2011, 08:51 AM
  5. VBA Loops + Arrays
    By Kengh in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-26-2010, 07:41 PM
  6. Loops and Arrays
    By v2jtb in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-29-2010, 12:09 PM
  7. [SOLVED] slow for loops ... better way using arrays or something?
    By wdeleo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-28-2006, 10:35 AM
  8. [SOLVED] VBA excel using arrays and loops
    By vincentwongau@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-06-2006, 08:55 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1