IS there a way to speed up this loop?

Milbourn · Sep 6, 2017

I feel like this code is taking way too long to process (2-3 minutes). It's quite basic so was wondering if there were any ways I could improve it?

Code:

Sub Sample1()
Dim LastRow As Long, i As Long
Dim ws As Worksheet

Set ws = Sheets("Sheet1")

LastRow = ws.Range("A" & Rows.Count).End(xlUp).row


With ws
For i = 1 To LastRow
        If .Range("F" & i).Value = "" Or .Range("F" & i).Value = "-" Then _
        ws.Range("F" & i).Formula = "=IFERROR(INDEX('Sheet2'!C:C,MATCH('Sheet1'!A" & i & "&"""",'Sheet2'!A:A,0)),""-"")"
        Range("F" & i) = Range("F" & i).Value
        
        If .Range("G" & i).Value = "" Or .Range("G" & i).Value = "-" Then _
        ws.Range("G" & i).Formula = "=IFERROR(INDEX('Sheet2'!D:D,MATCH('Sheet1'!A" & i & "&"""",'Sheet2'!A:A,0)),""-"")"
        Range("G" & i) = Range("G" & i).Value
        
        If .Range("H" & i).Value = "" Or .Range("H" & i).Value = "-" Then _
        ws.Range("H" & i).Formula = "=IFERROR(INDEX('Sheet2'!H:H,MATCH('Sheet1'!A" & i & "&"""",'Sheet2'!A:A,0)),""-"")"
        Range("H" & i) = Range("H" & i).Value
        
        If .Range("I" & i).Value = "" Or .Range("I" & i).Value = "-" Then _
        ws.Range("I" & i).Formula = "=IFERROR(INDEX('Sheet2'!F:F,MATCH('Sheet1'!A" & i & "&"""",'Sheet2'!A:A,0)),""-"")"
        Range("I" & i) = Range("I" & i).Value
        
        If .Range("J" & i).Value = "" Or .Range("J" & i).Value = "-" Then _
        ws.Range("J" & i).Formula = "=IFERROR(INDEX('Sheet2'!E:E,MATCH('Sheet1'!A" & i & "&"""",'Sheet2'!A:A,0)),""-"")"
        Range("J" & i) = Range("J" & i).Value
        
        If .Range("K" & i).Value = "" Or .Range("K" & i).Value = "-" Then _
        ws.Range("K" & i).Formula = "=IFERROR(PROPER(INDEX('Sheet2'!L:L,MATCH('Sheet1'!A" & i & "&"""",'Sheet2'!A:A,0))),""-"")"
        Range("K" & i) = Range("K" & i).Value
        
        If .Range("L" & i).Value = "" Or .Range("L" & i).Value = "-" Then _
        ws.Range("L" & i).Formula = "=IFERROR(INDEX('Sheet2'!O:O,MATCH('Sheet1'!A" & i & "&"""",'Sheet2'!A:A,0)),""-"")"
        Range("L" & i) = Range("L" & i).Value
        
        If .Range("M" & i).Value = "" Or .Range("M" & i).Value = "-" Then _
        ws.Range("M" & i).Formula = "=IFERROR(INDEX('Sheet2'!I:I,MATCH('Sheet1'!A" & i & "&"""",'Sheet2'!A:A,0)),""-"")"
        Range("M" & i) = Range("M" & i).Value
    Next i
End With
End Sub

wideboydixon · Sep 6, 2017

I think using the formula to get the value is going to cause lots of calculations. You could probably try something like this:

Code:

Sub Sample1()

Dim lastRow As Long
Dim thisRow As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim matchIndex As Variant

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

