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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Welcome to the Board!

A few things to check:

1. What versions of Excel are each computer using? Are they the same?

2. What I see most often in these situations is that the computer where it is NOT working does not have all the needed VBA libraries selected.
First go into the computer where it is working, go into the VB Edtior, and from the Tool menu, select References. Note all the References/Libraries that are selected.
Now repeat this step on the other computer. Are any selected References missing? If so, find and select the missing ones.
Note that if the two computers are using different versions of Excel, the version number of the references might differ slightly. Select the closest one (the text of the Reference should match, only the version number will differ slightly).
 
Upvote 0
Joe4 said:
Welcome to the Board!

A few things to check:

1. What versions of Excel are each computer using? Are they the same?

2. What I see most often in these situations is that the computer where it is NOT working does not have all the needed VBA libraries selected.
First go into the computer where it is working, go into the VB Edtior, and from the Tool menu, select References. Note all the References/Libraries that are selected.
Now repeat this step on the other computer. Are any selected References missing? If so, find and select the missing ones.
Note that if the two computers are using different versions of Excel, the version number of the references might differ slightly. Select the closest one (the text of the Reference should match, only the version number will differ slightly).
Click to expand...
From what I can tell both version of excel are both using the 64 version as well as the libraries are matching since I sent the file from machine to the other. As well as checking both libraries anyway.
 
Upvote 0
TrainerBlue said:
as the libraries are matching since I sent the file from machine to the other.
Click to expand...
Did you do as I ask and checked the libraries?
It does not matter that it is the same file. It is different computers.
 
Upvote 0
What exactly is "x64Solution()", that is called in your code?
It looks like that should a procedure or function that is defined somewhere, but it is not posted in your code anywhere.
Are you sure that it isn't part of some AddIn that is installed on one computer and not on the other?
 
Upvote 0
Joe4 said:
What exactly is "x64Solution()", that is called in your code?
It looks like that should a procedure or function that is defined somewhere, but it is not posted in your code anywhere.
Are you sure that it isn't part of some AddIn that is installed on one computer and not on the other?
Click to expand...
I'm not 100% sure tbh it's code I borrow from another macro used at my building. It doesn't cause any issues on the laptop I use. But when trying to use the file on another desktop that I use, it always crashes.
 
Upvote 0
It looks like it is probably a direct call to another VBA procedure or function. So the question becomes, where is it (because it is NOT in your code)?
Check to see which Add-Ins are installed on the computer that works, and check for the same Add-Ins on the computer where it does not work.
Also check to see if the computer where it works has a Personal Macro Workbook installed (maybe it is there).
 
Upvote 0
Joe4 said:
It looks like it is probably a direct call to another VBA procedure or function. So the question becomes, where is it (because it is NOT in your code)?
Check to see which Add-Ins are installed on the computer that works, and check for the same Add-Ins on the computer where it does not work.
Also check to see if the computer where it works has a Personal Macro Workbook installed (maybe it is there).
Click to expand...
I've got no add-ins on both the working and non working one. I checked the file where I initially pulled the code from but the only instance of x64Solution() is in the specific part of the code that I copied.
 
Upvote 0
Did you check for a Personal Macro Workbook on the computer where it is working?
The computer where it is working is finding it somewhere, so you have to find out where that is.
 
Upvote 0
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