Return Value at end of Macro

D

Desu Nota from Columbus

Well-known Member
Joined
Mar 17, 2011
Messages
556
In the macro posted below, I have two values I input in A1 and A2.

These values are used and then cleared. Is there a way to have these values returned at the very end of the macro into Results!D2 and Results!D3?

Code: 
Sub ATest()
Dim i As Integer
Dim lrow As Long
Dim irow As Long
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim startDate As Date
Dim endDate As Date

Server_Name = "GGVORNE\SQLEXPRESS" ' Enter your server name here
Database_Name = "VorneDB" ' Enter your database name here
User_ID = "vorne" ' enter your user ID here
Password = "VData!!!" ' Enter your password here
startDate = Range("A1").Value
endDate = Range("A2").Value
SQLStr = ""
SQLStr = SQLStr & " SELECT * FROM [VorneDB].[dbo].[interval_stream] "
SQLStr = SQLStr & " WHERE [start_time] >= '" & Format(startDate, "yyyy-mm-dd hh:nn:ss.000") & "'"
SQLStr = SQLStr & " AND [end_time] < '" & Format(endDate, "yyyy-mm-dd hh:nn:ss.000") & "'"
MsgBox SQLStr
Debug.Print SQLStr
Application.ScreenUpdating = False

Set Cn = New ADODB.Connection
Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"

rs.Open SQLStr, Cn, adOpenStatic
' Dump to spreadsheet
With Worksheets("sheet1").Range("a1:z50000") ' Enter your sheet name and range here
    .ClearContents
    .CopyFromRecordset rs
End With
'            Tidy up
rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing

With Sheets("Sheet1")
    Columns("A:BB").Select
    Selection.NumberFormat = "General"
    Range("A:A,J:J,AS:AS").Select
    Selection.NumberFormat = "m/d/yyyy h:mm"
End With

    
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Cells.AutoFilter
    Selection.AutoFilter Field:=54, Criteria1:="job"
    Columns("A:A").Select
    Range("A" & Rows.Count).End(xlUp).Select
    Range(Selection, Selection.End(xlUp)).Offset(1, 0).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Delete Shift:=xlUp
    Selection.AutoFilter Field:=54, Criteria1:="stub_job"
    Columns("A:A").Select
    Range("A" & Rows.Count).End(xlUp).Select
    Range(Selection, Selection.End(xlUp)).Offset(1, 0).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Delete Shift:=xlUp
    Selection.AutoFilter Field:=54, Criteria1:="stub_shift"
    Columns("A:A").Select
    Range("A" & Rows.Count).End(xlUp).Select
    Range(Selection, Selection.End(xlUp)).Offset(1, 0).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Delete Shift:=xlUp
    Selection.AutoFilter Field:=54, Criteria1:="shift"
    Selection.AutoFilter
    Range("A:A,C:C,E:F,H:I,K:T,W:W,AF:AG,AJ:AQ,AT:AT,AU:AW,AX:AY").Delete
    Range("A1").Value = "DeviceKey"
    Range("B1").Value = "Availability"
    Range("C1").Value = "Down Time"
    Range("D1").Value = "End Time"
    Range("E1").Value = "Shift"
    Range("F1").Value = "OEE"
    Range("G1").Value = "Percent Down"
    Range("H1").Value = "Percent Run"
    Range("I1").Value = "Percent Setup"
    Range("J1").Value = "Percent Standby"
    Range("K1").Value = "Performance"
    Range("L1").Value = "Quality"
    Range("M1").Value = "Reject Count"
    Range("N1").Value = "Run Time"
    Range("O1").Value = "Sequence_Number"
    Range("P1").Value = "Setup Time"
    Range("Q1").Value = "Standby Time"
    Range("R1").Value = "Start Time"
    Range("S1").Value = "Total Count"
    Range("T1").Value = "Total Time"
    Range("U1").Value = "Type"
    Columns("D:D").Select
    Selection.Cut
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    Columns("R:R").Select
    Selection.Cut
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    Columns("G:G").Select
    Selection.Cut
    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight
    Columns("L:L").Select
    Selection.Cut
    Columns("F:F").Select
    Selection.Insert Shift:=xlToRight
    Columns("M:M").Select
    Selection.Cut
    Columns("G:G").Select
    Selection.Insert Shift:=xlToRight
    Columns("O:O").Select
    Selection.Cut
    Columns("H:H").Select
    Selection.Insert Shift:=xlToRight
    Columns("J:J").Select
    Selection.Cut
    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight
    Columns("L:L").Select
    Selection.Cut
    Columns("K:K").Select
    Selection.Insert Shift:=xlToRight
    Columns("N:N").Select
    Selection.Cut
    Columns("M:M").Select
    Selection.Insert Shift:=xlToRight
    Columns("R:R").Select
    Selection.Cut
    Columns("K:K").Select
    Selection.Insert Shift:=xlToRight
    Columns("R:R").Select
    Selection.Cut
    Columns("L:L").Select
    Selection.Insert Shift:=xlToRight
    Columns("R:R").Select
    Selection.Cut
    Columns("Q:Q").Select
    Selection.Insert Shift:=xlToRight
    Columns("T:T").Select
    Selection.Cut
    Columns("Q:Q").Select
    Selection.Insert Shift:=xlToRight
    Range("S:U").Delete
    Range("A1").Select
    Sheets.Add
    Sheets.Add
    Sheets.Add
    Sheets.Add
    Sheets.Add
    Sheets.Add
    Sheets.Add
    Sheets.Add
    Sheets.Add
    Sheets.Add
    Sheets.Add
    Sheets.Add
    Sheets("Sheet1").Name = "All Assets"
    Sheets("Sheet4").Name = "Line1"
    Sheets("Sheet5").Name = "Line2"
    Sheets("Sheet6").Name = "Line3"
    Sheets("Sheet7").Name = "Line4"
    Sheets("Sheet8").Name = "Line5"
    Sheets("Sheet9").Name = "Line6"
    Sheets("Sheet10").Name = "Line10"
    Sheets("Sheet11").Name = "All Production"
    Sheets("Sheet12").Name = "Flowline"
    Sheets("Sheet13").Name = "Lamination"
    Sheets("Sheet14").Name = "Grinding"
    Sheets("Sheet15").Name = "Results"
    Application.DisplayAlerts = False
    Sheets("Sheet2").Delete
    Sheets("Sheet3").Delete
    Application.DisplayAlerts = True
    
    For i = 1 To 6
    With Sheets("All Assets").Cells
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="Line" & i
        .SpecialCells(xlCellTypeVisible).Copy
        Sheets("Line" & i).Paste
        .AutoFilter
    End With
