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

T

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
 
Sort by date Sort by votes
RoryA said:
Do you have any Declare Function or Declare Sub statements? What references are set in your project?
Click to expand...
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.
 
Upvote 0

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
You must log in or register to reply here.

Similar threads

S
  • Question Question
Excel VBA infinitely looping in 64bit
Replies
8
Views
2K
Samitha156
S
S
  • Solved
Trying to copy another column from one table to another.
Replies
2
Views
570
Alex Blakenburg
A
T
  • Question Question
Excel VBA JSON issue
Replies
1
Views
3K
Dan_W
D
D
  • Question Question
VBA Error Saving, wrap text not working
Replies
0
Views
2K
DanielMZw
D
P
  • Question Question
Can anyone figure out why one sheet acts differently that another?
2
Replies
11
Views
2K
jbeaucaire
jbeaucaire

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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