lastRow = ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row
For thisRow = 1 To lastRow
    ' Get the index of this value from the second sheet
    matchIndex = Application.Match(ws1.Cells(thisRow, 1).Value, ws2.Range("A:A"), 0)
    
    ' Column F
    If ws1.Cells(thisRow, "F").Value = "" Or ws1.Cells(thisRow, "F").Value = "-" Then
        ws1.Cells(thisRow, "F").Value = IIf(IsError(matchIndex), "-", ws2.Cells(matchIndex, "C").Value)
    End If

    ' Column G
    If ws1.Cells(thisRow, "G").Value = "" Or ws1.Cells(thisRow, "G").Value = "-" Then
        ws1.Cells(thisRow, "G").Value = IIf(IsError(matchIndex), "-", ws2.Cells(matchIndex, "D").Value)
    End If

    ' Column H
    If ws1.Cells(thisRow, "H").Value = "" Or ws1.Cells(thisRow, "H").Value = "-" Then
        ws1.Cells(thisRow, "H").Value = IIf(IsError(matchIndex), "-", ws2.Cells(matchIndex, "H").Value)
    End If

    ' Column I
    If ws1.Cells(thisRow, "I").Value = "" Or ws1.Cells(thisRow, "I").Value = "-" Then
        ws1.Cells(thisRow, "I").Value = IIf(IsError(matchIndex), "-", ws2.Cells(matchIndex, "F").Value)
    End If

    ' Column J
    If ws1.Cells(thisRow, "J").Value = "" Or ws1.Cells(thisRow, "J").Value = "-" Then
        ws1.Cells(thisRow, "J").Value = IIf(IsError(matchIndex), "-", ws2.Cells(matchIndex, "E").Value)
    End If

    ' Column K
    If ws1.Cells(thisRow, "K").Value = "" Or ws1.Cells(thisRow, "K").Value = "-" Then
        ws1.Cells(thisRow, "K").Value = IIf(IsError(matchIndex), "-", StrConv(ws2.Cells(matchIndex, "L").Value, vbProperCase))
    End If

    ' Column L
    If ws1.Cells(thisRow, "L").Value = "" Or ws1.Cells(thisRow, "L").Value = "-" Then
        ws1.Cells(thisRow, "L").Value = IIf(IsError(matchIndex), "-", ws2.Cells(matchIndex, "O").Value)
    End If

    ' Column M
    If ws1.Cells(thisRow, "M").Value = "" Or ws1.Cells(thisRow, "M").Value = "-" Then
        ws1.Cells(thisRow, "M").Value = IIf(IsError(matchIndex), "-", ws2.Cells(matchIndex, "I").Value)
    End If
Next thisRow

End Sub

WBD

Milbourn · Sep 6, 2017

Heya! Thanks for your reply! I did look into not having these set out as formulas but I thought that the Application.Match/Vlookup would throw up the same kinda issue so just stuck with what I knew!

Thanks for putting together the code, as well, it looks like it's throwing up a type mismatch error 2042 at the moment (I think it's the same as in this link https://stackoverflow.com/questions/27302794/application-match-gives-type-mismatch). As I understand are their certain formats that this code has trouble processing?

RoryA · Sep 6, 2017

The problem is that IIf always evaluates both the True and False expressions, which causes an error. You need to use an If Then construct instead for each part:

Code:

If IsError(matchIndex) Then
   ws1.Cells(thisRow, "F").Value = "-"
Else
   ws1.Cells(thisRow, "F").Value = ws2.Cells(matchIndex, "C").Value)
End If

rather than:

Code:

ws1.Cells(thisRow, "F").Value = IIf(IsError(matchIndex), "-", ws2.Cells(matchIndex, "C").Value)

wideboydixon · Sep 6, 2017

RoryA said:
The problem is that IIf always evaluates both the True and False expressions, which causes an error. You need to use an If Then construct instead for each part:

Code:

If IsError(matchIndex) Then ws1.Cells(thisRow, "F").Value = "-" Else ws1.Cells(thisRow, "F").Value = ws2.Cells(matchIndex, "C").Value) End If

rather than:

Code:

ws1.Cells(thisRow, "F").Value = IIf(IsError(matchIndex), "-", ws2.Cells(matchIndex, "C").Value)

Doh! Thanks for that Rory. I always forget about IIf evaluating both expressions. Silly VBA. I don't think the same happens in C with the ? operator.

WBD

wideboydixon · Sep 6, 2017

I'm a nice guy. I changed the code

Code:

Sub Sample1()

Dim lastRow As Long
Dim thisRow As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim matchIndex As Variant

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

