Excel File not working on one computer but will on another.

TrainerBlue

New Member
Joined
Jan 6, 2021
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
Hi so I have my macro working for this file I have to track stats of my team that I managed. It works fine on my laptop but when I try and move it over to the big desktop it crashes at a certain point in the macro. I get a "Compile Error: Sub or Function not defined" and it highlights "x64Solution". I'm not sure what's going on.

VBA Code:
Sub Amnesty()
    Call LudicrousMode(True)
    Dim HTTP As Object, JSON As Object, Script As Object, TableRow As Object, Keys As Object, SubKeys As Object
    Dim x64 As Object
    Dim nowUTC As U, utcDate As Date, utcDiff As Double
    Dim Key As Variant, SubKey As Variant
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim shtraw As Worksheet: Set shtraw = wb.Worksheets("Amnesty")
    Dim main As Worksheet: Set main = wb.Worksheets("MainNumbers")
    Dim PayLoad As String
    Dim y As Integer
    Set HTTP = CreateObject("WinHTTP.WinHTTPRequest.5.1")
    FC = main.Range("T2").Value
    Call GetSystemTime(nowUTC)
    sdate = main.Range("X1").Value
    edate = main.Range("X2").Value
    StartTime = "18:00"
    EndTime = "06:00"
    
    Dim lte As Double, gte As Double
    Dim date1 As Date, date2 As Date
    
    ' Set our dates
    date2 = CDate(CStr(sdate) + " " + CStr(DateAdd("h", 4, CDate(StartTime))))
    date1 = CDate(CStr(edate) + " " + CStr(DateAdd("h", 4, CDate(EndTime))))
    
    'Debug.Print date2
    'Debug.Print date1
    ' Set our Less than and Greater than range
    lte = (date1 - 25569) * 86400000
    gte = (date2 - 25569) * 86400000
    Debug.Print lte, gte
    
    Sheets("Amnesty").Select
Range("A:F").ClearContents

