Excel VBA Copy Paste Values with conditions

Question

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

Can you provide at least a sample of the input? Like, what is in `A1` through `A10` in sheet "MD with ID" and what is in `B2` through `B10` in sheet "D with ID"? Other inputs will likely be useful, too. — Matt Cremeens, Jun 08 '16 at 13:25
Try using the Value2 property instead of Value when accessing the data in the cells. Depending on what the data looks like it might fix it. http://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2 — , Jun 08 '16 at 13:34
Also try inserting a couple debug statements to see what comparisons your code is making and how the logic is working. I'd put one after your first `If` statement: `Debug.Print "e.Cells(" & j & ",3) equals i.Cells(" & d & ",4) --> " & e.Cells(j,3).value & " = " & i.Cells(d,4).value`. Then probably another debug print after the next `If` statement as well. If you can visually inspect where you know there is am equivalence that should be copied, then adjust your loop parameters to focus on that area when you're testing. — PeterT, Jun 08 '16 at 13:43
Why do you have this? If e.Cells(j, 3).Value = i.Cells(d, 4).Value Then If e.Cells(j, 13).Value = i.Cells(d, 10).Value Then . Why not to change it to If (e.Cells(j, 3).Value = i.Cells(d, 4).Value) And (e.Cells(j, 13).Value = i.Cells(d, 10).Value) Then saves you an if and possibly time execution. Also, why are there 2 do until? Shouldn't be faster determine which one is the longest of both and then do the cycle based on that? — Sgdva, Jun 08 '16 at 14:32
@Sgdva you are right about the nested ifs are not necessary here, but the nested loop is because I'm not sure if the data are in the same sort in both sheets so I need to loop the shortest sheet for each record in the large sheet. — SarahM., Jun 08 '16 at 21:02
I can say that nothing happens with your data in this sample because there are no matching case numbers or "other ids", so there is nothing to copy over. — Chewy, Jun 08 '16 at 21:10
I've added an answer to reflect the comment above and help to display to you what I think is your issue. Once I made the changes below I could see updates to your sheet. — Chewy, Jun 08 '16 at 21:24

score 0 · Answer 1 · answered Jun 08 '16 at 21:19

0

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.

answered Jun 08 '16 at 21:19

Chewy

651
6
21

What about if there are blank among rows? It would exit before desired – Sgdva Jun 08 '16 at 21:59
Of course it would. It depends on her data set. She has two versions of the code. One goes through thousands of rows and the other is designed to exit on the first blank. The underlying code in both is essentially the same. So both treated the same should work the same. Just depends on her intentions. Same edit would just need to apply to the second script although I did not verify that one. Just looks like she wanted at least one to work by the question. – Chewy Jun 08 '16 at 22:46
I have tried to fix it but it does not work apparently the if condition is always false even when the value is supposed to be true! thankfully I was able to make the changes by python thank you all for your help :) – SarahM. Jun 09 '16 at 11:27
Yeah, that is weird. It worked just fine with your test data. I am wondering if your sheet names have an extra space somewhere.... – Chewy Jun 09 '16 at 11:43

Excel VBA Copy Paste Values with conditions

1 Answers1