I need a Faster loop

[Hal75]

Dear All,

I am new to Excel VBA. I have two sheets in the same workbook, Sheet1 & Production. In "Sheet1" I have two columns A & B with date and well names. I need to loop through these rows and compare to production sheet. If well name and date are matched then get production figure. I managed to get it working but my real sheet has over 23,000 rows and over 200 columns on both sheets so I need a faster and more efficient way of doing this. my current code looks like this:

Private Sub CommandButton1_Click()

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim iRow As Long
Dim jRow As Long
Dim kCol As Long

'Find the last non-blank row in Sheet1
iRow = Worksheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Offset(1).Row

'Find the last non-blank row in Production sheet
jRow = Worksheets("Production").Range("A" & Rows.Count).End(xlUp).Offset(1).Row

'Find the last non-blank column in Production sheet
kCol = Worksheets("Production").Cells(1, Columns.Count).End(xlToLeft).Column



For i = 2 To iRow
For j = 2 To jRow
For k = 2 To kCol


If Worksheets("Sheet1").Cells(i, 2) = Worksheets("Production").Cells(j, 1) Then

If Worksheets("Sheet1").Cells(i, 1) = Worksheets("Production").Cells(1, k) Then

Worksheets("Sheet1").Cells(i, 3) = Worksheets("Production").Cells(j, k)

End If
End If

Next k
Next j
Next i


End Sub







my "Sheet1" looks like this"

Name	Date	Production
Well A	01/01/2017
Well B	01/01/2017
Well C	01/01/2017
Well A	02/01/2017
Well B	02/01/2017
Well C	02/01/2017
Well A	03/01/2017
Well B	03/01/2017
Well C	03/01/2017

<tbody>
</tbody><colgroup><col><col><col></colgroup>

my "Production sheet looks like this:

Date	Well A	Well B	Well C
05/02/2015	1	10	100
06/02/2015	2	20	200
07/02/2015	3	30	300
08/02/2015	4	40	400
09/02/2015	5	50	500
10/02/2015	6	60	600
11/02/2015	7	70	700
12/02/2015	8	80	800
13/02/2015	9	90	900
14/02/2015	10	100	1000
15/02/2015	11	110	1100
01/01/2017	12	120	1200
02/01/2017	13	130	1300
03/01/2017	14	140	1400

<tbody>
</tbody><colgroup><col><col span="3"></colgroup>

For short number of rows and columns the code works well. However it takes forever when I ran it with real data with over 20,000 rows. Please help me to optimize the loop so it can runs faster.


Regards,

 

[kvsrinivasamurthy]

Try this

[COLOR=#333333]Private Sub CommandButton1_Click()[/COLOR]

Dim i As Integer
Dim iRow As Long
Dim jRow As Long
Dim kCol As Long
Dim Rng1 As Range, Rng2 As Range

'Find the last non-blank row in Sheet1
iRow = Worksheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Offset(1).Row

'Find the last non-blank row in Production sheet
jRow = Worksheets("Production").Range("A" & Rows.Count).End(xlUp).Offset(1).Row

'Find the last non-blank column in Production sheet
kCol = Worksheets("Production").Cells(1, Columns.Count).End(xlToLeft).Column

With Sheets("Sheet1")
For i = 2 To iRow
Set Rng1 = Worksheets("Production").Range("A1:A" & jRow).Find(.Cells(i, 2))
Set Rng2 = Worksheets("Production").Range(Cells(1, 1).Address, Cells(1, kCol).Address).Find(.Cells(i, 1))

If Not Rng1 Is Nothing And Not Rng2 Is Nothing Then
.Cells(i, 3) = Worksheets("Production").Cells(Rng1.Row, Rng2.Column)
End If

Next i
End With

End Sub

 

[jkpieterse]

Thanks but I have no clue with get and transform. An example would be useful.

Depending on your version of Excel the location on the ribbon is different. In My Excel 2016 from my Office 365 subscription I select the data, click on the Data tab of the ribbon and then on the left there is a group called Get & Transform Data with a button called "From Table/Range". CLick that button and confirm with the correct range. NExt the PowerQuery window will open with a new menu. Click on the Date column to select it (it will turn green). Then click on the Transform tab and click on the arrow to the right of "Unpivot columns" and select "Unpivot Other Columns". Now update the new column headers to what ever you like. Last step is clicking on the Home tab and on the dropdown called "Close and Load" on the far left and selecting "Close and load to". You can now decide whether to load to a table or use the data as the source for a pivottable directly.