CODEWITHSUNDEEP
vbaexcel
Timothy Williams
Timothy Williams

Reputation: 217

Iterate through For Loop with Replace function

I have a data set that looks like this:

Pin

  1. 2Ard-7
  2. 2Ard-99
  3. 1RPI-35
  4. 1RPI-34
  5. 1RPI-39
  6. 1RPI-38

I am trying to loop through this and replace parts of each cell with a different designation. Here is the code I have so far:

Dim CompPin() As String
Dim AutoPin() As String
Dim Item As Variant

CompPin() = Split("1ARD 2ARD 1RPI 2RPI")
AutoPin() = Split("PB1A PC1A PF1A PF2B")

For Each Item In CompPin
    Worksheets("Demo").Columns("D").Replace What:=CompPin(Item), Replacement:=AutoPin(Item), LookAt:=xlPart
Next

When I run this code, I get a type mismatch. Any insight would be helpful.

Upvotes: 0

Views: 96

Answers (2)

user3598756
user3598756

Reputation: 29421

an alternative could be a "double-split":

Sub main()
    Dim CompAutoPin() As String
    Dim Item As Variant

    CompAutoPin() = Split("1ARD-PB1A 2ARD-PC1A 1RPI-PF1A 2RPI-PF2B")    
    For Each Item In CompAutoPin
        Worksheets("Demo").Columns("D").Replace What:=Split(Item, "-")(0), Replacement:=Split(Item, "-")(1), LookAt:=xlPart
    Next Item
End Sub

Upvotes: 0

Mister 832
Mister 832

Reputation: 1221

Try this

CompPin() = Split("1ARD 2ARD 1RPI 2RPI")
AutoPin() = Split("PB1A PC1A PF1A PF2B")

For n = 0 To UBound(CompPin)
    Worksheets("Demo").Columns("D").Replace What:=CompPin(n), Replacement:=AutoPin(n), LookAt:=xlPart
Next

Upvotes: 2

Related Questions