Issue with Do...Until Function VBA

Question

I have an issue with my VBA code. I try to go through a whole table that has a lot of data. I go through a first column with a first condition required. Once this condition is complete, I go through the column next to the first one but starting at the same position I stopped the previous one. Once the second condition is complete, I try to do a copy paste. But for some reasons I got the error "Subscript out of Range" Could you please help me?

Here is the code:

Sub Match()

Dim i As Integer
i = 0
Dim j As Integer
Do
    i = i + 1
Loop Until Sheets("Sheet1").Range("A1").Offset(i, 0).Text = Sheets("Sheet2").Range("I5").Text
j = i
Do
    j = j + 1
Loop Until Sheets("Sheet1").Range("B1").Offset(j, 0).Value = Sheets("Sheet2").Range("I11").Value

Sheets("Sheet1").Range("C1").Offset(j, 0).Copy
Sheets("Sheet2").Range("N11").Paste

End Sub

Thanks guys

Are you sure your values ever line up? If the values you've hardcoded to check for matching don't line up, then it will run forever. — Grade 'Eh' Bacon
– Grade 'Eh' Bacon, Commented Jul 31, 2015 at 15:30
Yes the values line up. The first do loop don't even execute but I don't know why. — James
– James, Commented Jul 31, 2015 at 15:40
Right now you loop in A , going down the rows until you find the value of I5. Then you move on to column B. You start at the row AFTER the one where you found I5 in A and you move down until you find I11. When you find I11 in B, you copy the cell next to it (in C) and you paste in Sheet2, N11. That's what you want to do? — David G
– David G, Commented Jul 31, 2015 at 15:49

SierraOscar · Accepted Answer · 2015-07-31 16:12:22Z

1

This should do the same thing without any loops:

Sub Match()

Dim lastA As Long, lastB As Long
Dim i As Long, j As Long

With Sheets("Sheet1")
    last a = .Cells(.Rows.count, 1).End(xlUp).Row
    last b = .Cells(.Rows.count, 2).End(xlUp).Row
End With

i = WorksheetFunction.Match(Sheets("Sheet2").Range("I5").Text, Sheets("Sheet1").Range("A:A"), 0)
j = WorksheetFunction.Match(Sheets("Sheet2").Range("I11").value, Sheets("Sheet1").Range("B" & i & ":B" & lastB), 0)

Sheets("Sheet2").Range("N11").value = Sheets("Sheet1").Cells(j, 3).value

End Sub

answered Jul 31, 2015 at 16:12

SierraOscar

17.7k6 gold badges44 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SierraOscar Over a year ago

You could actually retrieve the same result just using an INDEX/MATCH formula without using any VBA

bbishopca · Accepted Answer · 2015-07-31 15:57:32Z

0

I didn't get the same error as you but I changed the last line and it seems to work.

Sub Match()

Dim i As Integer
i = 0
Dim j As Integer
Do
    i = i + 1
Loop Until Sheets("Sheet1").Range("A1").Offset(i, 0).Text = Sheets("Sheet2").Range("I5").Text
j = i
Do
    j = j + 1
Loop Until Sheets("Sheet1").Range("B1").Offset(j, 0).Value = Sheets("Sheet2").Range("I11").Value

Sheets("Sheet1").Range("C1").Offset(j, 0).Copy Destination:=Sheets("Sheet2").Range("N11")

End Sub

I did notice that your code runs for ever if you do not get a match which is not good. You may want to add a solution to this. It can be as easy as adding Or i > 10000 on the Loop Until lines.

answered Jul 31, 2015 at 15:57

bbishopca

2963 silver badges13 bronze badges

Comments

Gary's Student · Accepted Answer · 2015-07-31 15:59:25Z

0

I modified your code slightly:

Sub Match()

    Dim i As Integer
    i = 0
    Dim j As Integer

    Do
        i = i + 1
    Loop Until Sheets("Sheet1").Range("A1").Offset(i, 0).Text = Sheets("Sheet2").Range("I5").Text

    j = i
    Do
        j = j + 1
    Loop Until Sheets("Sheet1").Range("B1").Offset(j, 0).Value = Sheets("Sheet2").Range("I11").Value
    Sheets("Sheet1").Range("C1").Offset(j, 0).Copy Sheets("Sheet2").Range("N11")
End Sub

and it worked fine with data like:

In Sheet1.

Note the B match must be below the A match.

answered Jul 31, 2015 at 15:59

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Collectives™ on Stack Overflow

Issue with Do...Until Function VBA

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related