Can someone help me with this? Please look at the code in yellow below and tell me how to re-write the code so it doesn't look for absolute cells but relative cells?
Sub LTWO()
'
' LTWO Macro
'
' Keyboard Shortcut: Ctrl+m
'
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Range("E1").Select
ActiveCell.FormulaR1C1 = "CAR MARK"
Dim x As Long
Dim BottomRow As Long
BottomRow = 0
x = 2 'starting row number
Do Until BottomRow > 0
Range("A" & x).Select
If ActiveCell.Value = "" Then
BottomRow = x - 1
End If
x = x + 1
Loop
Range("E2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-2],"" "",RC[-1])"
Selection.AutoFill Destination:=Range("E2:E" & BottomRow)
Range("E2:E" & BottomRow).Select
Columns("E:E").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("E:E").EntireColumn.AutoFit
Rows("2:2").Select
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True
Range("C4").Activate
Application.CutCopyMode = False
Set ws = ActiveSheet
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=Range( _
"B2:B" & BottomRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
Set ws = ActiveSheet
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=Range( _
"D2:D" & BottomRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.ActiveSheet.Sort
.SetRange Range("A1").CurrentRegion
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[2],RC[3],RC[4])"
Selection.AutoFill Destination:=Range("A2:A" & BottomRow)
Range("A2:A" & BottomRow).Select
Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=SUMIF(R[1]C[-11]:R[2572]C[-11],RC[-11],R[1]C[-1]:R[2572]C[-1])"
Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=SUMIF(R[1]C[-11]:R2574C1,RC[-11],R[1]C[-1]:R2574C11)"
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L" & BottomRow)
Range("L2:L" & BottomRow).Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("M2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
Range("M2").Select
Selection.AutoFill Destination:=Range("M2:M" & BottomRow)
Range("M2:M" & BottomRow).Select
Range("M2").Activate
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=Range("M2:M" & BottomRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.ActiveSheet.Sort
.SetRange Range("A1").CurrentRegion
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("M17").Select
Selection.End(xlDown).Select
Range("K2574:M2574").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("K2575").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2573]C:R[-1]C)"
Range("L2575").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2573]C:R[-1]C)"
Range("M2575").Select
Selection.FormulaR1C1 = "="
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
Range("M2576").Select
End Sub
Bookmarks