Type mismatch error

X

xiaoying

New Member
Joined
Dec 6, 2019
Messages
26
Office Version
  1. 365
Platform
  1. Windows
I am trying to automate an engagement letter using the cell values from multiple columns and rows. When I used the for loop, I ran with type mismatch which I do not know how to debug. Below are my vba codes:

Code: 
Option Explicit

Public Sub WordFindAndReplaceSave()
    Dim ws As Worksheet, msWord As Object
    Dim currentRow As Long
    Dim rowCount As Long
    Dim lastRow As Long
    Dim filename As String
    Dim Path1 As String
   
    Path1 = "C:\Users\xlim\Desktop\Edited Letters"
    Set ws = ActiveSheet
    Set msWord = CreateObject("Word.Application")
    lastRow = ws.Cells(Rows.Count, 1).End(xlUp).row
   
    For currentRow = 2 To lastRow
    filename = ws.Cells("A", "currentRow + 1").Value
    With msWord
        .Visible = True
        .Documents.Open "C:\Users\xlim\Desktop\LOR - STC.docx"
        .Activate

        With .ActiveDocument.Content.Find
            .ClearFormatting
            .Replacement.ClearFormatting

            .Text = "KOOLOOK PTE. LTD."
            .Replacement.Text = ws.Cells("A", "currentRow + 1")

            .Forward = True
            .Wrap = 1               'wdFindContinue (WdFindWrap Enumeration)
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False

            .Execute Replace:=2     'wdReplaceAll (WdReplace Enumeration)
        End With
       
        With .ActiveDocument.Content.Find
            .ClearFormatting
            .Replacement.ClearFormatting

            .Text = "31 October 2017"
            .Replacement.Text = ws.Cells("B", "currentRow + 1")

            .Forward = True
            .Wrap = 1               'wdFindContinue (WdFindWrap Enumeration)
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False

            .Execute Replace:=2     'wdReplaceAll (WdReplace Enumeration)
        End With
       
        With .ActiveDocument.Content.Find
            .ClearFormatting
            .Replacement.ClearFormatting

            .Text = "30 April 2018"
            .Replacement.Text = ws.Cells("C", "currentRow + 1")

            .Forward = True
            .Wrap = 1               'wdFindContinue (WdFindWrap Enumeration)
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False

            .Execute Replace:=2     'wdReplaceAll (WdReplace Enumeration)
        End With
       
        With .ActiveDocument.Content.Find
            .ClearFormatting
            .Replacement.ClearFormatting

            .Text = "Delvyn Wong Wern Liat"
            .Replacement.Text = ws.Cells("D", "currentRow + 1")

            .Forward = True
            .Wrap = 1               'wdFindContinue (WdFindWrap Enumeration)
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False

            .Execute Replace:=2     'wdReplaceAll (WdReplace Enumeration)
        End With
       
        With .ActiveDocument.Content.Find
            .ClearFormatting
            .Replacement.ClearFormatting

            .Text = "Director"
            .Replacement.Text = ws.Cells("E", "currentRow + 1")

            .Forward = True
            .Wrap = 1               'wdFindContinue (WdFindWrap Enumeration)
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False

            .Execute Replace:=2     'wdReplaceAll (WdReplace Enumeration)
        End With
        msWord.ActiveDocument.SaveAs filename:=Path1 & "/" & "LOR - " & filename & ".docx"
        rowCount = rowCount + 1
    End With
    Next currentRow
End Sub
 
Last edited by a moderator:

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
I edited my codes and the error changed to the image I have attached.

Code: 
Option Explicit

