0

I'm very new to VBA on Excel and I have a problem that's stumped me for a while now. In my Excel sheet (in the same row), I have Columns B and F where: if B is a higher value than F then insert a row beneath the current row and cut/paste the three columns of that row into the newly created row (see image link: https://i.stack.imgur.com/cRZbY.png) so B11:D11 would become B12:D12 and above those cells would be three blank cells. I have tried the below code for this but I keep getting an error 1004: PasteSpecial method of Range class failed. I suspect it has something to do with the PasteSpecial but it has worked for me previously.

Sub SubA()
Range("B11").Activate
If Range("B11").Value > Range("F11").Value Then ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown
Range(Selection, Selection.End(xlToRight)).Cut
ActiveCell.Offset(1).PasteSpecial Paste:=xlPasteValues

End Sub

If I can get this fixed for the first row, I would like it to do the same process for all rows in the spreadsheet, however I am not sure on how to start this either. Any help would be much appreciated! Thanks.

EDIT: The end result should look like this: https://i.stack.imgur.com/SXqg9.png There was a few more bits of code I was going to do after the above loop was sorted to get it to look like the above. Essentially, any unique weld numbers should have their own row, and any matching weld numbers should be in the same row (in ascending order).

dooooncan
  • 1
  • 1
  • 1
    You can't use `Cut` and `PasteSpecial` together (try it manually, it doesn't work). – BigBen Aug 06 '20 at 16:26
  • What can I use instead? It's just the values I need – dooooncan Aug 06 '20 at 16:29
  • 1
    You can copy and then clear the original cells. Instead of using the clipboard (i.e. copy and pastespecial), you can use value transfer, see [this question](https://stackoverflow.com/questions/51528000/vba-paste-as-values-how-to) for an example. – BigBen Aug 06 '20 at 16:30
  • Thanks for this, however I don't see where value transfer is used on the example? – dooooncan Aug 06 '20 at 16:47
  • Value transfer looks like `Range(somerange).Value` = `Range(otherrange).Value`. – BigBen Aug 06 '20 at 16:48
  • Okay, how would I add this into my code with the intention of automating this over every row? Unless inputting `Range("B11:F11").Value = Range("B12:F12").Value` would be correct? – dooooncan Aug 06 '20 at 17:01
  • You'd need a loop. Since you're inserting rows as you're looping, you'd need to loop from the bottom to the top. – BigBen Aug 06 '20 at 17:03
  • I'm trying to understand your expected output... you want to repeat rows from the region in B10, only until `WeldNum` is lower than `Fcolumn`? for example you will end up with the first row repeated 3 times, and 4-5th rows will be `220, 1:15, 27` & `...48`? Because inserting a full row will repeat to infinity and beyond – RichieV Aug 06 '20 at 20:47
  • perhaps it can help if you include a table of how you want it to look like – RichieV Aug 06 '20 at 20:49
  • Hi Richie, I've added what the table should look like in the OP. Thanks in advance – dooooncan Aug 06 '20 at 21:45

0 Answers0