lastRow = ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row
For thisRow = 1 To lastRow
    ' Get the index of this value from the second sheet
    matchIndex = Application.Match(ws1.Cells(thisRow, 1).Value, ws2.Range("A:A"), 0)
    
    ' Save some time here
    
    ' Column F
    If ws1.Cells(thisRow, "F").Value = "" Or ws1.Cells(thisRow, "F").Value = "-" Then
        If IsError(matchIndex) Then
            ws1.Cells(thisRow, "F").Value = "-"
        Else
            ws1.Cells(thisRow, "F").Value = ws2.Cells(matchIndex, "C").Value
        End If
    End If

    ' Column G
    If ws1.Cells(thisRow, "G").Value = "" Or ws1.Cells(thisRow, "G").Value = "-" Then
        If IsError(matchIndex) Then
            ws1.Cells(thisRow, "G").Value = "-"
        Else
            ws1.Cells(thisRow, "G").Value = ws2.Cells(matchIndex, "D").Value
        End If
    End If

    ' Column H
    If ws1.Cells(thisRow, "H").Value = "" Or ws1.Cells(thisRow, "H").Value = "-" Then
        If IsError(matchIndex) Then
            ws1.Cells(thisRow, "H").Value = "-"
        Else
            ws1.Cells(thisRow, "H").Value = ws2.Cells(matchIndex, "H").Value
        End If
    End If

    ' Column I
    If ws1.Cells(thisRow, "I").Value = "" Or ws1.Cells(thisRow, "I").Value = "-" Then
        If IsError(matchIndex) Then
            ws1.Cells(thisRow, "I").Value = "-"
        Else
            ws1.Cells(thisRow, "I").Value = ws2.Cells(matchIndex, "F").Value
        End If
    End If

    ' Column J
    If ws1.Cells(thisRow, "J").Value = "" Or ws1.Cells(thisRow, "J").Value = "-" Then
        If IsError(matchIndex) Then
            ws1.Cells(thisRow, "J").Value = "-"
        Else
            ws1.Cells(thisRow, "J").Value = ws2.Cells(matchIndex, "E").Value
        End If
    End If

    ' Column K
    If ws1.Cells(thisRow, "K").Value = "" Or ws1.Cells(thisRow, "K").Value = "-" Then
        If IsError(matchIndex) Then
            ws1.Cells(thisRow, "K").Value = "-"
        Else
            ws1.Cells(thisRow, "K").Value = StrConv(ws2.Cells(matchIndex, "L").Value, vbProperCase)
        End If
    End If

    ' Column L
    If ws1.Cells(thisRow, "L").Value = "" Or ws1.Cells(thisRow, "L").Value = "-" Then
        If IsError(matchIndex) Then
            ws1.Cells(thisRow, "L").Value = "-"
        Else
            ws1.Cells(thisRow, "L").Value = ws2.Cells(matchIndex, "O").Value
        End If
    End If

    ' Column M
    If ws1.Cells(thisRow, "M").Value = "" Or ws1.Cells(thisRow, "M").Value = "-" Then
        If IsError(matchIndex) Then
            ws1.Cells(thisRow, "M").Value = "-"
        Else
            ws1.Cells(thisRow, "M").Value = ws2.Cells(matchIndex, "I").Value
        End If
    End If
Next thisRow

End Sub

WBD

Milbourn · Sep 6, 2017

You two are superstars, thanks for your help!

The results have been throwing up "-" for every result but I think it might just the the indexmatch column references so gonna have a look to see if i can fix it!T

thanks again!

Milbourn · Sep 6, 2017

Unfortuntely I can seem to stop it from triggering the

Code:

If IsError(matchIndex) Then
            ws1.Cells(thisRow, "F").Value = "

error on every row. The index match (though im unfamiliar with the way it's been set up) looks fine so i'm not sure as to why it's not working. :/

wideboydixon · Sep 6, 2017

Strange indeed. Here's my Sheet2:

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

a

$B$1

$C$1

$D$1

$E$1

$F$1

$G$1

$H$1

$I$1

$J$1

$K$1

$L$1

$M$1

$N$1

$O$1

2

b

$B$2

$C$2

$D$2

$E$2

$F$2

$G$2

$H$2

$I$2

$J$2

$K$2

$L$2

$M$2

$N$2

$O$2

3

c

$B$3

$C$3

$D$3

$E$3

$F$3

$G$3

$H$3

$I$3

$J$3

$K$3

$L$3

$M$3

$N$3

$O$3

4

d

$B$4

$C$4

$D$4

$E$4

$F$4

$G$4

$H$4

$I$4

$J$4

$K$4

$L$4

$M$4

$N$4

$O$4

5

e

$B$5

$C$5

$D$5

$E$5

$F$5

$G$5

$H$5

$I$5

$J$5

$K$5

$L$5

$M$5

$N$5

$O$5

6

f

$B$6

$C$6

$D$6

$E$6

$F$6

$G$6

$H$6

$I$6

$J$6

$K$6

$L$6

$M$6

$N$6

$O$6

Sheet2

Here's Sheet1 before the macro runs:

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

1

a

-

2

c

-

3

e

-

Sheet1

Running the macro then gives this:

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

1

a

-

$C$1

$D$1

$H$1

$F$1

$E$1

$l$1

$O$1

$I$1

2

c

-

$C$3

$D$3

$H$3

$F$3

$E$3

$l$3

$O$3

$I$3

3

e

-

$C$5

$D$5

$H$5

$F$5

$E$5

$l$5

$O$5

$I$5

Sheet1

Can you share any samples of the data in column A on Sheet1 and Sheet2?

WBD

Milbourn · Sep 8, 2017

Got it! The table I was performing the lookup on was all formatted as text and the lookup value was a number so it kept throwing up the error! (it's always the formatting...). I just added a & "" on the end and it all works perfectly!

Thanks again for your help wideboydixon!!

IS there a way to speed up this loop?

Milbourn

New Member

Excel Facts

wideboydixon

Well-known Member

Milbourn

New Member

RoryA

MrExcel MVP, Moderator

wideboydixon

Well-known Member

wideboydixon

Well-known Member

Milbourn

New Member

Milbourn

New Member

wideboydixon

Well-known Member

Milbourn

New Member

Similar threads

Forum statistics

Share this page

IS there a way to speed up this loop?

New Member

Excel Facts

Well-known Member

New Member

MrExcel MVP, Moderator

Well-known Member

Well-known Member

New Member

New Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock