Userform - Paste over row

[jamescooper]

With the following code I am trying to take whatever is in Textbox6 in a userform - this is what is in column A of the spreadsheet. It finds this record in sheet and copies the array of texboxes/comboboxes to the sheet.

Any ideas how I can get it to work?

Thanks.

Private Sub CommandButton1_Click()

Dim Msg As String
Dim Ans As Variant
Dim ary As Variant
Dim fn As Range

    Msg = "Do you want overwrite record with ID " & TextBox6 & "?"

    Ans = MsgBox(Msg, vbYesNo)
    
    Select Case Ans

    Case vbYes

ary = Array(TextBox6, TextBox1, TextBox9, TextBox5, TextBox13, TextBox15, TextBox13, ComboBox2, TextBox10, ComboBox1, ComboBox4, ComboBox16, TextBox21, ComboBox7, ComboBox9, ComboBox11, TextBox26, TextBox30, ComboBox8, TextBox31, ComboBox13, TextBox35, ComboBox10, TextBox32, TextBox33, TextBox34, ComboBox12, TextBox36, TextBox39, TextBox37, TextBox38, TextBox40, TextBox25, ComboBox3, ComboBox5, ComboBox6, ComboBox14, ComboBox15, 0, TextBox14, TextBox8, TextBox12, TextBox16)

With Edit
    Set fn = Sheets("Data - Operations").Range("A:A").Find(.Range(ary(0)).Value, , xlValues, xlWhole)
        If Not fn Is Nothing Then
            For i = LBound(ary) To UBound(ary)
               Sheets("Data - Operations").Cells(fn.Row, i + 1) = .Range(ary(i)).Value 'Starts in column 1 and goes accross based on array count
            Next
        End If

End With

End Sub

 

[dmt32]

Hi,
untested but see if this update to your code does what you want

Private Sub CommandButton1_Click()
    Dim Msg As String, UserID As String
    Dim ary As Variant
    Dim Ans As VbMsgBoxResult
    Dim fn As Range
    Dim wsDataOperations As Worksheet
    
    UserID = TextBox6.Text
    If Len(UserID) = 0 Then Exit Sub

    Msg = "Do you want overwrite record with ID " & UserID & "?"
    Ans = MsgBox(Msg, 36, "OverWrite Record")
     If Ans = vbNo Then Exit Sub
     
     Set wsDataOperations = ThisWorkbook.Worksheets("Data - Operations")

    ary = Array(TextBox6, TextBox1, TextBox9, TextBox5, TextBox13, _
                TextBox15, TextBox13, ComboBox2, TextBox10, ComboBox1, _
                ComboBox4, ComboBox16, TextBox21, ComboBox7, ComboBox9, _
                ComboBox11, TextBox26, TextBox30, ComboBox8, TextBox31, _
                ComboBox13, TextBox35, ComboBox10, TextBox32, TextBox33, _
                TextBox34, ComboBox12, TextBox36, TextBox39, TextBox37, _
                TextBox38, TextBox40, TextBox25, ComboBox3, ComboBox5, _
                ComboBox6, ComboBox14, ComboBox15, 0, TextBox14, TextBox8, _
                TextBox12, TextBox16)

    Set fn = wsDataOperations.Columns(1).Find(UserID, , xlValues, xlWhole)
        If Not fn Is Nothing Then
            fn.Resize(, UBound(arr)).Value = ary
            
        Else
            MsgBox "ID " & UserID & Chr(10) & "Record Not Found", 48, "Not Found"
        
        End If

End Sub

Dave

 

[jamescooper]

Thanks, I am receiving run-time error 13 on the following line;

            fn.Resize(, UBound(arr)).Value = ary

Any ideas?

Thanks.

 

[dmt32]

sorry, typo on my part

fn.Resize(, UBound(ary)).Value = ary


Dave

 

[jamescooper]

Thanks, sorted!

 

[dmt32]

Thanks, sorted!

welcome

Dave