Forum Discussion
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 format. For eg: 305969 FM233971 Mary 12/22/23 7:25 AM. I would like to remove the FM233971 Mary 12/22/23 7:25 AM part of this data for all cells and retaining just the (eg:)305969 part of it. I recorded the macro (code below) and I have onlty used the record mode since I dont code. I recorded this macro only for the first cell of that row in that column and then press tab to exit that cell (I have also tried enter to exit). Then stop recording the macro. Then I try running the macro for the next row cell in that column and it doesnt do a thing except just move the active cell selection to the tab /or wherever I exited while recording the macro. Unless I paste the contents of the row I want edited on the row I created the macro on, this doesnt work.
Sub Macro1()
'
' Macro1 Macro
'
'
Range("A5").Select
ActiveCell.FormulaR1C1 = "305969"
Range("B5").Select
End Sub
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.
6 Replies
i am facing a issue regarding excel macros it is showing me greyish because of which i cant enable my macros even if my file is .xlsm, i have to import a file.
i am facing a issue regarding excel macros it is showing me greyish because of which i cant enable my macros even if my file is .xlsm, i have to import a file.
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.
- 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.
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 SubYou 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.
