Find and replace

[Kartyk]

Hi All,

I would like to assign values to an array spread across two columns in a worksheet.

So values in column A to one array and Column B to another.

Kindly help.

Cheers
K

[mana]

Sorry, I delete my comments #2 & #3.

[mana]

^^

[mana]

Why? "Find and replace"


Option Explicit


Sub test()
Dim aryA, aryB

With Cells(1).CurrentRegion
aryA = WorksheetFunction.Transpose(.Columns(1))
aryB = WorksheetFunction.Transpose(.Columns(2))
End With

End Sub

[Kartyk]

Thanks Mana. Scenario is as below

* One sheet has the data, other has a find list and a replace list.
* Code am looking for should find the ones on find list on the data sheet and replace them with replace list.


Cheers
K

[mana]

please show me the sample data
(befoe and after running macro)

[mana]

Option Explicit

Sub test()
Dim dic As Object
Dim c As Range

Set dic = CreateObject("scripting.dictionary")

With Worksheets("Sheet1")
For Each c In .Range("a1", .Range("a" & .Rows.Count).End(xlUp))
dic(c.Value) = c.Offset(, 1).Value
Next
End With

With Worksheets("Sheet2")
For Each c In .Range("a1", .Range("a" & .Rows.Count).End(xlUp))
c.Offset(, 1).Value = dic(c.Value) 'c.Value = dic(c.Value)
Next
End With

End Sub

[Kartyk]

Thanks Mana. Although it works, I noticed that when the cell value is not present in the find list, then value on the data sheet gets deleted. I have attached a file where you can see what I intend to do.

Also, this is just a dummy file and original file will have multiple data and column might not be in the same position everytime. It is dynamic

Cheers

[mana]

If dic.exists(c.Value) Then
c.Offset(, 1).Value = dic(c.Value) 'c.Value = dic(c.Value)
End If

[Kartyk]

Thanks a lot. It works.