Problem Macro - HELP!!!

[TooZippy]

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"...

[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

 

[jim may]

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]

 

[TooZippy]

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.

 

[jim may]

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


​

 

[TooZippy]

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.

 

[jim may]

Tks for your response. Jim

 

[TooZippy]

Jim,

Your activate code worked. The macro is working great. Thanks for the help.

Jared Z.