VBA to Copy Sheet1.Cell to Sheet2.Cell if sheet1.cell = sheet2.cell

[Mgas]

I am an excel VBA novice and get lots of errors when writing VBA to manipulate Excel sheet data.

I get lost with the Excel object concepts/code

The following psuedo-code shows what I want to do

Sheet1.Range(a1b1:c1d1) ' where a1,c1 = row and b1,d1 = col

Sheet2.Range(a2b2:c2d2) ' where a2,c2 = row and b2,d2 = col

For rw1 = a1 to c1 ' outer loop
For rw2 = a2 to c2 ' inner loop
if Sheet1.Cell(rw1:A) = Sheet2.Cell(rw2:A)
Copy Sheet2.Cell(rw2:D) to Sheet1.Cell(rw1:G)
Escape/Exit loop ' escape/exit inner loop
EndiF
EndFor 'end inner loop
EndFor 'end outer loop

Basically ..for each row in sheet 1, find a match in sheet2 and then copy a cell from the matching row in Sheet2 to the corresponding sheet1 row.

Can anyone help me with the VBA to do the above? its fairly urgent and I cant spare my usual trial and error time to try and figure it out.

Many thanks

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Mgas()
    Application.ScreenUpdating = False
    Dim Cl As Range
    
    With CreateObject("scripting.dictionary")
      For Each Cl In Sheet2.Range("A2", Sheet2.Range("A" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Cl.Offset(, 3).Value
      Next Cl
      For Each Cl In Sheet1.Range("A2", Sheet1.Range("A" & Rows.Count).End(xlUp))
         If .exists(Cl.Value) Then Cl.Offset(, 6).Value = .Item(Cl.Value)
      Next Cl
   End With
End Sub

 

[Mgas]

Hi
This worked fine, first time. Thanks very much,.

I would never have thought of coding it like this. it took me a while to work out how it worked.

Regards and thanks again

 

[Fluff]

You're welcome & thanks for the feedback.