Next i
With Sheets("All Assets").Cells
    .AutoFilter
    .AutoFilter Field:=1, Criteria1:="Line10"
    .SpecialCells(xlCellTypeVisible).Copy
    Sheets("Line10").Paste
    .AutoFilter Field:=1, Criteria1:="Flowline"
    .SpecialCells(xlCellTypeVisible).Copy
    Sheets("Flowline").Paste
    .AutoFilter Field:=1, Criteria1:="Lamination"
    .SpecialCells(xlCellTypeVisible).Copy
    Sheets("Lamination").Paste
    .AutoFilter Field:=1, Criteria1:="Grinding"
    .SpecialCells(xlCellTypeVisible).Copy
    Sheets("Grinding").Paste
    .AutoFilter Field:=1, Criteria1:="=*Line*"
    .SpecialCells(xlCellTypeVisible).Copy
    Sheets("All Production").Paste
    .AutoFilter
End With
    
For i = 1 To 6
    Sheets("Line" & i).Select
        Range("S1").Value = "Total Time"
        Range("S2:S65534").Select
        Selection.FormulaR1C1 = "=If(RC[-10]="""","""",(RC[-10]+RC[-9])/3600)"
        Range("T1").FormulaR1C1 = "=Sum(R2C[-1]:R65534C[-1])"
        Range("U1").Value = "Weighted OEE Value"
        Range("U2:U65534").FormulaR1C1 = "=IF(RC[-2]="""","""",RC[-16]*RC[-2]/R1C20)"
        Range("V1").Value = "OEE Weighted"
        Range("V2").FormulaR1C1 = "=IF(R[-1]C[-2]=0,""Not Run"",SUM(RC[-1]:R[65532]C[-1]))"
        Range("V2").Select
        Selection.NumberFormat = "0%"
        Range("S:U").Select
        Selection.NumberFormat = "General"
        Range("V1:V2").Select
            With Selection.Font
                .Name = "Arial"
                .FontStyle = "Bold"
                .Size = 22
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ColorIndex = 2
            End With
            With Selection.Interior
                .ColorIndex = 5
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
            End With
        Cells.Select
        Selection.Columns.AutoFit
        Columns("S:U").EntireColumn.Hidden = True
        ActiveWindow.ScrollColumn = 13
