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!
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]