Forum Discussion

Riva80's avatar
Riva80
Copper Contributor
Dec 29, 2023
Solved

Excel Macro not working on other rows

Hello, This is my first time recording a Macro. This is what I am trying to do: I have a list of data in a column that I want to modify for each row of that column. The data all have a similar form...
excel
Macros and VBA
  • OliverScheurich's avatar
    OliverScheurich
    Dec 29, 2023

    Riva80 

    The macro only refers to cells in row 5 (A5 and B5) and therefore can't change the output in other rows.

     

    =LEFT(A5,SEARCH(" ",A5)-1)

    An alternative could be this formula.

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Dec 29, 2023

Riva80 

The macro only refers to cells in row 5 (A5 and B5) and therefore can't change the output in other rows.

 

=LEFT(A5,SEARCH(" ",A5)-1)

An alternative could be this formula.

  • Riva80's avatar
    Riva80
    Copper Contributor
    Dec 29, 2023
    Thank you, I will note this formula. I am trying to learn to Macro, so if you could point me in the way the Macro could be edited, that would be super helpful to me.
    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor
      Dec 29, 2023

      Riva80 

      Sub Extract()

    Dim lastrowA As Long, f As String

    f = "=LEFT(A5,SEARCH("" "",A5)-1)"
       'Find a lastrow of column A
       lastrowA = Range("A" & Rows.Count).End(xlUp).Row
       Range("B5:B" & lastrowA).Formula = f

End Sub

      You can run this macro which returns the result shown in the screenshot. One can't see that the formula was entered by a macro this time.

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Dec 29, 2023

      Riva80 

      The following macro will remove the text from all cells in the column of the active cell:

      Sub RemovePart()
    Const Text2Remove = " FM233971 Mary 12/22/23 7:25 AM"
    ActiveCell.EntireColumn.Replace What:=Text2Remove, Replacement:="", LookAt:=xlPart
End Sub

Resources