Excel VBA move and remove a value from a range

[pretzman21]

I would like to use an excel VBA to in this case remove an "a" from a group of cells, and move one of them to a new cell. In the formula below, it has added the "a" to K48 and removed the a's from L48:N48. I would like to to search a range, for me it is L2:N100. The "a" needs to be removed from any grouping and placed in that corresponding K cell. It needs to be able to do this any amount of times. Can this formula be adjusted to do this, or a new formula perhaps? Any help is appreciated. Thank you.

Sub MoveAValue()

Range("L48:N48").Select
Selection.Replace What:="a", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Range("K48").Select
ActiveCell.FormulaR1C1 = "a"
Range("K49").Select
End Sub

 

[pretzman21]

I was able to update my formula to what you see below, however, this only removes the "a" if it exists, which is about 50% of what I need. I still would like to have the value in Row K to update to have an "a" if it detects an "a" in the equivalent L row.


AbsentValue = "a"
For X = 1 To Range("L" & Rows.Count).End(xlUp).Row
With Range("L" & X)
If Left(.Value, 1) = AbsentValue Then .Value = Replace(.Value, EqualsSign, "'=", , 1)
End With
Next

Columns("L:N").Select
Selection.Replace What:="a", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

Range("A1").Select

 

[pretzman21]

Anyone have any thoughts on this situation?