VBA Code Help - Step through table Copying & Pasting

[Jack_881]

Hi All

My first post here as I have hit a wall in trying to figure this out. Please see a image of my spreadsheet below, where I want to acheive using VBA:

• Copy E10 to C2
• Copy B10 to C3
• Spreadsheet then works out the string that are in C5 & C6 from doing the above
• Copy C5 to C10
• Copy C6 to D10
• Move to the next row in the table
• Copy E11 to C2
• Copy B11 to C3
• Spreadsheet then works out the string that are in C5 & C6 from doing the above
• Copy C5 to C11
• Copy C6 to D11
• Perform this loop until the end of the table is reached

Hopefully I have explained the well enough to have a guru assist!

Cheers

Jack

 

[Norie]

Try this.

Sub Test()
Dim rngSrc As Range

     Set rngSrc = Range("E10")

     Do
         Range("C2").Value = rngSrc.Value
         Range("C3").Value = rngSrc.Offset(, -3).Value
         Application.Calculate
         rngSrc.Offset(, -2).Value = Range("C5").Value
         rngSrc.Offset(, -1).Value = Range("C6").Value
         Set rngSrc = rngDst.Offset(1)
     Loop Until rngSrc.Value = ""

End Sub

 

[Jack_881]

Thank you. I am getting an Object Required error on this line:

Loop Until rngSrc.Value = ""

The code up until this point is doing what I want to achieve. Do you know how I can resolve that error?

Cheers

Jack

 

[Norie]

Jack

There is a typo in the code but not on that line.

Here's the code again with the typo fixed.

Sub Test()
Dim rngSrc As Range

     Set rngSrc = Range("E10")

     Do
         Range("C2").Value = rngSrc.Value
         Range("C3").Value = rngSrc.Offset(, -3).Value
         Application.Calculate
         rngSrc.Offset(, -2).Value = Range("C5").Value
         rngSrc.Offset(, -1).Value = Range("C6").Value
         Set rngSrc = rngSrc.Offset(1)
     Loop Until rngSrc.Value = ""

End Sub

 

[Jack_881]

Yes, that was it. Working pefectly, many thanks you have made my day!