Fill in cell base on another cell value

[zermrodrigues]

Dear all,

I have been trying to adapt a macro to suit my purposes but cannot make the macro to add data on more that once cell. What I want is when the cell value on the column B is "Office" then the macro should write "TAG/IN" on the column F (which I have managed to get) and also "TAG/OUT" on the column N in the same line as the "Office" value shows up. I have been using this macro:

Sub Fill_F_column()
Dim Rng As Range
Dim T&, Lr&, S$
Application.ScreenUpdating = False
Lr = Range("B" & Rows.Count).End(xlUp).Row
For T = Lr To 2 Step -1
If Range("B" & T) = "Office" And Range("F" & T) = "" Then S = S & ",F" & T
If S <> "" Then
If Len(S) > 240 Or T = 2 Then Range(Mid(S, 2)) = "TAG/IN": S = ""
End If
Next T
Application.ScreenUpdating = True
End Sub

Can anyone halp me with this? Thank you. Kind regards, Jose

 

[My Aswer Is This]

Wen I first looked at what you were wanting I thought a sheet change event script would work.
For example, if you entered "Apple" in any cell in column B "Pie" would be entered in Column C same row.

But when I see in your script you have things like this:
"If Len(S) > 240 Or T = 2 Then Range(Mid(S, 2)) = "TAG/IN": S = """

I have no idea what you're wanting.
Please explain and maybe I can help you.

 

[Peter_SSs]

Welcome to the MrExcel board!

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block at the bottom of this post has more details. I have added the tags for you this time.

Is this what you mean? Test with a copy of your data.

Sub Fill_F_and_N_column()
  Dim Rng As Range
  Dim T&, Lr&, S$
 
  Application.ScreenUpdating = False
  Lr = Range("B" & Rows.Count).End(xlUp).Row
  For T = Lr To 2 Step -1
    If Range("B" & T) = "Office" And Range("F" & T) = "" Then S = S & ",F" & T
    If S <> "" Then
      If Len(S) > 240 Or T = 2 Then
        Range(Mid(S, 2)) = "TAG/IN"
        Range(Mid(S, 2)).Offset(, 8).Value = "TAG/OUT"
        S = ""
      End If
    End If
  Next T
  Application.ScreenUpdating = True
End Sub