Macro Runs Differently When Placed Within Another Macro

M

mharper90

Board Regular
Joined
May 28, 2013
Messages
117
Office Version
  1. 365
Platform
  1. MacOS
The Macro below, cpypste5, runs perfectly when run by itself. But when it is placed inside of another Macro, "CommandButton3_Click", to act as a part of a larger evolution, it doesn't operate the same. Specifically, the part where it is supposed to insert a formula in C3/D3/E3/F3 and fill down to the last used row ( & Lr), no longer stops at the last row, but continues down the page to almost the 100th row (it should have filled down less than 20 rows), thus displaying #N/A since these are VLOOKUP functions with no reference past the last used row.

I've included all of the code for these 2 macros. Any minor typo might just be from me typing this in by hand from a printout, because this file is stuck on a secure computer with no real internet access. Hopefully I typed it right, and it'll be obvious to someone why it just won't cooperate. Like I said, cpypste5 works perfectly when run by itself.

Lastly, I'm very new to VBA, and always looking for ways to improve and clean up my code, so please call something out if you notice a way to make it better! Thanks!

Code: 
[COLOR=#000000][FONT='inherit']Sub cpypste5()[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    Dim x         As String[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    Dim ws1    As Worksheet: Set ws1 = ThisWorkbook.Sheets(“Main Data”)[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    Dim ws2    As Worksheet: Set ws2 = ThisWorkbook.Sheets(“Figure 2-2”)[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    Dim r1 As Range, r2 As Range, multiAreaRange As Range[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    Dim cell    As Range[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    Dim Lr        As Long[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    Set r1 = ws1.Range(“B:B”)[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    Set r2 = ws1.Range(“C:C”)[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    Set multiAreaRange = Union(r1, r2)[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']Application.ScreenUpdating = False[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    x = “NO”[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    ws2.Rows(“3:” & Rows.Count).Delete[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    If Not IsError(Application.Match(x, ws1.Range(“A:A”), 0)) Then[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']        ws1.Rnage(“A3”).CurrentRegion.AutoFilter Field:=1, Criterial:=x[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']        Intersect(ws1.AutoFilter.Range.Offset(1), multiAreaRange).Copy _[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']            Destination:=ws2.Range(“A” & Rows.Count).End(xlUp).Offset(1)[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']        ws1.AutoFilterMode = False[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    End If[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']SortGroup2Printout[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    ws2.Range(“A:F”).Interior.ColorIndex = xlNone[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    Lr = Range(“A” & Rows.Count).End(xlUp).Row[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    With ws2.Range(“C3:C” & Lr)[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    .Cells.Formula = “=TEXT(VLOOKUP(A3, ‘Main Data’!B:N, 4, FALSE), “”m/dd/yyyy””)”[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    .Cells.FillDown[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    End With[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    With ws2.Range(“D3:D” & Lr)[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    .Cells.Formula = “=IF((VLOOKUP(A3, ‘Main Data’!B:N, 5 FALSE))<=TODAY(), TEXT(VLOOKUP(A3, ‘Main Data’!B:N, 5 FALSE), “”m/dd/yyyy””), TEXT(“”””,))”[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    .Cells.FillDown[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    End With[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    With ws2.Range(“E3:E” & Lr)[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    .Cells.Formula = “=IF(AND(“Main Data’!$C$4=“”1””, (VLOOKUP(A3, ‘Main Data’!B:N,8,FALSE))<>””””), (VLOOKUP(A3, ‘Main Data’!B:N,7,FALSE)-VLOOKUP(A3, ‘Main Data’!B:N,8,FALSE)), IF(AND(“Main Data’!$C$4=“”2””, (VLOOKUP(A3, ‘Main Data’!B:N,9,FALSE))<>””””), (VLOOKUP(A3, ‘Main Data’!B:N,7,FALSE)-VLOOKUP(A3, ‘Main Data’!B:N,8,FALSE)-VLOOKUP(A3, ‘Main Data’!B:N,9,FALSE)), IF(AND(“Main Data’!$C$4=“”3””, (VLOOKUP(A3, ‘Main Data’!B:N,10,FALSE))<>””””), (VLOOKUP(A3, ‘Main Data’!B:N,7,FALSE)-VLOOKUP(A3, ‘Main Data’!B:N,8,FALSE)-VLOOKUP(A3, ‘Main Data’!B:N,9,FALSE)-VLOOKUP(A3, ‘Main Data’!B:N,10,FALSE)), IF(AND(“Main Data’!$C$4=“”4””, (VLOOKUP(A3, ‘Main Data’!B:N,11,FALSE))<>””””), (VLOOKUP(A3, ‘Main Data’!B:N,7,FALSE)-VLOOKUP(A3, ‘Main Data’!B:N,8,FALSE)-VLOOKUP(A3, ‘Main Data’!B:N,9,FALSE)-VLOOKUP(A3, ‘Main Data’!B:N,10,FALSE)-VLOOKUP(A3, ‘Main Data’!B:N,11,FALSE)), VLOOKUP(A3, ‘Main Data’!B:N, 13, FALSE)))))”[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    .Cells.FillDown[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    End With[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    With ws2.Range(“F3:F” & Lr)[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    .Cells.Formula = “=IF(AND(‘Main Data’!$C$4=“”1””, (VLOOKUP(A3, ‘Main Data’!B:N, 8, FALSE))<>””””), (VLOOKUP(A3, ‘Main Data’!B:N, 8, FALSE)), =IF(AND(‘Main Data’!$C$4=“”2””, (VLOOKUP(A3, ‘Main Data’!B:N, 9, FALSE))<>””””), (VLOOKUP(A3, ‘Main Data’!B:N, 9, FALSE)), =IF(AND(‘Main Data’!$C$4=“”3””, (VLOOKUP(A3, ‘Main Data’!B:N, 10, FALSE))<>””””), (VLOOKUP(A3, ‘Main Data’!B:N, 10, FALSE)), =IF(AND(‘Main Data’!$C$4=“”4””, (VLOOKUP(A3, ‘Main Data’!B:N, 11, FALSE))<>””””), (VLOOKUP(A3, ‘Main Data’!B:N, 11, FALSE)), “”””))))”[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    .Cells.FillDown[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    End With[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    With ws2[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']        With Application.ErrorCheckingOptions[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']            .BackgroundChecking = False[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']            .EvaluateToError = False[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']            .InconsistentFormula = False[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']        End With[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    End With[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    [/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    With ws2.Range(“A3:F” & Lr)[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']        .Cells.BorderAround _[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']        ColorIndex:=1, Weight:=xlMedium[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    EndWith[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    With ws2.Range(“A” & Rows.Count).End(xlUp).Offset(5, 1)[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']        .Value = “Closeout Review: _____________   Date: __________”[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']            With ws2.Range(“A” & Rows.Count).End(xlUp).Offset(6, 1)[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']                .Value = “                              CRA”[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']            End With[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']        .Resize(3, 4).Offset(-1, 0) .BorderAround _[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']        ColorIndex:=1, Weight:=xlMedium[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    End With[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']Application.ScreenUpdating = True[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']End Sub[/FONT][/COLOR]

Code: 
[COLOR=#000000][FONT='inherit']Private Sub CommandButton3_Click()[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']Dim newRow As Long[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']Dim ws As Worksheet[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']Application.ScreenUpdating = False[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    If Me.ComboBox1.Value = “” Or Me.TextBox2.Text = “” Or Me.TextBox3.Text = “” Or Me.TextBox4.Text = “” Or Me.TextBox5.Text = “” Or Me.TextBox6.Text = “” Then[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']        MsgBox “ All fields must be completed. “, vbCritical, “Entry Error”[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']        Exit Sub[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    End If[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    Set ws = Worksheets(“Main Data”)[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    newRow = Application.WorksheetFunction.CountA(ws.Range(“A:A”)) + 2[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']        ws.Cells(newRow, 1).Value = Me.ComboBox1.Value[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']        ws.Cells(newRow, 2).Value = Me.TextBox2.Value[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']        ws.Cells(newRow, 3).Value = Me.TextBox3.Value[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']        ws.Cells(newRow, 4).Value = Me.TextBox4.Value[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']        ws.Cells(newRow, 8).Value = Me.TextBox5.Value[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']        ws.Cells(newRow, 9).Value = Me.TextBox6.Value[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']        If ws.Cells(newRow, 4).Value = Range(“$D$4”) Then[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']            ws.Cells(newRow, 4).Value = “”[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']        End If[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']        If ws.Cells(newRow, 9).Value = 0 Then[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']            ws.Cells(newRow, 9).Value = “”[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']        End If[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']sortColB[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    With Me.ComboBox1[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']        If .Value = “YES” Or .Value = “MDR” Then[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']        Dim ans As String[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']            ans = Me.TextBox2.Value[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']            Sheets(“Master Blank for ERC”).Range(“A3”).Value = Me.TextBox2.Value[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']                If Me.ComboBox1.Value = “MDR” Then[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']                    Sheets(“Master Blank for ERC”).Range(“L5”).Value = “entry authorized*”[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']                    Sheets(“Master Blank for ERC”).Range(“A60”).Value = “* Entry Authorized for Emergencies Only”[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']                End If[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']            Sheets(“Master Blank for ERC”).Copy After:=Sheets(Sheets.Count)[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']            ActiveSheet.Name = ans[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']            Sheets(“Master Blank for ERC”).Range(“A3”).Value = “$NAME$”[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']            Sheets(“Master Blank for ERC”).Range(“L5”).Value = “entry authorized”[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']            Sheets(“Master Blank for ERC”).Range(“A60”).Value = “”[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']SortSheetsTabName[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']fillFormula3[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']            Else[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']        End If[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    End With[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    Me.ComboBox1.Value = “”[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    Me.TextBox2.Value = “”[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    Me.TextBox3.Value = “”[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    Me.TextBox4.Value = “”[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    Me.TextBox5.Value = “”[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    Me.TextBox6.Value = “”[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    Me.ComboBox1.SetFocus[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit'][COLOR=#ff0000]cpypste5[/COLOR][/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']fillFormula3[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    Me.Hide[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    Sheets(“Main Data”).Activate[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    Sheets(“Main Data”).Select[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    Sheets(“Main Data”).Range(“A1”).Select[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']Application.ScreenUpdating = True[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']End Sub[/FONT][/COLOR]
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
a couple of things
1. I'd force lr to reference the sheet as well, for example

Code: 
 Lr = [color=red]ws1.[/color]Range(“A” & Rows.Count).End(xlUp).Row

Also, these blocks of code

Code: 
With ws2.Range(“C3:C” & Lr)
    .Cells.Formula = “=TEXT(VLOOKUP(A3, ‘Main Data’!B:N, 4, FALSE), “”m/dd/yyyy””)”
    .Cells.FillDown
    End With

could be reduced to

Code: 
ws2.Range(“C3:C” & Lr).Formula = “=TEXT(VLOOKUP(A3, ‘Main Data’!B:N, 4, FALSE), “”m/dd/yyyy””)”

try not to reference entire columns either. The above formula could be varied to read

Code: 
ws2.Range(“C3:C” & Lr).Formula = “=TEXT(VLOOKUP(A3, ‘Main Data’!B" & lr & ":N" & lr & ", 4, FALSE), “”m/dd/yyyy””)”
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Code to Add Data From UserForm In Row Above or Below Based on Criteria
Replies
1
Views
197
amoverton2
A
S
  • Solved
Tweak Macro that Imports specific table columns from a different workbook to filter a column first
Replies
4
Views
155
sspatriots
S
K
  • Question
VBA : Copy only new information from specific cells in another worksheet
Replies
6
Views
611
kevin9999
K
I
  • Question
Current Code taking 10 Hours to run. Need Help to Optimize
Replies
5
Views
228
ItalianPlatinum
I
E
  • Solved
Macro stops working before end sub with no prompt
2 3
Replies
21
Views
1K
_eNVy_
E

Forum statistics

Threads
1,214,523
Messages
6,120,039
Members
448,940
Latest member
mdusw

Share this page

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