Problem Macro - HELP!!!

TooZippy

Board Regular
Joined
Dec 30, 2018
Messages
70
This macro was working but when I put the raw data in the spreadsheet to test it out in worksheet "A", it inserted columns "F" and "G" in worksheet "B" instead. That is all it did. It didn't insert the formulas into columns "F" and "G". The debug box came up and when I went to debug, the part of the macro that highlighted yellow was at the bottom to insert the header name "ABC" in column "F"...

Code:
[LEFT][COLOR=#26282A][FONT=Helvetica Neue]Sub MergeColumns()[/FONT][/COLOR]

[COLOR=#26282A][FONT=Helvetica Neue]Columns("F:G").Insert[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]Worksheets("Paste Vendor Data").Range("F2:F1501").Formula = "=IF(LEFT(E2,1)=""-"",MID(E2,2,LEN(E2)),E2)"[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]Worksheets("Paste Vendor Data").Range("G2:G1501").Formula = "=IF(AND(F2>0,R2>0,S2>0),CONCATENATE(R2,""-"",S2),if(AND(R2>0,S2=0),R2,IF(AND(F2>0,S2=0),F2,IF(AND(F2=0,S2>0),IF(R2>0,CONCATENATE(R2,""-"",S2),S2),""""))))"[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]    [/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]    Columns("F:F").Select[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]    Selection.Copy[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]        :=False, Transpose:=False[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]        [/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]Application.ScreenUpdating = False[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]    Dim cell As Range[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]    ActiveSheet.Cells.Replace what:=Chr(160), Replacement:=Chr(32), _[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]    On Error Resume Next   'in case no text cells in selection[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]    For Each cell In ActiveSheet.Cells.SpecialCells(xlConstants, xlTextValues)[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]        cell.Value = Application.Trim(cell.Value)[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]    Next cell[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]    On Error GoTo 0[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]    Application.ScreenUpdating = True[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]    [/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]    Columns("G:G").Select[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]    Application.CutCopyMode = False[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]    Selection.Copy[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]        :=False, Transpose:=False[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]        [/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]    Range("A1").End(xlToRight).Offset(0, 1).Value = "ABC"[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]        [/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]    MsgBox ("Done!")[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]        [/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]End Sub[/FONT][/COLOR][/LEFT]

I was thinking that it might have something to do with the """" at the end of the second formula. I am trying to enter a blank space if the condition is false. Could this be the problem?

Thank you,

Jared Z
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Your Macro [FONT=&quot]MergeColumns() is not designed to be Run UNTIL you have made a specific Worksheet the ACTIVE sheet. Make that Worksheet the Active worksheet and run [/FONT][FONT=&quot]MergeColumns()[/FONT] again.[FONT=&quot]
[/FONT]

[FONT=&quot]
[/FONT]
 
Upvote 0
Jim,

I have the data that I want to manipulate in worksheet "A". I am pasting the formula's to accommodate 1500 rows of data. I have all of the buttons (Form Control)
that I assign the macros to
in worksheet "B". All of the other buttons that activate other macros manipulate the data in worksheet "A" while they sit in worksheet "B" like this one. Should I use "Worksheets(WorksheetA).Columns("F:G").Insert" in this code at the start of the macro for it to work?

Thank you,

Jared Z.
 
Upvote 0
If your macro MergeColumns() is DIRECTED TO Manipulation of data on your worksheetA and if your Forms Button which is on worksheetB -- I'd say the FIRST NEW LINE OF CODE you should enter BEFORE your existing line: "Columns("F:G").Insert" should be:

Worksheets("WorksheetA").Activate 'WorksheetA should be the exact visible name you have assigned to your worksheetA

Doing so - Excel knows to begin the ALL SUBSEQUENT CODE-LINES from INSIDE WorksheetA, and to continue manipulation of All Code to Objects within WorksheetA SOLELY -- Unless directed otherwise by subsequent code.

If you can make this change - and then Split your screen so that you can see both your WorksheetA (On left-hand Side) and your MergeColumns Code window (On the right-hand Side) - then Click on the first line of Code ("MergeColumns()) and Press the F8 Function Key. As you Press the F8 Key a single line will be highlighted in Yellow -- meaning "This line has not yet been
comitted, but when you press F8 again it will and then jump to the Next line below it continually until it gets to the "END SUB" code-line.
As you press it (the F8 Key) pay attention to your left-hand screen to see what each single code line is doing.

I find this technique (resource) to be invaluable -- as it has solved 99% of all coding problem I've ever had..

Good luck. Write back if necessary.

Jim


 
Upvote 0
Thank you Jim. That was very helpful. I won't be able to try it until Tuesday. I will let you know how it goes.

Jared Z.
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

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
Back
Top