excel vba replace range cell

Question

This code is already working, but i want "plain" & "Letter" to be dynamic. Like pointing to specific range cell. I've tried it but it wasn't successful.

Basically sReplace = Replace(sReplace, Range"A1", "Range"B1:B10")

    Private Sub CommandButton1_Click()

    Dim sFind As String
    Dim sReplace As String
    Dim iFileNum As Integer
    Dim FilePath As String
    Dim newFileName As String

    FilePath = "C:\Users\new\Plain.prn"

    iFileNum = FreeFile
    Open FilePath For Input As iFileNum

    Do Until EOF(iFileNum)
    Line Input #iFileNum, sFind
    sReplace = sReplace & sFind & vbCrLf
    Loop
    Close iFileNum

    sReplace = Replace(sReplace, "plain", "Letter")

    'SaveAs txtfile
    newFileName = Replace(FilePath, ".", "_edit.")
    iFileNum = FreeFile
    Open newFileName For Output As iFileNum

    Print #iFileNum, sReplace
    Close iFileNum
    End Sub

What do you want to accomplish? Replace a Range of cells with a specific value from another cell? — drec4s
– drec4s, Commented Mar 20, 2018 at 11:43

Vityata · Accepted Answer · 2018-03-20 11:42:59Z

1

This is how to make the values pointing to a given range in a given worksheet:

With Worksheets("NameOfTheWorksheet")
    sReplace = Replace(sReplace, .Range("A1"), .Range("A2"))
End With

answered Mar 20, 2018 at 11:42

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

drec4s · Accepted Answer · 2018-03-20 11:51:25Z

0

This might do what you are asking:

Replaces the values in Range B1:B10 where it matches sReplace, with the value from Range A1

Range("B1:B10").Replace What:=sReplace, Replacement:=Range("A1"), LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

answered Mar 20, 2018 at 11:51

drec4s

8,1178 gold badges35 silver badges56 bronze badges

Collectives™ on Stack Overflow

excel vba replace range cell

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related