SOLVED
Home

Switch and replace

%3CLINGO-SUB%20id%3D%22lingo-sub-1121786%22%20slang%3D%22en-US%22%3ESwitch%20and%20replace%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1121786%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20y'all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20currently%20have%202%20tables%20with%20data%2C%20and%20I'm%20looking%20for%20a%20macro%20that%20can%20switch%20the%20two%20and%20remove%20a%20specific%20value.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F166479iC993594D4EDB96F2%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_1.png%22%20title%3D%22clipboard_image_1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20currently%20have%20the%20macro%3A%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EDim%20container%20As%20Double%3CBR%20%2F%3Econtainer%20%3D%20Range(%22B3%22).Value%3CBR%20%2F%3ERange(%22B3%22).Value%20%3D%20Range(%22G3%22).Value%3CBR%20%2F%3ERange(%22G3%22).Value%20%3D%20container%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eto%20switch%20the%20two%20cells%2C%20but%20this%20has%20it's%20problems.%20I%20need%20to%20make%20one%20of%20these%20for%20every%20cell%20i%20switch%20and%20when%20one%20of%20the%20cells%20is%20empty%2C%20it%20becomes%20a%200.%20On%20a%20larger%20scale%2C%20this%20becomes%20a%20lot%20of%20work%20and%20with%20it%20being%200's%20it%20doesn't%20like%20it%20in%20other%20formulas.%26nbsp%3B%3C%2FP%3E%3CP%3EIF%20anybody%20knows%20a%20easier%20way%20to%20switch%20them%2C%20or%20even%20just%20to%20make%20all%200's%20in%20to%20%22%22%20that%20would%20be%20great.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1121786%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1121956%22%20slang%3D%22en-US%22%3ERe%3A%20Switch%20and%20replace%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1121956%22%20slang%3D%22en-US%22%3ETo%20solve%20the%20issue%20about%20needing%20the%20code%20for%20each%20line%2C%20you%20should%20add%20a%20loop%20so%20you%20don't%20need%20to%20copy%20paste%20the%20code%20per%20row.%20Use%20this%20code%3A%3CBR%20%2F%3E%3CBR%20%2F%3ESub%20switchandreplace()%3CBR%20%2F%3E%3CBR%20%2F%3Elastrow%20%3D%20Cells(Rows.Count%2C%202).End(xlUp).Row%3CBR%20%2F%3EFor%20x%20%3D%203%20To%20lastrow%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20container%20As%20Double%3CBR%20%2F%3Econtainer%20%3D%20Cells(x%2C%202).Value%3CBR%20%2F%3ECells(x%2C%202).Value%20%3D%20Cells(x%2C%207).Value%3CBR%20%2F%3ECells(x%2C%207).Value%20%3D%20container%3CBR%20%2F%3E%3CBR%20%2F%3ENext%20x%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3CBR%20%2F%3E%3CBR%20%2F%3Eabout%20the%20issue%20with%20the%20zero.%20What%20logic%20would%20you%20like%20to%20have%20implemented%20in%20your%20code%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1124574%22%20slang%3D%22en-US%22%3ERe%3A%20Switch%20and%20replace%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1124574%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F422482%22%20target%3D%22_blank%22%3E%40PascalKTeam%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20taking%20your%20time%20to%20help%20me.%3C%2FP%3E%3CP%3EAlthough%20I%20have%20found%20a%20solution%20already%20which%20is%20more%20compact.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional 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
Highlighted
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?
Highlighted
Solution

@PascalKTeam 

Thank you for taking your time to help me.

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

Related Conversations
Tilde not working to replace asterisk?
dmharris in Excel on
3 Replies
Find and Replace with Growing Number
ryan_k in Word on
0 Replies
Using Replace to remove unspecified amount of letters
Family Lieberman in Excel on
2 Replies