Public Sub WordFindAndReplaceSave()
    Dim ws As Worksheet, msWord As Object
    Dim currentRow As Long
    Dim rowCount As Long
    Dim lastRow As Long
    Dim filename As String
    Dim Path1 As String
   
    Path1 = "C:\Users\xlim\Desktop\Edited Letters"
    Set ws = ActiveSheet
    Set msWord = CreateObject("Word.Application")
    lastRow = ws.Cells(Rows.Count, 1).End(xlUp).row
   
    For currentRow = 2 To lastRow
    filename = ws.Range("A", "currentRow + 1").Value
    With msWord
        .Visible = True
        .Documents.Open "C:\Users\xlim\Desktop\LOR - STC.docx"
        .Activate

        With .ActiveDocument.Content.Find
            .ClearFormatting
            .Replacement.ClearFormatting

            .Text = "KOOLOOK PTE. LTD."
            .Replacement.Text = ws.Cells("A", "currentRow + 1")

            .Forward = True
            .Wrap = 1               'wdFindContinue (WdFindWrap Enumeration)
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False

            .Execute Replace:=2     'wdReplaceAll (WdReplace Enumeration)
        End With
       
        With .ActiveDocument.Content.Find
            .ClearFormatting
            .Replacement.ClearFormatting

            .Text = "31 October 2017"
            .Replacement.Text = ws.Cells("B", "currentRow + 1")

            .Forward = True
            .Wrap = 1               'wdFindContinue (WdFindWrap Enumeration)
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False

            .Execute Replace:=2     'wdReplaceAll (WdReplace Enumeration)
        End With
       
        With .ActiveDocument.Content.Find
            .ClearFormatting
            .Replacement.ClearFormatting

            .Text = "30 April 2018"
            .Replacement.Text = ws.Cells("C", "currentRow + 1")

            .Forward = True
            .Wrap = 1               'wdFindContinue (WdFindWrap Enumeration)
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False

            .Execute Replace:=2     'wdReplaceAll (WdReplace Enumeration)
        End With
       
        With .ActiveDocument.Content.Find
            .ClearFormatting
            .Replacement.ClearFormatting

            .Text = "Delvyn Wong Wern Liat"
            .Replacement.Text = ws.Cells("D", "currentRow + 1")

            .Forward = True
            .Wrap = 1               'wdFindContinue (WdFindWrap Enumeration)
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False

            .Execute Replace:=2     'wdReplaceAll (WdReplace Enumeration)
        End With
       
        With .ActiveDocument.Content.Find
            .ClearFormatting
            .Replacement.ClearFormatting

            .Text = "Director"
            .Replacement.Text = ws.Cells("E", "currentRow + 1")

            .Forward = True
            .Wrap = 1               'wdFindContinue (WdFindWrap Enumeration)
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False

            .Execute Replace:=2     'wdReplaceAll (WdReplace Enumeration)
        End With
        msWord.ActiveDocument.SaveAs filename:=Path1 & "/" & "LOR - " & filename & ".docx"
        rowCount = rowCount + 1
    End With
    Next currentRow
End Sub
 

Attachments

  • Capture.PNG
    Capture.PNG
    76.2 KB · Views: 10
Last edited by a moderator:
Upvote 0
It's hard to find errors in unformatted code, when you post vba it is preferable to click on the </> icon and paste your code into the pop-up. This retains the original formatting.

It is always helpful to to tell us which line causes the error so we don't have to search all of the code trying to find it.

The first error that I found is in this line
VBA Code: 
filename = ws.Cells("A", "currentRow + 1").Value
which should be
Code: 
filename = ws.Cells("A", currentRow + 1).Value
The double quotes are resulting in an incorrect data type and an invalid row reference.

edit:- This reply is based in your first post, I have not looked at your 'edited' code.
 
Upvote 0
Hi jasonb75, sorry for the inconvenience and thanks for replying :) I managed to debug it and the automation runs now. There is just a final small problem that I am facing right now. After running the automation with two rows of cell values, it resulted in three word documents. Two of which it returned the cell values and one of it that I do not need. Do you now how to deal with this? Thank you so much.
VBA Code: 
Option Explicit

