Find value on an other worksheet's column and if found, copy the row to that worksheet

[makiwara]

Hi! I need some help!

I Have 2 worksheets.

Sheet "2": The code copies the value from column "G" which is something like A11, A12, A13 etc. up to A316 (a for loop would be great)

Sheet"1": the code search for the copied value on Sheet "1" and finds "A11" in the 65. row

Then the code copies the data from Sheet "2" from column "H" and inserts it in Sheet "1'' in G65.

What I've got:

Sub FindAndChangeSomeCells()
Dim i As Integer
Dim code As String 'code means the A1, A2, and so on up to 316
Dim rownumber As String
Dim foundcell As Range




For i = 1 To 316 Step 1
code = Cells(i, "G").Value


Set foundcell = Sheets("1").Columns("A:A").Find(What:=code, LookIn:=xlFormulas, _
                                                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                                MatchCase:=False, SearchFormat:=False)


Worksheets("1").Activate
foundcell.Select


rownumber = ActiveCell.Row


Sheets("2").Range(i, "H").Value.Copy
Sheets("1").Range(rownumber, "G").Value.Insert


Next

End sub

But it doesn't work. Thank you for your time and effort!

 

[Fluff]

How about

Sub FindAndChangeSomeCells()
Dim i As Integer
Dim code As String 'code means the A1, A2, and so on up to 316
Dim foundcell As Range

For i = 1 To 316 Step 1
   code = Cells(i, "G").Value
   Set foundcell = Sheets("1").Columns("A:A").Find(What:=code, lookIn:=xlFormulas, _
                                                   LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                                   MatchCase:=False, SearchFormat:=False)
   
   If Not foundcell Is Nothing Then foundcell.Offset(, 6).Value Range("H" & i).Value
   Set foundcell = Nothing
Next i
End Sub

 

[makiwara]

Thank you Fluff!

If Not foundcell Is Nothing Then foundcell.Offset(, 6).Value Range("H" & i).Value" --> Invalid use of property" and it highlights the Value" word.

Any idea? Thank you for your time again, you are my multiple saver:D

How about

Sub FindAndChangeSomeCells()
Dim i As Integer
Dim code As String 'code means the A1, A2, and so on up to 316
Dim foundcell As Range

For i = 1 To 316 Step 1
   code = Cells(i, "G").Value
   Set foundcell = Sheets("1").Columns("A:A").Find(What:=code, lookIn:=xlFormulas, _
                                                   LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                                   MatchCase:=False, SearchFormat:=False)
   
   If Not foundcell Is Nothing Then foundcell.Offset(, 6).Value Range("H" & i).Value
   Set foundcell = Nothing
Next i
End Sub

 

[vmjan02]

Try this

replace this code
If Not foundcell Is Nothing Then foundcell.Offset(, 6).Value Range("H" & i).Value


to this code.
If Not foundcell Is Nothing Then foundcell.Offset(, 6).Value = Range("H" & i).Value

 

[Fluff]

Yup missed the = sign