Next i
    Sheets("Line10").Select
        Range("S1").Value = "Total Time"
        Range("S2:S65534").Select
        Selection.FormulaR1C1 = "=If(RC[-10]="""","""",(RC[-10]+RC[-9])/3600)"
        Range("T1").FormulaR1C1 = "=Sum(R2C[-1]:R65534C[-1])"
        Range("U1").Value = "Weighted OEE Value"
        Range("U2:U65534").FormulaR1C1 = "=IF(RC[-2]="""","""",RC[-16]*RC[-2]/R1C20)"
        Range("V1").Value = "OEE Weighted"
        Range("V2").FormulaR1C1 = "=IF(R[-1]C[-2]=0,""Not Run"",SUM(RC[-1]:R[65532]C[-1]))"
        Range("V2").Select
        Selection.NumberFormat = "0%"
        Range("S:U").Select
        Selection.NumberFormat = "General"
        Range("V1:V2").Select
            With Selection.Font
                .Name = "Arial"
                .FontStyle = "Bold"
                .Size = 22
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ColorIndex = 2
            End With
            With Selection.Interior
                .ColorIndex = 5
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
            End With
        Cells.Select
        Selection.Columns.AutoFit
        Columns("S:U").EntireColumn.Hidden = True
        ActiveWindow.ScrollColumn = 13
    Sheets("All Production").Select
        Range("S1").Value = "Total Time"
        Range("S2:S65534").Select
        Selection.FormulaR1C1 = "=If(RC[-10]="""","""",(RC[-10]+RC[-9])/3600)"
        Range("T1").FormulaR1C1 = "=Sum(R2C[-1]:R65534C[-1])"
        Range("U1").Value = "Weighted OEE Value"
        Range("U2:U65534").FormulaR1C1 = "=IF(RC[-2]="""","""",RC[-16]*RC[-2]/R1C20)"
        Range("V1").Value = "OEE Weighted"
        Range("V2").FormulaR1C1 = "=IF(R[-1]C[-2]=0,""Not Run"",SUM(RC[-1]:R[65532]C[-1]))"
        Range("V2").Select
        Selection.NumberFormat = "0%"
        Range("S:U").Select
        Selection.NumberFormat = "General"
        Range("V1:V2").Select
            With Selection.Font
                .Name = "Arial"
                .FontStyle = "Bold"
                .Size = 22
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ColorIndex = 2
            End With
            With Selection.Interior
                .ColorIndex = 5
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
            End With
        Cells.Select
        Selection.Columns.AutoFit
        Columns("S:U").EntireColumn.Hidden = True
        ActiveWindow.ScrollColumn = 13
    With Sheets("Flowline")
        lrow = .Range("E" & .Rows.Count).End(xlUp).Row
        For irow = lrow To 1 Step -1
            Select Case .Cells(irow, "E").Value
                Case Is = 0
                    .Rows(irow).Delete
            End Select
        Next irow
    End With
    With Sheets("Flowline").Select
        Range("S1").Value = "Total Time"
        Range("S2:S65534").Select
        Selection.FormulaR1C1 = "=If(RC[-10]="""","""",(RC[-10]+RC[-9])/3600)"
        Range("T1").FormulaR1C1 = "=Sum(R2C[-1]:R65534C[-1])"
        Range("U1").Value = "Weighted OEE Value"
        Range("U2:U65534").FormulaR1C1 = "=IF(RC[-2]="""","""",RC[-16]*RC[-2]/R1C20)"
        Range("V1").Value = "OEE Weighted"
        Range("V2").FormulaR1C1 = "=IF(R[-1]C[-2]=0,""Not Run"",SUM(RC[-1]:R[65532]C[-1]))"
        Range("V2").Select
        Selection.NumberFormat = "0%"
        Range("S:U").Select
        Selection.NumberFormat = "General"
        Range("V1:V2").Select
            With Selection.Font
                .Name = "Arial"
                .FontStyle = "Bold"
                .Size = 22
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ColorIndex = 2
            End With
            With Selection.Interior
                .ColorIndex = 5
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
            End With
        Cells.Select
        Selection.Columns.AutoFit
        Columns("S:U").EntireColumn.Hidden = True
        ActiveWindow.ScrollColumn = 13
    End With
    With Sheets("Lamination").Select
        Range("S1").Value = "Total Time"
        Range("S2:S65534").Select
        Selection.FormulaR1C1 = "=If(RC[-10]="""","""",(RC[-10]+RC[-9])/3600)"
        Range("T1").FormulaR1C1 = "=Sum(R2C[-1]:R65534C[-1])"
        Range("U1").Value = "Weighted OEE Value"
        Range("U2:U65534").FormulaR1C1 = "=IF(RC[-2]="""","""",RC[-16]*RC[-2]/R1C20)"
        Range("V1").Value = "OEE Weighted"
        Range("V2").FormulaR1C1 = "=IF(R[-1]C[-2]=0,""Not Run"",SUM(RC[-1]:R[65532]C[-1]))"
        Range("V2").Select
        Selection.NumberFormat = "0%"
        Range("S:U").Select
        Selection.NumberFormat = "General"
        Range("V1:V2").Select
            With Selection.Font
                .Name = "Arial"
                .FontStyle = "Bold"
                .Size = 22
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ColorIndex = 2
            End With
            With Selection.Interior
                .ColorIndex = 5
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
            End With
        Cells.Select
        Selection.Columns.AutoFit
        Columns("S:U").EntireColumn.Hidden = True
        ActiveWindow.ScrollColumn = 13
    End With
    With Sheets("Results").Select
    For i = 1 To 6
        Range("A" & i + 1).Value = "Line" & i
    Next i
    End With
    With Sheets("Results").Select
        Range("A8").Value = "Line10"
        Range("A9").Value = "All Prod"
        Range("A10").Value = "Flowline"
        Range("A11").Value = "Lamination"
        Range("A1").Value = "Asset"
        Range("B1").Value = "OEE"
        Range("B2").FormulaR1C1 = "=Line1!RC[20]"
        Range("B3").FormulaR1C1 = "=Line2!R[-1]C[20]"
        Range("B4").FormulaR1C1 = "=Line3!R[-2]C[20]"
        Range("B5").FormulaR1C1 = "=Line4!R[-3]C[20]"
        Range("B6").FormulaR1C1 = "=Line5!R[-4]C[20]"
        Range("B7").FormulaR1C1 = "=Line6!R[-5]C[20]"
        Range("B8").FormulaR1C1 = "=Line10!R[-6]C[20]"
        Range("B9").FormulaR1C1 = "='All Production'!R[-7]C[20]"
        Range("B10").FormulaR1C1 = "=Flowline!R[-8]C[20]"
        Range("B11").FormulaR1C1 = "=Lamination!R[-9]C[20]"
        Range("A1:B1").Select
            With Selection.Font
                .FontStyle = "Bold"
            End With
        Range("D1").Value = "Date Range For Data"
        Range("E2").Value = "<-- Start Time"
        Range("E3").Value = "<-- End Time"
        Cells.Select
        Selection.Columns.AutoFit
    End With
