VBA Code Help - Step through table Copying & Pasting

Jack_881

New Member
Joined
Sep 11, 2020
Messages
10
Office Version
  1. 2019
Platform
  1. Windows
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!

VBA Code.jpg


Cheers

Jack
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Try this.
VBA Code:
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

New Member
Joined
Sep 11, 2020
Messages
10
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Jack

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

Here's the code again with the typo fixed.
VBA Code:
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

New Member
Joined
Sep 11, 2020
Messages
10
Office Version
  1. 2019
Platform
  1. Windows
Yes, that was it. Working pefectly, many thanks you have made my day!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,477
Members
414,070
Latest member
DuncanLucas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top