Offset partial values to the left

[zack8576]

I need to offset the first 3 characters in column O to the left, add them infront of the existing text in K (with parenthesis) ONLY WHEN the value in column F is P1

so in the example below, row 2, 7, and 14 have P1 in column F, after I run the macro column F on these rows will look like:
(D B) D Base,6"w
((D J) 58x60",Base,8"w,FT
(C B)C Box,6"w

this is what I have so far, and it is not working properly

public lr6 As Long
public Lr11 As Long
Sub StructureID() 
    Set rng2 = Range("A1").CurrentRegion
    lr6 = rng2.Cells(Rows.Count, "F").End(3).Row
    lr11 = rng2.Cells(Rows.Count, "O").End(3).Row
    For i = lr6 To 2 Step -1
        If rng2.Cells(i, 6) Like "*P1*" Then
           lr11.Offset(0, 4).Value = Left(lr11.Value, 3)
        End If
    Next i
End Sub

 

[SanjayGMusafir]

Why you want to go through VBA when you can do same with a simple formula and helper column without much effort?

 

[zack8576]

Why you want to go through VBA when you can do same with a simple formula and helper column without much effort?

Because there are many files that need to be modified, and each file has a different number of rows that contains "P1", and these can be any row in the file.

 

[Fluff]

How about

Sub zack()
   With Range("K2:K" & Range("F" & Rows.Count).End(xlUp).Row)
      .Value = Evaluate(Replace("If(" & .Offset(, -5).Address & "=""P1"",""(""&left(" & .Offset(, 4).Address & ",3)&"") ""&@,if(@="""","""",@))", "@", .Address))
   End With
End Sub

 

[zack8576]

How about

Sub zack()
   With Range("K2:K" & Range("F" & Rows.Count).End(xlUp).Row)
      .Value = Evaluate(Replace("If(" & .Offset(, -5).Address & "=""P1"",""(""&left(" & .Offset(, 4).Address & ",3)&"") ""&@,if(@="""","""",@))", "@", .Address))
   End With
End Sub

thanks ! this works like a charm, AND this approach is simpler and much more elegant !

one more question, there are 2 exceptions, if the value in column O is "E Box" or "E Base", no offset is needed. How do I add this exception rule in there ?
If I figure this out myself somehow, I will post it here so others can see

 

[Fluff]

How about

Sub zack()
   With Range("K2:K" & Range("F" & Rows.Count).End(xlUp).Row)
      .Value = Evaluate(Replace(Replace("If(" & .Offset(, -5).Address & "=""P1"",""(""&if((#=""E Box"")+(#=""E Base""),#,left(#,3))&"") ""&@,if(@="""","""",@))", "@", .Address), "#", .Offset(, 4).Address))
   End With
End Sub

 

[zack8576]

How about

Sub zack()
   With Range("K2:K" & Range("F" & Rows.Count).End(xlUp).Row)
      .Value = Evaluate(Replace(Replace("If(" & .Offset(, -5).Address & "=""P1"",""(""&if((#=""E Box"")+(#=""E Base""),#,left(#,3))&"") ""&@,if(@="""","""",@))", "@", .Address), "#", .Offset(, 4).Address))
   End With
End Sub

this works beautifully, thanks a ton !

 

[Fluff]

You're welcome & thanks for the feedback.