Home > Enterprise >  How to do multiple swapping in Excel VBA
How to do multiple swapping in Excel VBA

Time:06-07

How can I swap the specific cells in the "Complete Solution of Neighbor" row based on the "Three Selected Item to Swap"? Since the value stated in the three selected item to swap is random generate, so I can't put specific value in the code. For example, the 3 values 13, 10 and 15 need to swap, the result can be 5-14-13-2-4-8-12-15-9-10-6-3-7-1-11-5. enter image description here

CodePudding user response:

Please, try using the next code. But your picture is not clear in terms of rows and columns header... So, please adapt the ranges I built to demonstrate how it works:

Sub SwappingNumbs()
  Dim sh As Worksheet, rngS As Range, rng As Range
  Dim mtch1, mtch2, mtch3
  
  Set sh = ActiveSheet
  Set rngS = sh.Range("B3:D3") 'the range containing numbers used to swap
  Set rng = sh.Range("B6:Q6")  'the range containing numbers to be swapped
  
  mtch1 = Application.match(rngS(1, 1), rng, 0) 'it returns position in rng Range
  mtch2 = Application.match(rngS(1, 2), rng, 0)
  mtch3 = Application.match(rngS(1, 3), rng, 0)
  
  rng(1, mtch1) = rngS(1, 2) 'swapping the numbers...
  rng(1, mtch2) = rngS(1, 3)
  rng(1, mtch3) = rngS(1, 1)
End Sub

You should show us what you tried by your own, even if your code/solution does not work as you want...

I make now an exception, but I would like you to learn that a question must prove your effort to solve it by your own.

  • Related