Trying to populate an ActiveX Textbox in Word with the last cell value of an Excel Sheet

Question

I am auto incrementing Column A in an excel spreadsheet with this;

=IF(B3="","",A2+1)

I do this so that when data is added to Column B the ID number in Column A increments by 1.

At the same time, I have a Word doc that I added a Command Button to. The idea is that the user will press the command button, it will populate columns B through E with data from the Word Doc, which will cause A to increment, then I want to take the value of A and put it into an ActiveX Textbox back on the word doc. I have everything working except for the transfer of the incremented ID # in column A back to the Word doc. Here is what I have so far. The first function finds the last row for me in order to populate the data from the word doc to the excel spreadsheet. It works fine for that purpose, but using it the opposite way doesn't work. The second function is something I use to make sure the required fields have actual data in them so I am not trying to write blank fields to the spreadsheet.

Function GetLastRow(ByVal col As String) As Long
   With Worksheets("Sheet1")
      GetLastRow = .Range(col & .Rows.Count).End(xlUp).Row + 1
   End With
End Function

Function RequiredField(strQuestion, strMessage)
    If strQuestion.Text = "" Then
        Do
            sInFld = InputBox(strMessage)
        Loop While sInFld = ""
            strQuestion.Text = sInFld
    End If
    'Required Field Prompt Function w/ input box

End Function

Private Sub RequestClaimNumber_Click()

    Dim wkbk As Workbook
    Dim doc As Object
    Dim app As Object
    Dim sdocname As String
    Dim LastRow As Long

    Set app = GetObject(, "Word.Application")
    sdocname = "J:\Correspondence\CAST Database\Word Docs\General Loss Form.docm"
    Set doc = ThisDocument
    Set wkbk = Workbooks.Open("J:\Accident Reports\98 Claims Register.xlsx")

    With wkbk.Sheets("Sheet1")
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    End With
    Debug.Print LastRow

    RequiredField PolicyName, "Please insert a name for the Policy Holder in the 'Policy Holder Name' field."
    RequiredField InsuredName, "Please insert a name for the Contractor in the 'Insured Name' field."
    RequiredField DOL, "Please enter a Date of Loss for this incident."

    With wkbk.Sheets("Sheet1")
        rw = GetLastRow("B")
        .Range("B" & rw) = ThisDocument.ReportDate
        rw = GetLastRow("C")
        .Range("C" & rw) = ThisDocument.DOL
        rw = GetLastRow("D")
        .Range("D" & rw) = ThisDocument.PolicyName
        rw = GetLastRow("E")
        .Range("E" & rw) = ThisDocument.InsuredName
        With doc
            doc.FormFields("ClaimNumber").Result = .Range(LastRow).Value
        End With
    End With
    wkbk.Save
    wkbk.Close
    Set wkbk = Nothing

End Sub

The problem seems to be with this:

LastRow = .Range("A" & .Rows.Count).End(xlUp).Row

When I debug that line it returns with the number 30687. I am assuming this is supposed to be the cell that it is returning on the spreadsheet. I also get a

"Subscript Out of Range"

error, and it highlights this row;

doc.FormFields("ClaimNumber").Result = .Range(LastRow).Value

My assumption is that it is not finding the actual last used cell in column A. It is going to the very bottom of A and not finding anything. To this end I have tried several methods of trying to find the last cell, but they all give me the same subscript out of range error, and they all return the value of 30687 when I debug them.

If there is a different way of doing this that I don't know of I am open to suggestions. Otherwise I just need to figure out where my problem is with my existing code so I can get this working.

Answer 1

I figured it out. Here is the final code that works.

Private Sub RequestClaimNumber_Click()

    Dim wkbk As Workbook
    Dim lastColumn As Long

    RequiredField PolicyName, "Please insert a name for the Policy Holder in the 'Policy Holder Name' field."
    RequiredField InsuredName, "Please insert a name for the Contractor in the 'Insured Name' field."
    RequiredField DOL, "Please enter a Date of Loss for this incident."

    Set wkbk = Workbooks.Open("J:\Accident Reports\98 Claims Register.xlsx")

    With wkbk.Sheets("Sheet1")
        lastColumn = .Range("B" & .Rows.Count).End(xlUp).Row
    End With

    With wkbk.Sheets("Sheet1")
        rw = GetLastRow("B")
        .Range("B" & rw) = ThisDocument.ReportDate
        rw = GetLastRow("C")
        .Range("C" & rw) = ThisDocument.DOL
        rw = GetLastRow("D")
        .Range("D" & rw) = ThisDocument.PolicyName
        rw = GetLastRow("E")
        .Range("E" & rw) = ThisDocument.InsuredName
        rw = GetLastRow("F")
        .Range("F" & rw) = ThisDocument.UserID
        ClaimNumber.Text = .Range("A" & lastColumn + 1).Text
    End With
    wkbk.Save
    wkbk.Close
    Set wkbk = Nothing

End Sub

Answer 2

You are not supplying a column, just the row, so you need to say .Range("X" & LastRow).Value or .cells(lastrow,24)