type mismatch probably due to array being missinterpreted

Question

I get a type mismatch out of this code, if anyone can help that would be greatly appreciated. It probably has to do with where i used the split command.. i am trying to basically grab A2 and B2 then A3 and B3 and so on... to put it in another program (Microstation)

Sub Main()

Dim Find_text() As Variant
Dim Replace_text() As Variant
Dim objExcelApp As Object
Dim wb As Object


     Set objExcelApp = CreateObject("Excel.Application")
     Set wb = objExcelApp.Workbooks.Open("My path")

     Dim ws As Object
     Set ws = wb.Sheets(1)


Find_text = Split(ws.Range("A2:A628"))
Replace_text = Split(ws.Range("B2:B628"))


CadInputQueue.SendKeyin "MDL KEYIN FINDREPLACETEXT,CHNGTXT CHANGE DIALOGTEXT"

For i = 0 To UBound(Find_text)

        CadInputQueue.SendKeyin "FIND DIALOG SEARCHSTRING " & Find_text(i)

        CadInputQueue.SendKeyin "FIND DIALOG REPLACESTRING " & Replace_text(i)

        CadInputQueue.SendKeyin "CHANGE TEXT ALLFILTERED"

Next

End Sub

Answer 1

The problem is with these two lines:

Find_text = Split(ws.Range("A2:A628"))
Replace_text = Split(ws.Range("B2:B628"))

Neither ws.Range("A2:A628").Value nor ws.Range("B2:B628").Value are strings -- so you can't split them. You can, however, directly assign them to arrays.

Make the following changes:

Dim Find_text As Variant '() not required
Dim Replace_text As Variant

...

Find_text = ws.Range("A2:A628").Value
Replace_text = ws.Range("B2:B628").Value

...

For i = 1 To UBound(Find_text)

        CadInputQueue.SendKeyin "FIND DIALOG SEARCHSTRING " & Find_text(i,1)

        CadInputQueue.SendKeyin "FIND DIALOG REPLACESTRING " & Replace_text(i,1)

        CadInputQueue.SendKeyin "CHANGE TEXT ALLFILTERED"

Next

When you assign the value of a multi-cell rectangular range to a variant, it becomes a 2-dimenisonal 1-based array. This is why the loop starts with 1 now and why Find_text(i) had to be replaced by Find_text(i,1) (and similarly for Replace_text).