When my computer crashed, I lost the code that I used to replace all values in a row with the value from the first column. For instance, I have a sheet with Column A being a persons name. Columns B:AB each contain a formula, and I want to replace a value in that formula with the value from Column A. I was able to recollect the code below, but what happens is that it replace every value I want to replace with the value from the first cell of the first column. I know this is a step in the right direction, but I recall developing this code even farther, so maybe I am not going about it the right way. Hopefully someone can be of some assistance, which would be much appreciated and save me mountains of time.
Replace text in rows with value from column
-
-
-
Re: Replace text in rows with value from column
Upload a workbook.
When you click reply there will be a new button labeled go advanced, click on that button then there will be a paperclip icon in the toolbar, click on that icon and follow the instructions.
-
Re: Replace text in rows with value from column
It is easier to copy the top line in the last tab down, then replace the one text string with the first name in the column. Thanks for your help!
-
Re: Replace text in rows with value from column
Try this. :cool:
Code
Display MoreSub FindValueAndReplace() Dim rngData As Range, r As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual With Worksheets("BALANCES").Range("A1").CurrentRegion Set rngData = .Offset(1, 1).Resize(.Rows.Count - 1, .Columns.Count - 1) For Each r In rngData.Rows r.Replace What:="=*!", Replacement:="=" & Cells(r.Row, 1).Value & "!" Next r End With Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub
-
Re: Replace text in rows with value from column
Works wonderfully! Thanks
Quote from skywriter;768876Try this. :cool:
Code
Display MoreSub FindValueAndReplace() Dim rngData As Range, r As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual With Worksheets("BALANCES").Range("A1").CurrentRegion Set rngData = .Offset(1, 1).Resize(.Rows.Count - 1, .Columns.Count - 1) For Each r In rngData.Rows r.Replace What:="=*!", Replacement:="=" & Cells(r.Row, 1).Value & "!" Next r End With Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub
-
-
Re: Replace text in rows with value from column
My pleasure, thanks for the feedback. :cool:
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!