SOLVED

Switch and replace

Copper Contributor

Hello y'all,

 

I currently have 2 tables with data, and I'm looking for a macro that can switch the two and remove a specific value.

clipboard_image_1.png

I currently have the macro:


Dim container As Double
container = Range("B3").Value
Range("B3").Value = Range("G3").Value
Range("G3").Value = container

 

to switch the two cells, but this has it's problems. I need to make one of these for every cell i switch and when one of the cells is empty, it becomes a 0. On a larger scale, this becomes a lot of work and with it being 0's it doesn't like it in other formulas. 

IF anybody knows a easier way to switch them, or even just to make all 0's in to "" that would be great.

2 Replies
To solve the issue about needing the code for each line, you should add a loop so you don't need to copy paste the code per row. Use this code:

Sub switchandreplace()

lastrow = Cells(Rows.Count, 2).End(xlUp).Row
For x = 3 To lastrow

Dim container As Double
container = Cells(x, 2).Value
Cells(x, 2).Value = Cells(x, 7).Value
Cells(x, 7).Value = container

Next x

End Sub

about the issue with the zero. What logic would you like to have implemented in your code?
best response confirmed by BramBlue (Copper Contributor)
Solution

@PascalKTeam 

Thank you for taking your time to help me.

Although I have found a solution already which is more compact. 

1 best response

Accepted Solutions
best response confirmed by BramBlue (Copper Contributor)
Solution

@PascalKTeam 

Thank you for taking your time to help me.

Although I have found a solution already which is more compact. 

View solution in original post