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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,471
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

Board Regular
Joined
Dec 30, 2018
Messages
70
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

Well-known Member
Joined
Jul 4, 2004
Messages
7,471
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

Board Regular
Joined
Dec 30, 2018
Messages
70

ADVERTISEMENT

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.
 

TooZippy

Board Regular
Joined
Dec 30, 2018
Messages
70
Jim,

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

Jared Z.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,626
Messages
5,523,982
Members
409,550
Latest member
baaabies

This Week's Hot Topics

Top