End Sub
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
Since the variables in which you store them in don't get changed then all you have to do is output them at the end of your code.

Code: 
    startDate = Range("A1").Value
    endDate = Range("A2").Value
'...
'...code
'...
    Worksheets("Results").Range("D2").Value = startDate
    Worksheets("Results").Range("D3").Value = endDate
 
Upvote 0
Ralajer said:
Since the variables in which you store them in don't get changed then all you have to do is output them at the end of your code.

Code: 
    startDate = Range("A1").Value
    endDate = Range("A2").Value
'...
'...code
'...
    Worksheets("Results").Range("D2").Value = startDate
    Worksheets("Results").Range("D3").Value = endDate
Click to expand...


Ok I wasn't sure if they would store the values because after the dates are used, the info in A1 and A2 is deleted and replaced.

I will try this and let you know the results.

Thank you.
 
Upvote 0
Glad to hear it works. For future reference anytime you assign a value to a variable that value gets stored in the computer's memory and will remain there until you change it or the the sub ends.

Here just a simple breakdown

Dim startDate As Date
— Allocates memory of size date to the variable startDate

startDate = Range("A1").Value
— Takes the value stored in A1 and copies it to the memory allocated by the dim statement.
 
Last edited:
Upvote 0
Ralajer said:
Glad to hear it works. For future reference anytime you assign a value to a variable that value gets stored in the computer's memory and will remain there until you change it or the the sub ends.

Here just a simple breakdown

Dim startDate As Date
— Allocates memory of size date to the variable startDate

startDate = Range("A1").Value
— Takes the value stored in A1 and copies it to the memory allocated by the dim statement.
Click to expand...

I will definitely remember and use this in the future. Thanks for the explanation.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
How use RC formulas when fill three columns
2
Replies
18
Views
233
MARK858
MARK858
Noid67
  • Question Question
Run-time 1004 for this With statement
Replies
2
Views
176
Peter_SSs
Peter_SSs
Sean15
  • Question Question
Could you help improve recorded macro
2
Replies
13
Views
462
Sean15
Sean15
J
  • Question Question
VBA removing heading in spreadsheet
Replies
3
Views
273
rlv01
R
W
  • Solved
Looking For Tips To Improve My VBA
Replies
8
Views
227
who am i
W

Forum statistics

Threads
1,224,599
Messages
6,179,831
Members
452,947
Latest member
Gerry_F

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