If one cell equals another, the copy and paste a different cell

Question

Ok. I have the following code that keeps crashing Excel for me, but I can't figure out why. I have basically the same exact code that I have used multiple times with no problem.

Basically, I have last names and first names in two different workbooks, and if they equal each other, I want to copy the date of birth from the one workbook to other workbook.

Sub Macro4()

Dim iRow As Integer, cRow As Integer

iRow = 4

Dim wbC As Workbook, wbA As Workbook, wsC As Worksheet, wsA As Worksheet

Set wbC = Workbooks("Coventry Enroll Census  113014")
Set wbA = Workbooks("Copy of Ameritas Enroll Census Template 1 1 15 (2).xls")
Set wsC = wbC.Sheets(1)
Set wsA = wbA.Sheets(2)

Do: cRow = 2 'Reset cRow
        'DOB
        If wsC.Cells(cRow, 2) = wsA.Cells(iRow, 2) And wsC.Cells(cRow, 1) = wsA.Cells(iRow, 3)Then
         wsC.Cells(cRow, 10).Select
         Selection.Copy
           Range(wsA.Cells(iRow, 6)).PasteSpecial (xlPasteValues)
         iRow = iRow + 1
        Else
        cRow = cRow + 1

        End If

        Loop Until wsC.Cells(cRow, 1) = "" And wsC.Cells(cRow + 1, 1) = "" And wsC.Cells(cRow + 2, 1) = ""
End Sub

What happens when it crashes? Do you get an error or does Excel just close? The `selection.copy` part could be problematic since that would take a chunk of memory — Matt, Dec 09 '14 at 20:15
Excel just closes. I didn't think it would be too much memory because it is only one cell. — Tyler Yoder, Dec 09 '14 at 20:24

score 1 · Accepted Answer · answered Dec 09 '14 at 20:33

1

I think you created and endless loop as you reset cRow at the begin of each loop. So probably the until conditions never will be true because cRow is either 2 or 3. I can't really explain the crashing but I think it is related to that. Try setting cRow = 2 before the Do of the loop.

answered Dec 09 '14 at 20:33

Fratyx

5,717
1
12
22

I think I added that into this code without thinking about it, so my other ones didn't do that endless thing. – Tyler Yoder Dec 09 '14 at 20:42
@TylerYoder: Sh.t happens ;-) Good luck. – Fratyx Dec 09 '14 at 20:46

score 0 · Answer 2 · edited May 23 '17 at 12:12

Try setting the values directly instead of selecting them. I can't say for sure if this is going to solve your problem without having your workbooks and system, but give it a shot.

Also, Check out this link about avoiding using Select statements in VBA.

Do: cRow = 2 'Reset cRow
        'DOB
        If wsC.Cells(cRow, 2) = wsA.Cells(iRow, 2) And wsC.Cells(cRow, 1) = wsA.Cells(iRow, 3) Then
            wsA.Cells(iRow, 6).Value = wsC.Cells(cRow, 10).Value    'THIS SETS ONLY THE VALUE.
            iRow = iRow + 1
        Else
            cRow = cRow + 1
        End If

        Loop Until wsC.Cells(cRow, 1) = "" And wsC.Cells(cRow + 1, 1) = "" And wsC.Cells(cRow + 2, 1) = ""
End Sub

SIDE NOTE: Are you intending to include the file extention in wbA and not wbC? From your code:

Set wbC = Workbooks("Coventry Enroll Census  113014")
Set wbA = Workbooks("Copy of Ameritas Enroll Census Template 1 1 15 (2).xls")

edit notes: Added Side Note.

Thanks for that link about Select statements! I'm sure it will come in handy for me down the road. — Tyler Yoder, Dec 09 '14 at 20:44

If one cell equals another, the copy and paste a different cell

2 Answers2