Search for a value and copy to different sheet

[robcarr]

Dear All,

I am trying to change some coding that was given to me before to no avail, I am trying to do the following, check each value in column A on daily data, and find the location of this value in sheet1, when it finds the value in sheet1 it will copy the value in column B on daily data for that item, and paste the value in the farmost column in sheet1(sheet1 columns are always complete block of data, but rows can have blanks, but would be no more than 250 rows), but I am getting confused with the coding, daily data sheet values in column A will always appear on sheet1.

Coding


Sub copythedata()
Dim Match As Object
Dim i As Integer

i = 1
With Worksheets("Sheet1&quot.UsedRange.Columns(1)
Do
Set Match = .Find(Worksheets("Daily Data&quot.Cells(i, 1), LookAt:=xlWhole)
If Not Match Is Nothing Then Worksheets("Daily Data&quot.Cells(Match.Row, 2) _
= Worksheets("sheet1&quot.Cells(i, 15)
i = i + 1
Loop While Not Len(Worksheets("daily Data&quot.Cells(i, 1)) = 0
End With

Application.ScreenUpdating = True

End Sub

Any help will be greatly appreciated, I am sure the coding works as I used it on a similar example but cannot recreate it again.

Thanks in advance Thanks Rob.

 

[mmandk9]

Rob,

Assuming the above is the code you are using, what results are you getting when you execute?

MM

 

[robcarr]

the codiing doesnt seem to do anything I am getting lost with it, i can run it and no changes appear on the sheets, also I need to alter slightly or find better way to do it.

Thanks Rob.

 

[andrew299]

Robcarr,
Try this variation of your code and see if it does what you want.

Sub copythedata()
Dim Match As Object
Dim i As Integer
Dim xx
i = 1
With Worksheets("main&quot.UsedRange.Columns(1)
Do
Set Match = .Find(Worksheets("Daily Data&quot.Cells(i, 1), LookAt:=xlWhole)
If Not Match Is Nothing Then
Worksheets("Daily Data&quot.Select
Cells(i, 2).Select
xx = ActiveCell.Value

Worksheets("main&quot.Select
Cells(i, 15).Select
ActiveCell.Value = xx
End If
i = i + 1
Loop While Not Len(Worksheets("daily Data&quot.Cells(i, 1)) = 0
End With

Application.ScreenUpdating = True

End Sub

Andrew 299

 

[mmandk9]

robcarr,

I got the same results with the above code (I added the end if statement). I did switch the line in the if statement from:

Worksheets("Daily Data&quot.Cells(Match.Row, 2) _
= Worksheets("sheet1&quot.Cells(i, 15)

to:

Worksheets("sheet1&quot.Cells(i, 15) _
= Worksheets("Daily Data&quot.Cells(Match.Row, 2)

and it seemed to work...

Hope this is helpful.