1

I am new to Excel VBA and do not have much experience. I have two worksheets of data that I compare and then if a value matches up I copy and paste it to the second worksheet. I use for loops to compare every row and was wondering if there is a better way to do this? I am using brute force currently and was hoping there would be a way so that my program will not run for very long. (I repeat this block of code on different sheets 13 times). Essentially this code is consolidating information if it meets certain conditions. Below is my code. 

   Sub consolidate(z)
      Sheets(z).Range("B1:AXH100").Delete '''deletes former values'''
      For i = 1 To 30
      For x = 1 To 500
      If IsEmpty(Sheets("Sheet1").Cells(x, 13)) Then 'if cell value is empty skip it'
          a = 1
      Else:
          If Sheets("Sheet1").Cells(x, 18) = Sheets(z).Cells(1, 1) Then 'check to see if value is same'
          If Sheets("Sheet1").Cells(x, 13) = Sheets(z).Cells(i, 1) Then 'check to see if value is same'
              Sheets("Sheet1").Cells(x, 15).Copy 'copy value'
              Sheets(z).Select 'select second sheet'
              Cells(i, 1).Select
              ActiveSheet.Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Offset(0, 1).Select 'offsets cell to the
  left'
              Selection.PasteSpecial past:=xlPasteValues 'pastes'
      End Sub
cxw
  • 16,685
  • 2
  • 45
  • 81
Mackenzie
  • 19
  • 2
  • 1
    Where ever possible you should try to avoid using selecting of cells, as this causes screen updating, which is slow. You could try putting in Application.ScreenUpdating = False at the start to see if that improves it. Here alternatives to select are given as [link] (http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) – Clusks Jul 04 '16 at 14:40
  • 2
    The code seems incomplete (missing `Next`s abd `End If`s) You can also avoid using `Copy` and just assig the values directly as in `targetRange.Value = sourceRange.Value`. – arcadeprecinct Jul 04 '16 at 14:42

1 Answers1

0

As I answered here, if you need to access or change several cells, you're much better off reading the cells into an array, working directly with the array(s) and sending the results back to Excel once you're done. It's quite a bit more work, and Excel's array-handling is not the best, but speed-wise there's no comparison.

This article explains the process in more detail.

Community
  • 1
  • 1
xarxziux
  • 371
  • 4
  • 13