TrainerBlue
New Member
- Joined
- Jan 6, 2021
- Messages
- 12
- Office Version
- 2016
- Platform
- 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