PayLoad = "{""index"":""quality_intelligence_amnesty"",""search_type"":""count"",""ignore_unavailable"":true}"
PayLoad = PayLoad & Chr(10)
PayLoad = PayLoad & "{""query"":{""filtered"":{""query"":{""query_string"":{""query"":""problem_status: \""Resolved\"""",""analyze_wildcard"":true}}"
PayLoad = PayLoad & ",""filter"":{""bool"":{""must"":[{""query"":{""query_string"":{""analyze_wildcard"":true,""query"":""warehouse_id:" & FC & """}}},"
PayLoad = PayLoad & "{""range"":{""last_updated_date"":{""gte"":" & gte & ",""lte"":" & lte & "}}}],""must_not"":[]}}}}"
PayLoad = PayLoad & ",""size"":0,""aggs"":{""3"":{""terms"":{""field"":""source_defect_found"",""size"":0,""order"":{""1"":""desc""}}"
PayLoad = PayLoad & ",""aggs"":{""1"":{""sum"":{""field"":""addback_quantity""}},""4"":{""terms"":{""field"":""fnsku"",""size"":5"
PayLoad = PayLoad & ",""order"":{""1"":""desc""}},""aggs"":{""1"":{""sum"":{""field"":""addback_quantity""}}}}}}}}"
PayLoad = PayLoad & Chr(10)
PayLoad = PayLoad & "{""index"":""quality_intelligence_amnesty"",""search_type"":""count"",""ignore_unavailable"":true}"
PayLoad = PayLoad & Chr(10)
PayLoad = PayLoad & "{""query"":{""filtered"":{""query"":{""query_string"":{""query"":""problem_status: \""Resolved\"""",""analyze_wildcard"":true}}"
PayLoad = PayLoad & ",""filter"":{""bool"":{""must"":[{""query"":{""query_string"":"
PayLoad = PayLoad & "{""analyze_wildcard"":true,""query"":""warehouse_id: " & FC & """}}},"
PayLoad = PayLoad & "{""range"":{""last_updated_date"":{""gte"":" & gte & ",""lte"":" & lte & "}}}],"
PayLoad = PayLoad & " ""must_not"":[]}}}},""size"":0,""aggs"":{""3"":{""terms"":{""field"":""source_defect_found"",""size"":0,"
PayLoad = PayLoad & " ""order"":{""1"":""desc""}},""aggs"":{""1"":{""sum"":{""field"":""addback_quantity""}},"
PayLoad = PayLoad & " ""4"":{""terms"":{""field"":""addback_user"",""size"":0,""order"":{""1"":""desc""}},"
PayLoad = PayLoad & " ""aggs"":{""1"":{""sum"":{""field"":""addback_quantity""}}}}}}}}"
PayLoad = PayLoad & Chr(10)
PayLoad = PayLoad & "{""index"":""quality_intelligence_amnesty"",""search_type"":""count"",""ignore_unavailable"":true}"
PayLoad = PayLoad & Chr(10)
PayLoad = PayLoad & "{""query"":{""filtered"":{""query"":{""query_string"":{""query"":""problem_status: \""Resolved\"""",""analyze_wildcard"":true}}"
PayLoad = PayLoad & ",""filter"":{""bool"":{""must"":[{""query"":{""query_string"":{""analyze_wildcard"":true,""query"":""warehouse_id: " & FC & """}}}"
PayLoad = PayLoad & ",{""range"":{""last_updated_date"":{""gte"":" & gte & ",""lte"":" & lte & "}}}],""must_not"":[]}}}}"
PayLoad = PayLoad & ",""size"":0,""aggs"":{""3"":{""terms"":{""field"":""source_defect_found"",""size"":0,""order"":{""1"":""desc""}}"
PayLoad = PayLoad & ",""aggs"":{""1"":{""sum"":{""field"":""addback_quantity""}},""4"":{""terms"":{""field"":""addback_user"",""size"":0"
PayLoad = PayLoad & ",""order"":{""1"":""desc""}},""aggs"":{""1"":{""sum"":{""field"":""addback_quantity""}}"
PayLoad = PayLoad & ",""5"":{""filters"":{""filters"":{""/AMZN:.*/"":{""query"":"
PayLoad = PayLoad & "{""query_string"":{""query"":""/AMZN:.*/"",""analyze_wildcard"":true}}}}},""aggs"":{""1"":{""sum"":{""field"":""addback_quantity""}}}}}}}}}}"
PayLoad = PayLoad & Chr(10)
PayLoad = PayLoad & "{""index"":""quality_intelligence_amnesty"",""search_type"":""count"",""ignore_unavailable"":true}"
PayLoad = PayLoad & Chr(10)
PayLoad = PayLoad & "{""query"":{""filtered"":{""query"":{""query_string"":{""query"":""problem_status: \""Resolved\"""",""analyze_wildcard"":true}}"
PayLoad = PayLoad & ",""filter"":{""bool"":{""must"":[{""query"":{""query_string"":{""analyze_wildcard"":true,""query"":""warehouse_id: " & FC & """}}}"
PayLoad = PayLoad & ",{""range"":{""last_updated_date"":{""gte"":" & gte & ",""lte"":" & lte & "}}}],""must_not"":[]}}}}"
PayLoad = PayLoad & ",""size"":0,""aggs"":{""3"":{""terms"":{""field"":""source_defect_found"",""size"":0,""order"":{""1"":""desc""}}"
PayLoad = PayLoad & ",""aggs"":{""1"":{""sum"":{""field"":""addback_quantity""}},""4"":{""terms"":{""field"":""addback_user"",""size"":0"
PayLoad = PayLoad & ",""order"":{""1"":""desc""}},""aggs"":{""1"":{""sum"":{""field"":""addback_quantity""}}"
PayLoad = PayLoad & ",""5"":{""filters"":{""filters"":{""NOT /AMZN:.*/"":{""query"":{""query_string"":"
PayLoad = PayLoad & "{""query"":""NOT /AMZN:.*/"",""analyze_wildcard"":true}}}}},""aggs"":{""1"":{""sum"":{""field"":""addback_quantity""}}}}}}}}}}"
PayLoad = PayLoad & Chr(10)
PayLoad = PayLoad & "{""index"":""quality_intelligence_amnesty"",""search_type"":""count"",""ignore_unavailable"":true}"
PayLoad = PayLoad & Chr(10)
PayLoad = PayLoad & "{""query"":{""filtered"":{""query"":{""query_string"":{""query"":""*"",""analyze_wildcard"":true}}"
PayLoad = PayLoad & ",""filter"":{""bool"":{""must"":[{""query"":{""query_string"":{""analyze_wildcard"":true,""query"":""warehouse_id: " & FC & """}}}"
PayLoad = PayLoad & ",{""range"":{""last_updated_date"":{""gte"":" & gte & ",""lte"":" & lte & "}}}],""must_not"":[]}}}}"
PayLoad = PayLoad & ",""size"":0,""aggs"":{""2"":{""terms"":{""field"":""addback_user"",""size"":0,""order"":{""1"":""desc""}}"
PayLoad = PayLoad & ",""aggs"":{""1"":{""sum"":{""field"":""addback_quantity""}},""3"":{""filters"":{""filters"":{""Station /AMZN:.*/"":"
PayLoad = PayLoad & "{""query"":{""query_string"":{""analyze_wildcard"":true,""query"":""Station /AMZN:.*/""}}},""NOT /AMZN:.*/"":"
PayLoad = PayLoad & "{""query"":{""query_string"":{""analyze_wildcard"":true,""query"":""NOT /AMZN:.*/""}}}}},""aggs"":{""1"":"
PayLoad = PayLoad & "{""sum"":{""field"":""addback_quantity""}}}}}}}}"
PayLoad = PayLoad & Chr(10)
PayLoad = PayLoad & "{""index"":""quality_intelligence_amnesty"",""search_type"":""count"",""ignore_unavailable"":true}"
PayLoad = PayLoad & Chr(10)
PayLoad = PayLoad & "{""query"":{""filtered"":{""query"":{""query_string"":{""query"":""problem_status:"
PayLoad = PayLoad & " \""Resolved\"""",""analyze_wildcard"":true}},""filter"":{""bool"":{""must"":[{""query"":{""query_string"":"
PayLoad = PayLoad & "{""analyze_wildcard"":true,""query"":""warehouse_id: " & FC & """}}},{""range"":{""last_updated_date"":"
PayLoad = PayLoad & "{""gte"":" & gte & ",""lte"":" & lte & "}}}],""must_not"":[]}}}},""size"":0,""aggs"":{""3"":{""terms"":"
PayLoad = PayLoad & "{""field"":""source_defect_found"",""size"":0,""order"":{""1"":""desc""}},""aggs"":{""1"":{""sum"":"
PayLoad = PayLoad & "{""field"":""addback_quantity""}},""4"":{""terms"":{""field"":""warehouse_id"",""size"":0,""order"":"
PayLoad = PayLoad & "{""1"":""desc""}},""aggs"":{""1"":{""sum"":{""field"":""addback_quantity""}},""5"":{""filters"":"
PayLoad = PayLoad & "{""filters"":{""NOT /AMZN:.*/"":{""query"":{""query_string"":{""query"":""NOT /AMZN:.*/"",""analyze_wildcard"":true}}}}}"
PayLoad = PayLoad & ",""aggs"":{""1"":{""sum"":{""field"":""addback_quantity""}}}}}}}}}}"
PayLoad = PayLoad & Chr(10)
PayLoad = PayLoad & "{""index"":""quality_intelligence_amnesty"",""search_type"":""count"",""ignore_unavailable"":true}"
PayLoad = PayLoad & Chr(10)
PayLoad = PayLoad & "{""query"":{""filtered"":{""query"":{""query_string"":{""query"":""problem_status: \""Resolved\"""",""analyze_wildcard"":true}}"
PayLoad = PayLoad & ",""filter"":{""bool"":{""must"":[{""query"":{""query_string"":{""analyze_wildcard"":true,""query"":""warehouse_id: " & FC & """}}}"
PayLoad = PayLoad & ",{""range"":{""last_updated_date"":{""gte"":" & gte & ",""lte"":" & lte & "}}}]"
PayLoad = PayLoad & ",""must_not"":[]}}}},""size"":0,""aggs"":{""3"":{""terms"":{""field"":""source_defect_found"",""size"":0"
PayLoad = PayLoad & ",""order"":{""1"":""desc""}},""aggs"":{""1"":{""sum"":{""field"":""addback_quantity""}},""4"":{""terms"":"
PayLoad = PayLoad & "{""field"":""warehouse_id"",""size"":0,""order"":{""1"":""desc""}},""aggs"":{""1"":{""sum"":{""field"":""addback_quantity""}}"
PayLoad = PayLoad & ",""5"":{""filters"":{""filters"":{""Station /AMZN:.*/"":{""query"":"
PayLoad = PayLoad & "{""query_string"":{""query"":""Station /AMZN:.*/"",""analyze_wildcard"":true}}}}},""aggs"":{""1"":"
PayLoad = PayLoad & "{""sum"":{""field"":""addback_quantity""}}}}}}}}}}"
PayLoad = PayLoad & Chr(10)
PayLoad = PayLoad & "{""index"":""quality_intelligence_amnesty"",""search_type"":""count"",""ignore_unavailable"":true}"
PayLoad = PayLoad & Chr(10)
PayLoad = PayLoad & "{""query"":{""filtered"":{""query"":{""query_string"":{""query"":""*"",""analyze_wildcard"":true}}"
PayLoad = PayLoad & ",""filter"":{""bool"":{""must"":[{""query"":{""query_string"":{""analyze_wildcard"":true,""query"":""warehouse_id: " & FC & """}}}"
PayLoad = PayLoad & ",{""range"":{""last_updated_date"":{""gte"":" & gte & ",""lte"":" & lte & "}}}]"
PayLoad = PayLoad & ",""must_not"":[]}}}},""size"":0,""aggs"":{""2"":{""terms"":{""field"":""warehouse_id"",""size"":0"
PayLoad = PayLoad & ",""order"":{""1"":""desc""}},""aggs"":{""1"":{""sum"":{""field"":""addback_quantity""}},""3"":{""filters"":"
PayLoad = PayLoad & "{""filters"":{""Station /AMZN:.*/"":{""query"":{""query_string"":{""query"":""Station /AMZN:.*/"",""analyze_wildcard"":true}}}"
PayLoad = PayLoad & ",""NOT /AMZN:.*/"":{""query"":{""query_string"":{""query"":""NOT /AMZN:.*/"",""analyze_wildcard"":true}}}}},""aggs"":"
PayLoad = PayLoad & "{""1"":{""sum"":{""field"":""addback_quantity""}}}}}}}}"
PayLoad = PayLoad & Chr(10)
PayLoad = PayLoad & "{""index"":""quality_intelligence_amnesty"",""search_type"":""count"",""ignore_unavailable"":true}"
PayLoad = PayLoad & Chr(10)
PayLoad = PayLoad & "{""query"":{""filtered"":{""query"":{""query_string"":{""query"":""problem_status: \""Resolved\"""",""analyze_wildcard"":true}}"
PayLoad = PayLoad & ",""filter"":{""bool"":{""must"":[{""query"":{""query_string"":{""analyze_wildcard"":true,""query"":""warehouse_id: " & FC & """}}}"
PayLoad = PayLoad & ",{""range"":{""last_updated_date"":{""gte"":" & gte & ",""lte"":" & lte & "}}}],""must_not"":[]}}}},""size"":0,""aggs"":"
PayLoad = PayLoad & "{""3"":{""terms"":{""field"":""source_defect_found"",""size"":0,""order"":"
PayLoad = PayLoad & "{""1"":""desc""}},""aggs"":{""1"":{""sum"":{""field"":""addback_quantity""}},""4"":{""terms"":"
PayLoad = PayLoad & "{""field"":""warehouse_id"",""size"":0,""order"":{""1"":""desc""}},""aggs"":{""1"":{""sum"":{""field"":""addback_quantity""}}}}}}}}"
PayLoad = PayLoad & Chr(10)
PayLoad = PayLoad & "{""index"":""quality_intelligence_amnesty"",""search_type"":""count"",""ignore_unavailable"":true}"
PayLoad = PayLoad & Chr(10)
PayLoad = PayLoad & "{""query"":{""filtered"":{""query"":{""query_string"":{""query"":""problem_status:"
PayLoad = PayLoad & " \""Resolved\"""",""analyze_wildcard"":true}},""filter"":{""bool"":{""must"":[{""query"":{""query_string"":"
PayLoad = PayLoad & "{""analyze_wildcard"":true,""query"":""warehouse_id: " & FC & """}}}"
PayLoad = PayLoad & ",{""range"":{""last_updated_date"":{""gte"":" & gte & ",""lte"":" & lte & "}}}],""must_not"":[]}}}}"
PayLoad = PayLoad & ",""size"":0,""aggs"":{""4"":{""terms"":{""field"":""addback_reference_bin_id"",""size"":5,""order"":"
PayLoad = PayLoad & "{""1"":""desc""}},""aggs"":{""1"":{""sum"":{""field"":""addback_quantity""}}}}}}"
PayLoad = PayLoad & Chr(10)
PayLoad = PayLoad & "{""index"":""quality_intelligence_amnesty"",""search_type"":""count"",""ignore_unavailable"":true}"
PayLoad = PayLoad & Chr(10)
PayLoad = PayLoad & "{""query"":{""filtered"":{""query"":{""query_string"":{""query"":""problem_status:"
PayLoad = PayLoad & " \""Resolved\"""",""analyze_wildcard"":true}},""filter"":{""bool"":{""must"":[{""query"":{""query_string"":"
PayLoad = PayLoad & "{""analyze_wildcard"":true,""query"":""warehouse_id: " & FC & """}}}"
PayLoad = PayLoad & ",{""range"":{""last_updated_date"":{""gte"":" & gte & ",""lte"":" & lte & "}}}]"
PayLoad = PayLoad & ",""must_not"":[]}}}},""size"":0,""aggs"":{""3"":{""terms"":{""field"":""source_defect_found"",""size"":0"
PayLoad = PayLoad & ",""order"":{""1"":""desc""}},""aggs"":{""1"":{""sum"":{""field"":""addback_quantity""}},""4"":{""terms"":"
PayLoad = PayLoad & "{""field"":""addback_reference_bin_id"",""size"":5,""order"":{""1"":""desc""}}"
PayLoad = PayLoad & ",""aggs"":{""1"":{""sum"":{""field"":""addback_quantity""}}}}}}}}"
PayLoad = PayLoad & Chr(10)
PayLoad = PayLoad & "{""index"":""quality_intelligence_amnesty"",""ignore_unavailable"":true}"
PayLoad = PayLoad & Chr(10)
PayLoad = PayLoad & "{""highlight"":{""pre_tags"":[""@kibana-highlighted-field@""],""post_tags"":"
PayLoad = PayLoad & "[""@/kibana-highlighted-field@""],""fields"":{""*"":{}},""fragment_size"":2147483647}"
PayLoad = PayLoad & ",""query"":{""filtered"":{""query"":{""query_string"":{""query"":""*"",""analyze_wildcard"":true}}"
PayLoad = PayLoad & ",""filter"":{""bool"":{""must"":[{""query"":{""query_string"":"
PayLoad = PayLoad & "{""analyze_wildcard"":true,""query"":""warehouse_id: " & FC & """}}}"
PayLoad = PayLoad & ",{""range"":{""last_updated_date"":{""gte"":" & gte & ",""lte"":" & lte & "}}}]"
PayLoad = PayLoad & ",""must_not"":[]}}}},""size"":1000000,""sort"":[{""last_updated_date"":"
PayLoad = PayLoad & "{""order"":""desc"",""unmapped_type"":""boolean""}}],""fields"":[""*"",""_source""],""script_fields"":{}"
PayLoad = PayLoad & ",""fielddata_fields"":[""last_updated_date"",""created_date""]}"
PayLoad = PayLoad & Chr(10)
PayLoad = PayLoad & "{""index"":""quality_intelligence_amnesty"",""search_type"":""count"",""ignore_unavailable"":true}"
PayLoad = PayLoad & Chr(10)
PayLoad = PayLoad & "{""query"":{""filtered"":{""query"":{""query_string"":{""query"":""problem_status: \""Resolved\"""",""analyze_wildcard"":true}}"
PayLoad = PayLoad & ",""filter"":{""bool"":{""must"":[{""query"":{""query_string"":{""analyze_wildcard"":true,""query"":""warehouse_id: " & FC & """}}}"
PayLoad = PayLoad & ",{""range"":{""last_updated_date"":{""gte"":" & gte & ",""lte"":" & lte & "}}}],""must_not"":[]}}}}"
PayLoad = PayLoad & ",""size"":0,""aggs"":{""4"":{""terms"":{""field"":""fnsku"",""size"":5,""order"":{""1"":""desc""}}"
PayLoad = PayLoad & ",""aggs"":{""1"":{""sum"":{""field"":""addback_quantity""}}}}}}"
PayLoad = PayLoad & Chr(10)

Debug.Print PayLoad

    With HTTP

        .Open "GET", "https://qi-rpt-iad.iad.proxy.amazon.com/"
        .SetAutoLogonPolicy 0
        .send
'        'Debug.Print HTTP.responseText

        .Open "GET", "https://qi-rpt-iad.iad.proxy.amazon.com/sso/login"
        .SetAutoLogonPolicy 0
        .send
'        'Debug.Print HTTP.responseText

        .Open "POST", "https://qi-rpt-iad.iad.proxy.amazon.com/elasticsearch/_msearch?timeout=0&ignore_unavailable=true&preference=" & (Now() - 25569) * 86400 & "123"
        .setRequestHeader "Content-Type", "application/json;charset=UTF-8"
        .setRequestHeader "Origin", "https://qi-rpt-iad.iad.proxy.amazon.com"
        .setRequestHeader "Referer", " https://qi-rpt-iad.iad.proxy.amazon.com/"
        .send PayLoad
        'Debug.Print HTTP.responseText
    End With
'
    #If Win64 Then
Set x64 = x64Solution()
        x64.execScript "Function CreateObjectx86(sProgID) Set CreateObjectx86 = CreateObject(sProgID): End Function", "VBScript"
        Set Script = x64.CreateObjectx86("MSScriptControl.ScriptControl")
    #Else
        Set Script = CreateObject("ScriptControl")
    #End If

    Script.Language = "JScript"
    Script.AddCode "function keys(O) { var k = new Array(); for (var x in O) { k.push(x); } return k; } "

shtraw.Range("A1").Value = "Addback User"
shtraw.Range("B1").Value = "Bin Floor Mod"
shtraw.Range("C1").Value = "Pick Area/Floor"
shtraw.Range("D1").Value = "Quantity"
shtraw.Range("E1").Value = "Addback Reference ID"
shtraw.Range("F1").Value = "Location ID"
    
    Set JSON = Script.Eval("(" & HTTP.responseText & ")")
    Set JSON = CallByName(JSON, "responses", VbGet)
    Set JSON = CallByName(JSON, "11", VbGet)
    Set JSON = CallByName(JSON, "hits", VbGet)
    Set JSON = CallByName(JSON, "hits", VbGet)
    Set Keys = Script.Run("keys", JSON)
    DoEvents


    R = 2
    For Each Key In Keys
        Set TableRow = CallByName(JSON, Key, VbGet)
        Set TableRow = CallByName(TableRow, "_source", VbGet)

        On Error Resume Next
        shtraw.Cells(R, 1) = CallByName(TableRow, "addback_user", VbGet)
        shtraw.Cells(R, 2) = CallByName(TableRow, "addback_bin_floor_mod", VbGet)
        shtraw.Cells(R, 3) = CallByName(TableRow, "addback_pick_area", VbGet)
        shtraw.Cells(R, 4) = CallByName(TableRow, "addback_quantity", VbGet)
        shtraw.Cells(R, 5) = CallByName(TableRow, "addback_reference_bin_id", VbGet)
        shtraw.Cells(R, 6) = CallByName(TableRow, "addback_location_id", VbGet)
        shtraw.Cells(R, 7) = Format(((CallByName(TableRow, "created_date", VbGet) / 86400000) + 25569) - utcDiff, "mm/dd/yyyy hh:mm:ss")
        shtraw.Cells(R, 8) = Format(((CallByName(TableRow, "last_updated_date", VbGet) / 86400000) + 25569) - utcDiff, "mm/dd/yyyy hh:mm:ss")
    R = R + 1
    Next Key
    Call LudicrousMode(False)
    Call find_Station_Floor
End Sub

Sub find_Station_Floor()

    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim raw As Worksheet: Set raw = wb.Worksheets("Find_Rate_Raw")
    Dim sht As Worksheet: Set sht = wb.Worksheets("Kiva_Tech_Find_Rate")
    Dim lrow As Long
    
    sht.Range("B5:G500").Cells.Delete
    
    With raw
    
        lrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("G1").Value = "Station/Floor"
        .Range("G2:G" & lrow).FormulaR1C1 = "=IF(LEFT(RC5,2)=""P-"",""Floor"",""Station"")"
        
        sht.Range("B5:B" & lrow + 3).Value = .Range("A2:A" & lrow).Value
    End With
    
    With sht
        lrow = .Cells(.Rows.Count, "B").End(xlUp).Row
        .Range("B5:B" & lrow).RemoveDuplicates Columns:=1, Header:=xlNo
        lrow = .Cells(.Rows.Count, "B").End(xlUp).Row
        .Range("C5:C" & lrow).FormulaR1C1 = "=Sumifs('Find_Rate_Raw'!C4,'Find_Rate_Raw'!C1,RC2,'Find_Rate_Raw'!C7,""Station"")"
        .Range("D5:D" & lrow).FormulaR1C1 = "=Sumifs('Find_Rate_Raw'!C4,'Find_Rate_Raw'!C1,RC2,'Find_Rate_Raw'!C7,""Floor"")"
        .Range("E5:E" & lrow).FormulaR1C1 = "=Sumifs('Find_Rate_Raw'!C4,'Find_Rate_Raw'!C1,RC2)"
        .Range("F5:F" & lrow).FormulaR1C1 = "=RC3/RC5"
        .Range("F5:F" & lrow).NumberFormat = "0.00%"
        .Range("G5:G" & lrow).FormulaR1C1 = "=RC4/RC5"
        .Range("G5:G" & lrow).NumberFormat = "0.00%"
        
        With .Range("B4:G4").Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent4
            .TintAndShade = 0.399975585192419
            .PatternTintAndShade = 0
        End With
        
        With .Range("B4:G4").Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With .Range("B4:G4").Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With .Range("B4:G4").Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With .Range("B4:G4").Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With .Range("B4:G4").Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With .Range("B4:G4").Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        
        .Range("B4:G4").Borders(xlDiagonalDown).LineStyle = xlNone
        .Range("B4:G4").Borders(xlDiagonalUp).LineStyle = xlNone
        With .Range("B4:G4").Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With .Range("B4:G4").Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With .Range("B4:G4").Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With .Range("B4:G4").Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With .Range("B4:G4").Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        .Range("B4:G4").Borders(xlInsideHorizontal).LineStyle = xlNone
        Range(Range("B5:G" & lrow), Selection.End(xlDown)).Select
        Range("B5:G" & lrow).Borders(xlDiagonalDown).LineStyle = xlNone
        Range("B5:G" & lrow).Borders(xlDiagonalUp).LineStyle = xlNone
        With .Range("B5:G" & lrow).Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With .Range("B5:G" & lrow).Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With .Range("B5:G" & lrow).Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With .Range("B5:G" & lrow).Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With .Range("B5:G" & lrow).Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With .Range("B5:G" & lrow).Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With .Range("B5:G" & lrow).Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        
        'Condition formatting for Column F
        
        .Range("F5:F" & lrow).FormatConditions.AddColorScale ColorScaleType:=3
        .Range("F5:F" & lrow).FormatConditions(.Range("F5:F" & lrow).FormatConditions.Count).SetFirstPriority
        .Range("F5:F" & lrow).FormatConditions(1).ColorScaleCriteria(1).Type = _
            xlConditionValueLowestValue
        With .Range("F5:F" & lrow).FormatConditions(1).ColorScaleCriteria(1).FormatColor
            .Color = 7039480
            .TintAndShade = 0
        End With
        .Range("F5:F" & lrow).FormatConditions(1).ColorScaleCriteria(2).Type = _
            xlConditionValuePercentile
        .Range("F5:F" & lrow).FormatConditions(1).ColorScaleCriteria(2).Value = 50
        With .Range("F5:F" & lrow).FormatConditions(1).ColorScaleCriteria(2).FormatColor
            .Color = 8711167
            .TintAndShade = 0
        End With
        .Range("F5:F" & lrow).FormatConditions(1).ColorScaleCriteria(3).Type = _
            xlConditionValueHighestValue
        With .Range("F5:F" & lrow).FormatConditions(1).ColorScaleCriteria(3).FormatColor
            .Color = 8109667
            .TintAndShade = 0
        End With
    End With

End Sub
 

TrainerBlue

New Member
Joined
Jan 6, 2021
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
Do you have any Declare Function or Declare Sub statements? What references are set in your project?
1610791418111.png

These are the references are what are set. I did a search for Declare Function and Declare Sub statements in the project but I didn't see any.
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Watch MrExcel Video

Forum statistics

Threads
1,127,370
Messages
5,624,294
Members
416,018
Latest member
mirceaon

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
Top