Transferring information to a specific row within table using VBA (and errors)

Question

The function of this program is a machine information archive. The user inputs information about the machine in a specific sheet, and then that information gets transferred to the archive based on what machine was specified.

Machines are sorted by business unit. Once the user specifies business unit, it will look within the table, at a specific column, for the machine ID. It's supposed to paste all of the information inputted to the row for that machine.

Afterwards, it's supposed to clear the user interface so that it's clear for the next time a user chooses to log a machine.

I'm having trouble consolidating everything properly, and I'm getting runtime error 13: Type mismatch on the Set function for the variable MachFinder.

I don't even know if I'm slightly on the right track here. I'm apprehensive about whether or not I need to transpose all of this information or just copy and paste it? I don't know enough about VBA to decipher what I'm doing wrong here.

Sub Info_Update()

Dim axle As ListObject
Dim CatTab As ListObject
Dim IBTab As ListObject

Dim tbl, transfer, machineinfo

Dim MachFinder As Range

Dim axws As Worksheet
Dim catws As Worksheet
Dim ibws As Worksheet
Dim wbk As Workbook

Set wbk = ThisWorkbook

Set axws = wbk.Worksheets("AXLEWS")
Set catws = wbk.Worksheets("CAT")
Set ibws = wbk.Worksheets("IB")

Set AxleTab = axws.ListObjects("axle_tab")
Set CatTab = catws.ListObjects("cat_tab")
Set IBTab = ibws.ListObjects("ib_tab")

Dim unit_input As String
Dim machine_input As String

unit_input = wbk.Worksheets("Interface").Range("A2").Value
machine_input = wbk.Worksheets("Interface").Range("B2").Value

If unit_input = "AXLE" Then
    Set tbl = AxleTab
ElseIf unit_input = "CAT" Then
    Set tbl = CatTab
ElseIf unit_input = "IB" Then
    Set tbl = IBTab
End If

Set MachFinder = tbl.DataBodyRange.Columns("Machine").Find(machine_input, LookAt:=xlWhole)

transfer = tbl.ListRows(MachFinder.Row - tbl.HeaderRowRange.Row)
machineinfo = Array("B2:D2", "F2")

machineinfo.Copy
transfer.PasteSpecial Paste:=x1PasteValues

wbk.Worksheets("Interface").Range("A2:F2").ClearContents

End Sub

What am I missing, and can I improve this program in any way?

`machineinfo` isn't a `Range`. You can't call `.Copy` on it. — BigBen, Aug 09 '23 at 18:24
`transfer` isn't a `Range` either, so you can't call `.PasteSpecial` on it. — BigBen, Aug 09 '23 at 18:25
`Set MachFinder = tbl.ListColumns("Machine").DataBodyRange`... — BigBen, Aug 09 '23 at 18:26
@BigBen Ah, that's fairly simple, thank you. I can't access the rest of your comment after "DataBodyRange" for some reason, did you put anything after that or was that the whole line? — heiavieh, Aug 09 '23 at 18:37
Silly me. I guess I'm a bit confused on where I'm supposed to go from here... Do I need to make another variable that tells the program to actually do the searching for the `machine_input`? Would you recommend transposing the information, or do I need to set `machineinfo` and `transfer` as a Range? — heiavieh, Aug 09 '23 at 18:46

taller_ExcelHome · Accepted Answer · 2023-08-09T22:25:51.003

It seems you want to copy some cells from the Interface sheet into a table. Please share the layout of the destination table if my code does not meet your needs.

Sub demo()
    Dim tbl As ListObject
    Set axws = wbk.Worksheets("AXLEWS")
    Set catws = wbk.Worksheets("CAT")
    Set ibws = wbk.Worksheets("IB")
    Set AxleTab = axws.ListObjects("axle_tab")
    Set CatTab = catws.ListObjects("cat_tab")
    Set IBTab = ibws.ListObjects("ib_tab")
    Dim unit_input As String
    Dim machine_input As String
    unit_input = wbk.Worksheets("Interface").Range("A2").Value
    machine_input = wbk.Worksheets("Interface").Range("B2").Value
    If unit_input = "AXLE" Then
        Set tbl = AxleTab
    ElseIf unit_input = "CAT" Then
        Set tbl = CatTab
    ElseIf unit_input = "IB" Then
        Set tbl = IBTab
    End If

    Dim MachFinder As Range
    Set MachFinder = tbl.ListColumns("Machine").DataBodyRange.Find(machine_input, lookat:=xlWhole)
    If Not MachFinder Is Nothing Then
        ' copy "B2:D2", "F2" on Interface sheet to destination table column B:E
        With wbk.Worksheets("Interface")
            MachFinder.Offset(0, 1).Resize(1, 3).Value = .Range("B2:D2").Value
            MachFinder.Offset(0, 4).Value = .Range("F2").Value
            .Range("A2:F2").ClearContents
        End With
    End If
End Sub

Transferring information to a specific row within table using VBA (and errors)

1 Answers1