Offset code - only if value matches

[VbaHell]

Hello all

I hope you can help on this please

In column "F" I have various territory codes
In column "E" starting from row 13 I have user names

What I need is IF CELL IN COLUMN "F" = "OFF" THEN ENTER IN THAT CELL THE VALUE FROM "E" (Same Row) if not leave the values in this cell

Example would be

John WR
Sue OFF

Result

John WR
Sue Sue

This is the code that I am trying to change to work correctly because it's very similar to what I need but I can't get it right

Sub OffChange()
Sheets("Dashboard").Select
With Range("E13", Range("F" & Rows.Count).End(xlUp).Offset(, -1))
      .Value = Evaluate(Replace("If(@=""""," & .Address & ",""OFF"")", "@", .Offset(, 1).Address))
   End With

End Sub

 

[Tim_Excel_]

Sub OffChange()

With Sheets("Dashboard")

Lrow = .Cells(.Rows.Count, "F").End(xlUp).Row

For each c in .Range("F2:F" & Lrow)
    If c.Value = "OFF" Then c.Value = c.Offset(0,-1).Value
Next c

End with

End Sub

 

[VbaHell]

Tim this is great and just what I needed

Thanks very much

 

[Tim_Excel_]

Glad to have helped

 

[VbaHell]

Tim can I bother you again please
If I want to add three more c.Value to this code, Con, Exp, ST
is it best just to add the code three times or can it be included in the existing lines

 

[Tim_Excel_]

Let me help you by explaining the code, that works best to learn to do it yourself

With Sheets("Dashboard") 

End With

Every statement within these lines will start with Sheets("Dashboard") IF you start the statement with a period. So, .Range("A1") will technically be Sheets("Dashboard").Range("A1"). This will ensure you that the code is referring to the correct sheet.

Lrow = .Cells(.Rows.Count, "F").End(xlUp).Row

This lines populates variable Lrow with the row number of the last filled row (for column F)

For each c in .Range("F2:F" & Lrow)
'
Next c

This initialises a loop. Specifically, it loops for each object (which I named c) in the specified range. Thus, it will loop through all cells in col F from row 2 until the last row.

If c.Value = "OFF" Then c.Value = c.Offset(0,-1).Value

Within the loop, if the value of the object (cell) is "OFF", then that value has to become that of the cell in column to the right of it. This is where you want to change things, since this is the code that changes the values. I'm not too sure what you want to do, but for one you could have multiple if statements.

If c.Value = "OFF" Then 'do something
ElseIf c.Value = "Con" Then 'do something
ElseIf'....
Else'...
End If

Alternatively you could set the value of c.Offset(0,-1) to other cells too

If c.Value = "OFF" Then
c.Value = c.Offset(0,-1).Value
c.Offset(0,1).Value = c.Offset(0,-1).Value
.Cells(c.Row, "K").Value = c.Offset(0,-1).Value
End If

The possibilities are endless...

 

[VbaHell]

Tim thank you for breaking this down, I can work it out from here as it's very clear
Thanks again

 

[Tim_Excel_]

Glad to have helped and good luck