Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

Thread: How to speedup VBA

  1. #11
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    928
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to speedup VBA

    it is not entirely clear which which cells your variables are sitting in and I don't know which sheets are the source and destination, so I am sure this is not correct but it should show you how to do it:
    Code:
    Sub test()
    Dim outarr(1 To 7, 1 To 9) As Variant
    
    
    With Worksheets("sheet1")
     ' this is assumed to be the sheet with the red marks on it
     ' load all the data inot a variant array
     inarr = .Range("A1:E24")
    End With
    With Worksheets("sheet2")
     ' this is assumed to be othe sheet
      lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
        ' invoice No.
            outarr(1, 1) = inarr(3, 5) 'assumed to be E3
        ' date
            outarr(1, 2) = inarr(4, 5) ' Assumed to E4
        ' Name
            outarr(1, 3) = inarr(6, 3) ' Assumed to C6
         ' Mobile
            outarr(1, 3) = inarr(6, 5) ' Assumed to E6
         For j = 8 To 14 Step 1
           For k = 1 To 5
            outarr(j - 7, k + 4) = inarr(j, k)
           Next k
         Next j
       .Range(.Cells(lastrow + 1, 1), .Cells(lastrow + 7, 9)) = outarr
     End With
        
        
    
    
    End Sub
    Last edited by offthelip; Oct 9th, 2019 at 07:34 PM.
    Speed up your code use variant arrays and NEVER ACCESS THE WORKSHEET IN A LOOP

  2. #12
    Board Regular
    Join Date
    Jul 2012
    Posts
    74
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to speedup VBA

    Quote Originally Posted by offthelip View Post
    it is not entirely clear which which cells your variables are sitting in and I don't know which sheets are the source and destination, so I am sure this is not correct but it should show you how to do it:
    Code:
    Sub test()
    Dim outarr(1 To 7, 1 To 9) As Variant
    
    
    With Worksheets("sheet1")
     ' this is assumed to be the sheet with the red marks on it
     ' load all the data inot a variant array
     inarr = .Range("A1:E24")
    End With
    With Worksheets("sheet2")
     ' this is assumed to be othe sheet
      lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
        ' invoice No.
            outarr(1, 1) = inarr(3, 5) 'assumed to be E3
        ' date
            outarr(1, 2) = inarr(4, 5) ' Assumed to E4
        ' Name
            outarr(1, 3) = inarr(6, 3) ' Assumed to C6
         ' Mobile
            outarr(1, 3) = inarr(6, 5) ' Assumed to E6
         For j = 8 To 14 Step 1
           For k = 1 To 5
            outarr(j - 7, k + 4) = inarr(j, k)
           Next k
         Next j
       .Range(.Cells(lastrow + 1, 1), .Cells(lastrow + 7, 9)) = outarr
     End With
        
        
    
    
    End Sub

    Hi,
    Thank you so much for your help, almost done.
    If only one row in source sheet then its ok but if there is more then one then i want to repeat(inv, date,name, mobile)in sheet2
    please see i have attached the image as i want.


    Thanks once again.

    [IMG][/IMG]

  3. #13
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    928
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to speedup VBA

    try this:
    Code:
    Sub test()
    Dim outarr(1 To 7, 1 To 9) As Variant
    
    
    
    
    With Worksheets("sheet1")
     ' this is assumed to be the sheet with the red marks on it
     ' load all the data inot a variant array
     inarr = .Range("A1:E24")
    End With
    With Worksheets("sheet2")
     ' this is assumed to be othe sheet
      lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
         For j = 8 To 14 Step 1
        
        ' invoice No.
            outarr(j - 7, 1) = inarr(3, 5) 'assumed to be E3
        ' date
            outarr(j - 7, 2) = inarr(4, 5) ' Assumed to E4
        ' Name
            outarr(j - 7, 3) = inarr(6, 3)  ' Assumed to C6
         ' Mobile
            outarr(j - 7, 3) = inarr(6, 5) ' Assumed to E6
           For k = 1 To 5
            outarr(j - 7, k + 4) = inarr(j, k)
           Next k
         Next j
       .Range(.Cells(lastrow + 1, 1), .Cells(lastrow + 7, 9)) = outarr
     End With
    End Sub
    Speed up your code use variant arrays and NEVER ACCESS THE WORKSHEET IN A LOOP

  4. #14
    Board Regular
    Join Date
    Jul 2012
    Posts
    74
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to speedup VBA

    Hi,
    i tried this repeats 7 times, i want it should repeat only with data, if there is one row in source sheet repeat one time if two rows repeat two time.
    Thanks

  5. #15
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    928
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to speedup VBA

    put a test is to check if the data is blank
    Speed up your code use variant arrays and NEVER ACCESS THE WORKSHEET IN A LOOP

  6. #16
    Board Regular
    Join Date
    Jul 2012
    Posts
    74
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to speedup VBA

    Hi,

    It is coping/repeating (inv,date,name,mobile) with blank data

  7. #17
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    928
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to speedup VBA

    put this line in after the loop start:
    Code:
     For j = 8 To 14 Step 1
        if inarr(j,1)="" then exit for
    Speed up your code use variant arrays and NEVER ACCESS THE WORKSHEET IN A LOOP

  8. #18
    Board Regular
    Join Date
    Jul 2012
    Posts
    74
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to speedup VBA

    ***wonderful***
    I am very thank full and appreciate the way you help me.
    macro execution speed has been reduce from 3 second to 1 second.

  9. #19
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    928
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to speedup VBA

    I am pleased to hear that it is now much faster.
    Speed up your code use variant arrays and NEVER ACCESS THE WORKSHEET IN A LOOP

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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