Public Sub WordFindAndReplaceSave()
    Dim ws As Worksheet, msWord As Object
    Dim currentRow As Long
    Dim rowCount As Long
    Dim lastRow As Long
    Dim filename As String
    Dim Path1 As String
    
    Path1 = "C:\Users\xlim\Desktop\Edited Letters"
    Set ws = ActiveSheet
    Set msWord = CreateObject("Word.Application")
    lastRow = ws.Cells(Rows.Count, 1).End(xlUp).row
    
    For currentRow = 1 To lastRow
    filename = ws.Range("A" & currentRow + 1).Value
    With msWord
        .Visible = True
        .Documents.Open "C:\Users\xlim\Desktop\LOR - STC.docx"
        .Activate

        With .ActiveDocument.Content.Find
            .ClearFormatting
            .Replacement.ClearFormatting

            .Text = "KOOLOOK PTE. LTD."
            .Replacement.Text = ws.Range("A" & currentRow + 1).Value

            .Forward = True
            .Wrap = 1               'wdFindContinue (WdFindWrap Enumeration)
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False

            .Execute Replace:=2     'wdReplaceAll (WdReplace Enumeration)
        End With
        
        With .ActiveDocument.Content.Find
            .ClearFormatting
            .Replacement.ClearFormatting

            .Text = "31 October 2017"
            .Replacement.Text = ws.Range("B" & currentRow + 1).Value

            .Forward = True
            .Wrap = 1               'wdFindContinue (WdFindWrap Enumeration)
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False

            .Execute Replace:=2     'wdReplaceAll (WdReplace Enumeration)
        End With
        
        With .ActiveDocument.Content.Find
            .ClearFormatting
            .Replacement.ClearFormatting

            .Text = "30 April 2018"
            .Replacement.Text = ws.Range("C" & currentRow + 1).Value

            .Forward = True
            .Wrap = 1               'wdFindContinue (WdFindWrap Enumeration)
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False

            .Execute Replace:=2     'wdReplaceAll (WdReplace Enumeration)
        End With
        
        With .ActiveDocument.Content.Find
            .ClearFormatting
            .Replacement.ClearFormatting

            .Text = "Delvyn Wong Wern Liat"
            .Replacement.Text = ws.Range("D" & currentRow + 1).Value

            .Forward = True
            .Wrap = 1               'wdFindContinue (WdFindWrap Enumeration)
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False

            .Execute Replace:=2     'wdReplaceAll (WdReplace Enumeration)
        End With
        
        With .ActiveDocument.Content.Find
            .ClearFormatting
            .Replacement.ClearFormatting

            .Text = "Director"
            .Replacement.Text = ws.Range("E" & currentRow + 1).Value

            .Forward = True
            .Wrap = 1               'wdFindContinue (WdFindWrap Enumeration)
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False

            .Execute Replace:=2     'wdReplaceAll (WdReplace Enumeration)
        End With
        msWord.ActiveDocument.SaveAs filename:=Path1 & "/" & "LOR - " & filename & ".docx"
        rowCount = rowCount + 1
    End With
    Next currentRow
End Sub
jasonb75 said:
It's hard to find errors in unformatted code, when you post vba it is preferable to click on the </> icon and paste your code into the pop-up. This retains the original formatting.

It is always helpful to to tell us which line causes the error so we don't have to search all of the code trying to find it.

The first error that I found is in this line
VBA Code: 
filename = ws.Cells("A", "currentRow + 1").Value
which should be
Code: 
filename = ws.Cells("A", currentRow + 1).Value
The double quotes are resulting in an incorrect data type and an invalid row reference.

edit:- This reply is based in your first post, I have not looked at your 'edited' code.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Excel VBA search word document and create hyperlinks too certain text
Replies
0
Views
500
R0R30
R
D
  • Solved
Excel VBA - modify a macro to hard code file and ranges
Replies
3
Views
516
DanteAmor
DanteAmor
N
  • Question Question
Code taking too long to end (5 minutes) how to speed up?
Replies
1
Views
569
johnnyL
J
N
  • Solved
Why can't I delete files using kill method?
Replies
1
Views
517
Domenic
D
C
  • Question Question
Adding a hyperlink to an automated email
Replies
1
Views
623
Micron
M

Forum statistics

Threads
1,224,828
Messages
6,181,210
Members
453,023
Latest member
alabaz

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top