How to replace values using Arrays & Ranges?

Question

I can replace values by mentioning them one by one.

I want to replace (oldarray) with (newarray) where both of them are derived from ranges. i.e. oldarray = ("a2:a5") and newarray = ("b2:b5") instead of writing them one by one.

and also I need to replace each old value with adjacent cell value
i.e. a2 replaced by b2, and a3 replaced by b3.

is that possible?

Sub ReplaceValues()
    
    Dim NewValues() As String
    Dim NewValues() As String
    
    OldValues = Split("BMV,MERCE", ",")
    NewValues = Split("Jack,Sally", ",")
    
    
    For i = 0 To UBound(OldValues)
        With sheets("destination").Columns("Z:Z")
        .Replace What:=OldValues(i), Replacement:=NewValues(i), LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True
        End With
        Next
        
End Sub

@41686d6564 I am trying to replace values cell by cell not to (copy and paste) — Ismael
– Ismael, Commented Dec 11, 2020 at 23:14

Tim Williams · Accepted Answer · 2020-12-11 23:18:04Z

1

Something like this should work:

Sub ReplaceValues()
    
    Dim OldValues, NewValues, ws As Worksheet
    
    Set ws = Thisworkbook.worksheets("Config") 'or whichever sheet...
    OldValues = ws.Range("A2:A5").Value 'this gives a 2d array
    NewValues = ws.Range("B2:B5").Value 'this too
    
    
    For i = 1 To UBound(OldValues, 1)
        With sheets("destination").Columns("Z:Z")
        .Replace What:=OldValues(i, 1), Replacement:=NewValues(i, 1), _
                 LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True
        End With
        Next
        
End Sub

answered Dec 11, 2020 at 23:18

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

1 Comment

Ismael Over a year ago

Thanks for your time and effort, please check @VBasic2008 Answer.

VBasic2008 · Accepted Answer · 2020-12-12 02:44:48Z

Search and Replace (`Application.Match`)

If a value in the Destination column (Z) is found in the Search column (A), it will be replaced with the value in the same row of the Replace column (B).
This search (Application.Match) is not case-sensitive i.e. A = a.
Adjust the values in the constants section.
Only run replaceValues; the rest is being called by it.

The Code

Option Explicit

Sub replaceValues()
    
    ' Define constants.
    
    ' Source
    Const srcName As String = "Sheet1"
    Const sFirst As String = "A2"
    Const rFirst As String = "B2"
    ' Destination
    Const dstName As String = "Sheet2"
    Const dFirst As String = "Z2"
    ' Workbook
    Dim wb As Workbook
    Set wb = ThisWorkbook
    
    ' Write from worksheets to arrays.
    
    Dim ws As Worksheet   ' Each Worksheet
    Dim rng As Range      ' Each Column Range
    ' Source
    Dim sData As Variant  ' Search Data Array
    Dim rData As Variant  ' Replace Data Array
    Dim ColOffset As Long ' Search and Replace Column Offset
    Set ws = wb.Worksheets(srcName)
    Set rng = getColumnRange(getCellRange(ws, sFirst))
    ColOffset = getCellRange(ws, rFirst).Column - rng.Column
    sData = getColumn(rng)
    rData = getColumn(rng.Offset(, ColOffset))
    ' Destination
    Dim dData As Variant  ' Destination Array
    Set ws = wb.Worksheets(dstName)
    Set rng = getColumnRange(getCellRange(ws, dFirst))
    dData = getColumn(rng)
    
    ' Search and replace (in arrays).
    
    Dim mData As Variant ' Match Data Array
    mData = Application.Match(dData, sData, 0)
    Dim cMatch As Variant
    Dim i As Long
    For i = 1 To UBound(dData, 1) ' or 'UBound(mData, 1)'
        cMatch = mData(i, 1)
        If IsNumeric(cMatch) Then
            dData(i, 1) = rData(cMatch, 1)
        End If
    Next i
    
    ' Write from Destination Array to Destination Range.
    
    rng.Value = dData

End Sub

Function getCellRange( _
    ws As Worksheet, _
    ByVal CellAddress As String) _
As Range
    On Error Resume Next
    Set getCellRange = ws.Range(CellAddress)
    On Error GoTo 0
End Function

Function getColumnRange( _
    FirstCell As Range) _
As Range
    If Not FirstCell Is Nothing Then
        With FirstCell
            Dim rng As Range
            Set rng = .Resize(.Worksheet.Rows.Count - .Row + 1)
            Set rng = rng.Find("*", , xlFormulas, , , xlPrevious)
            If Not rng Is Nothing Then
                Set getColumnRange = .Resize(rng.Row - .Row + 1)
            End If
        End With
    End If
End Function

Function getColumn( _
    rng As Range) _
As Variant
    If Not rng Is Nothing Then
        If InStr(rng.Address, ":") > 0 Then
            getColumn = rng.Value
        Else
            Dim Data As Variant
            ReDim Data(1 To 1, 1 To 1)
            Data(1, 1) = rng.Value
            getColumn = Data
        End If
    End If
End Function

Variatus · Accepted Answer · 2020-12-12 01:12:15Z

0

The code below reads the data from A2:A6 into an array SrcArr and the data from B2:B6 into another array I called ModArr. Then it creates a third array (OutArr) of the same size as the source and writes data from SrcArr into it modified according to the data in ModArr. Finally, the OutArr is written to column D. This is the setup and the result.

And here is the code that did it.

Sub ReplaceArray()
    ' 138

    Dim SrcArr      As Variant              ' Source
    Dim ModArr      As Variant              ' Modifier
    Dim OutArr      As Variant              ' Output
    Dim R           As Long                 ' loop counter: rows
    
    With ActiveSheet
        SrcArr = .Range("A2:A6").Value
        ModArr = .Range("B2:B6").Value
        ReDim OutArr(1 To UBound(SrcArr), 1 To UBound(SrcArr, 2))
        
        For R = 1 To UBound(SrcArr)
            If ModArr(R, 1) = True Then
                OutArr(R, 1) = SrcArr(R, 1) * 12
            Else
                If IsEmpty(ModArr(R, 1)) Then
                    OutArr(R, 1) = "No data"
                Else
                    OutArr(R, 1) = 0
                End If
            End If
        Next R
        
        .Cells(2, "D").Resize(UBound(SrcArr), UBound(SrcArr, 2)).Value = OutArr
    End With
End Sub

answered Dec 12, 2020 at 1:12

Variatus

14.4k2 gold badges16 silver badges35 bronze badges

1 Comment

Ismael Over a year ago

Thanks for your time and effort, please check @VBasic2008 Answer.

Collectives™ on Stack Overflow

How to replace values using Arrays & Ranges?

3 Answers 3

1 Comment

Search and Replace (`Application.Match`)

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Search and Replace (Application.Match)

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related

Search and Replace (`Application.Match`)