Hi All, I'm a relative beginner at VBA and have been grappling with this issue for a while now. I've tried various methods and feel like i'm finally getting somewhere, so any help would be greatly appreciated.
I've been using the following code (Posted by TheSilkCode on another site I wont name), to find a part number from a table and replace it with the part number and description.
The Code works fine where the description is a single line however when multiple lines of text are used I get Run-time error '13': Type mismatch.
As the description is taken from another spreadsheet I don't control, I can't change the format of the source to be on a single line. So I'm looking for help to make the code work with multiline text.
Example-
If PN001 is replaced with
PN001 ABC, DEF
the code works fine..
If PN001 is replaced with
PN001:
ABC
DEF
Then the Run-Time error'13' occurs
Code:
Public Sub demoCode_v2()
Dim tableRange As Range
Dim myArray() As Variant
Dim rowCounter As Long
Dim targetRange As Range
'Create an Array out of the Table's Data
Set tableRange = ThisWorkbook.Sheets("EPM Find Replace List").ListObjects("Table1").DataBodyRange
myArray = tableRange
'Select target range
Set targetRange = Application.InputBox("Select target range:", Type:=8)
'Loop through each item in lookup table
For rowCounter = LBound(myArray, 1) To UBound(myArray, 1) 'Replace any cells in target range that contain whats in the first column of the lookup table, with whats in the 2nd column.. targetRange.Cells.Replace What:=myArray(rowCounter, 1), Replacement:=myArray(rowCounter, 2), LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next
End Sub
Display More