How to move cell to LEFT?

[Rampage598]

Currently using this to move one cell to right:
ActiveCell.Offset(1, 0)

I am not getting how to move one cell to left....

 

[Joe4]

See if this does what you want (notice no "ActiveCell" or "Select" in the code):

Sub test2()

Dim rng As Range
Dim lastCol As String
Dim lastRow As Long

Sheets("FinalData").Select

'Set range variable to last column in row 2 with data
Set rng = Cells(2, Columns.Count).End(xlToLeft)

'Find last column letter of this cell in row 2
lastCol = Split(rng.Address, "$")(1)

'Find last row in column A with data
lastRow = Cells(Rows.Count, "A").End(xlUp).Row

'Populate new headers in row 2
rng.Offset(0, 1) = "Running"
rng.Offset(0, 2) = "Cycling"
rng.Offset(0, 3) = "Strength(Mins)"

'Populate formulas into row 2
rng.Offset(1, 1).Formula = "=Sumif($B$2:" & lastCol & "$2,""*Running*"",B3:" & lastCol & "3)"
rng.Offset(1, 2).Formula = "=Sumif($B$2:" & lastCol & "$2,""*Cycling*"",B3:" & lastCol & "3)"
rng.Offset(1, 3).Formula = "=Sumif($B$2:" & lastCol & "$2,""*Strength(Mins)*"",B3:" & lastCol & "3)"

'Copy down for all rows
Range(rng.Offset(1, 1), rng.Offset(1, 3)).Copy Range(rng.Offset(1, 1), rng.Offset(lastRow - 2, 1))

End Sub

 

[Rampage598]

See if this does what you want (notice no "ActiveCell" or "Select" in the code):

Sub test2()

Dim rng As Range
Dim lastCol As String
Dim lastRow As Long

Sheets("FinalData").Select

'Set range variable to last column in row 2 with data
Set rng = Cells(2, Columns.Count).End(xlToLeft)

'Find last column letter of this cell in row 2
lastCol = Split(rng.Address, "$")(1)

'Find last row in column A with data
lastRow = Cells(Rows.Count, "A").End(xlUp).Row

'Populate new headers in row 2
rng.Offset(0, 1) = "Running"
rng.Offset(0, 2) = "Cycling"
rng.Offset(0, 3) = "Strength(Mins)"

'Populate formulas into row 2
rng.Offset(1, 1).Formula = "=Sumif($B$2:" & lastCol & "$2,""*Running*"",B3:" & lastCol & "3)"
rng.Offset(1, 2).Formula = "=Sumif($B$2:" & lastCol & "$2,""*Cycling*"",B3:" & lastCol & "3)"
rng.Offset(1, 3).Formula = "=Sumif($B$2:" & lastCol & "$2,""*Strength(Mins)*"",B3:" & lastCol & "3)"

'Copy down for all rows
Range(rng.Offset(1, 1), rng.Offset(1, 3)).Copy Range(rng.Offset(1, 1), rng.Offset(lastRow - 2, 1))

End Sub

Thanks for your help its working properly

 

[Joe4]

You are welcome!

I tried to document my code to explain what I am doing in each section.
Let me know if you have any questions about any of the code.

 

[Rampage598]

You are welcome!

I tried to document my code to explain what I am doing in each section.
Let me know if you have any questions about any of the code.

Can you tell me how to add "Day wise total" on above Cycling header?

Like this

 

[Joe4]

Add this to the header section of code:

rng.Offset(-1, 2) = "Day wise total"

Basically, we have hard-coded the "rng" range variable to be the last column in row 2 with data (your last header), and then just working from there using Offset (always starting out from that cell).

 

[Rampage598]

Add this to the header section of code:

rng.Offset(-1, 2) = "Day wise total"

Basically, we have hard-coded the "rng" range variable to be the last column in row 2 with data (your last header), and then just working from there using Offset (always starting out from that cell).

Once Again Thankyou