0 votes
in VBA by Beginner (21 points)
        Sheets("217").Range("$G$2:$G" & LastRow).Select
        Selection.Replace What:="", Replacement:=Cells(Active.row, 6).Value, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

I am trying to find and replace the currently selected cell value with that of another cell on the same current row.

I have added the code that I thought would work but it hasn't. Anyone got any ideas on how I could go about doing this?

 

2 Answers

+1 vote
by Skilled (279 points)
edited by
 
Best answer

Suggest you avoid using "Select". 

Public Sub ReplaceAll()
Dim lngLastRow As Long

    With Sheets(1)
        lngLastRow = .Range("G" & Rows.Count).End(xlUp).Row
        .Range("G2:G" & lngLastRow).Replace what:=ActiveCell.Value, replacement:=.Range("F" & ActiveCell.Row).Value
    End With
    
End Sub

HTH. 

0 votes
by Beginner (21 points)
                               
        Sheets("217").Range("$G$2:$G" & LastRow).Select
        Selection.Replace What:="", Replacement:=Range("F" & (ActiveCell.row)).Value, LookAt:=xlPart, 
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

Never mind answered my own question, used coding above.

Welcome to wellsr Q&A
Ask any questions you have about VBA and Python and our community will help answer them. wellsr Q&A is the standalone question and answer platform for wellsr.com. If you have a question about one of our specific tutorials, please include a link back to the tutorial.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Looking for something else? Hire our team directly through ourVBA Help page, instead.

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...