Expert: Victor Lan - 11/8/2009

Question
QUESTION: Hi
sorry to email you directly previously..
below is what im trying to do

If cell H of Worksheet A = cell E of Worksheet B (contain words)
and
If cell J of Worksheet A = Cell H of Worksheet B (contain numbers)
and
If cell K of Worksheet A = cell I of Worksheet B (contain numbers)
then
copy cell O of Worksheet A to cell L of Worksheet B (contain numbers)

(put in other words
when a row in column H,J,K of Worksheet A = a row in column E,H,I of Worksheet B, copy that row's column O of worksheet A to column L worksheetB)
e.g. if H3, J3, K3 of worksheet A = E5, H5, I5 of worksheet B
then copy O3 of worksheet A to L5 of worksheetB

the macro is to match and copy for the whole worksheet of A and B
Data from worksheet A is only to be used once
I hope i provided enough details. Im new to vba. pls help. Thanks
______________________________________________________________

Below is what I got from one the experts. but it doesnt seems to copy anything (maybe I phased mine question wrongly.. hence i have rewrite the above problem).

Sub CopyData()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim j As Long, i As Long, lastrow As Long
Set sh1 = Worksheets("Worksheet A")
Set sh2 = Worksheets("Worksheet B")
lastrow = sh1.Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 To lastrow
j = (i - 2) * 4 + 1
If sh1.Cells(i, "H").Value = sh2.Cells(j, "E").Value And _
   sh1.Cells(i, "J").Value = sh2.Cells(j, "H").Value And _
   sh1.Cells(i, "K").Value = sh2.Cells(j, "I").Value Then
   sh1.Cells(i, "O").Copy sh2.Cells(j, "L")
End If
j = j + 4
Next
End Sub


ANSWER: Hi nsy,

The VBA code given is working fine. However, your description of the problem is not too clear.
I have amended the code slightly.

Sub CopyData()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim j As Long, i As Long, lastrow As Long
Set sh1 = Worksheets("Worksheet A")
Set sh2 = Worksheets("Worksheet B")
lastrow = sh1.Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 To lastrow
j = i + 2

If sh1.Cells(i, "H").Value = sh2.Cells(j, "E").Value And _
  sh1.Cells(i, "J").Value = sh2.Cells(j, "H").Value And _
  sh1.Cells(i, "K").Value = sh2.Cells(j, "I").Value Then
  sh1.Cells(i, "O").Copy sh2.Cells(j, "L")
End If

Next
End Sub


Please check if it works as you wanted.
If not, kindly send me your file so that I can help.

Thank you.

Regards,
Victor LAN


---------- FOLLOW-UP ----------

QUESTION: Hi Victor
thank you for replying. I actually wanted the worksheet A to match any row of worksheet B. below is what i got and its working the way i wanted.
however i have two questions
first i have changed the code from
lastrow = sh1.Cells(Rows.Count, "A").End(xlUp).Row
to
lastrow1 = sh1.Cells.SpecialCells(xlCellTypeLastCell).Row
in order to get it working (i dont understand why is this so. could you please explain to me the differences between the two?)

second question is that in order to prevent the data from worksheet A to be copied twice, i have changed it to cut and paste to worksheet B. i wonder if you have any better solution to do this?



   Dim sh1 As Worksheet, sh2 As Worksheet
   Dim j As Long, i As Long, lastrow1 As Long, lastrow2 As Long

   Set sh1 = Worksheets("WorksheetA")
   Set sh2 = Worksheets("WorksheetB")
   
   lastrow1 = sh1.Cells.SpecialCells(xlCellTypeLastCell).Row
   lastrow2 = sh2.Cells.SpecialCells(xlCellTypeLastCell).Row
   
   For i = 1 To lastrow1

       For j = 1 To lastrow2

           If sh1.Cells(i, "H").Value = sh2.Cells(j, "E").Value And _
              sh1.Cells(i, "J").Value = sh2.Cells(j, "H").Value And _
              sh1.Cells(i, "K").Value = sh2.Cells(j, "I").Value Then
              sh1.Cells(i, "O").Cut sh2.Cells(j, "L")
           End If

       Next j
   Next i



Thank you
nsy

Answer
Hi nsy,

The method SpecialCells(xlCellTypeLastCell) simply returns the range of the last cell that is used in the worksheet. This is the same as doing a Ctrl-End in the worksheet.

sh1.Cells(Rows.Count, "A").End(xlUp) returns the range of the last cell in column A that contains data.

I'm not too sure about the second question, I'm sure you know that copy-paste and cut-paste are different.

Hope it helps.
Cheers.

Regards,
Victor LAN