CODEWITHSUNDEEP
vbaexcel
SarahM.
SarahM.

Reputation: 61

Excel VBA Copy Paste Values with conditions

I'm trying to copy values from one small sheet "MD with ID" to A Larger sheet "D with ID" if 2 fields are identical (consider those two as keys that identify each record).

Here is my first try:

Sub CopyIDCells()

Set i = Sheets("MD with ID")
Set e = Sheets("D with ID")
Dim d
Dim j
d = 1
j = 2

Do Until IsEmpty(e.Range("B" & j))
    d = 2
    Do Until IsEmpty(i.Range("A" & d))
        If e.Range("C" & j).Value = i.Range("D" & d).Value Then
            If e.Range("M" & j).Value = i.Range("J" & d).Value Then
                e.Range("A" & j).Value = i.Range("B" & d).Value
            End If
        End If
        d = d + 1
    Loop
    j = j + 1
Loop

End Sub

Here is my second try:

Sub CopyIDCells2()

Set i = Sheets("MD with ID")
Set e = Sheets("D with ID")
Dim d
Dim j
d = 1
j = 2

Do Until j = 20886
    d = 2
    Do Until d = 1742
        If e.Cells(j, 3).Value = i.Cells(d, 4).Value Then
            If e.Cells(j, 13).Value = i.Cells(d, 10).Value Then
                e.Cells(j, 1).Value = i.Cells(d, 2).Value
            End If
        End If
        d = d + 1
    Loop
    j = j + 1
Loop

End Sub

Nothing changes in the excel sheet when this code runs, although it takes few minutes to run -_-".

.. sample was removed

Upvotes: 2

Views: 1052

Answers (1)

Chewy
Chewy

Reputation: 651

So looking at your first CopyIdCells method, there is only one fix I would make to this - make variable d=2. This has headers at the top of your sample data and you need to start on row 2 just like the other sheet.

Sub CopyIDCells()

Set i = Sheets("MD with ID")
Set e = Sheets("D with ID")
Dim d
Dim j
d = 2
j = 2

Do Until IsEmpty(e.Range("B" & j))
    d = 2
    Do Until IsEmpty(i.Range("A" & d))
        If e.Range("C" & j).Value = i.Range("D" & d).Value Then
            If e.Range("M" & j).Value = i.Range("J" & d).Value Then
                e.Range("A" & j).Value = i.Range("B" & d).Value
            End If
        End If
        d = d + 1
    Loop
    j = j + 1
    Loop

End Sub

Other than that your formulas look good, you just do not have any data that meets your requirements. Add this column to the bottom of "MD with ID" and you will see your code match.

mouse   10  08  11267   A/J M   823 1/11/2008   1   SC-807  LONG    10/10/2005

Since you are matching on "Case Number" AND "Other ID" there are no items in both sheets that meet this criteria. When you add the row above to "MD with ID", you will see the appropriate ID added to your second sheet on several rows.

Upvotes: 0

Related Questions