Pushing data from userform to a specific row in a sheet

[ananthkrish]

I am new to VBA, please dont mind if my mistakes are blunder

I am trying to send the data from userform to a sheet, where there is a id in colom "A", and this data should go to the cells in that row.

i was using match and offset function,

below mentioned is the code i was trying.

please help

​

Private Sub Save_1_Click()​

'Dim i As Integer​

Sheet2.Activate​

Set i = Me.ID_Search​

Dim rng As Range​

Set rng = Sheets("sheet2").Range("A1").Offset(Application.WorksheetFunction.Match(i, Range("A:A"), 0), 2)​

 

[dmt32]

Re: Help me in pushing data from userform to a specific row in a sheet

Hi,
welcome to forum

Try this update to your code

Private Sub Save_1_Click()
    Dim RowNo As Variant
    Dim Search As String
    
    Search = Me.ID_Search.Text
    If Len(Search) = 0 Then Exit Sub
    
    With Worksheets("Sheet2")
        
        RowNo = Application.Match(Search, .Range("A:A"), 0)
        
        If Not IsError(RowNo) Then
            
'add records here
            .Cells(CLng(RowNo), 2).Value = Me.TextBox2.Text
            
        Else
'inform user
            MsgBox Search & Chr(10) & "Record Not Found", 48, "Not Found"
            
        End If
    End With
    
End Sub

Dave

 

[ananthkrish]

Re: Help me in pushing data from userform to a specific row in a sheet

Hi,

I have tried that, but not working.

my ID_Search is a Numeric, as we mentioned as .text will that be a reason for error?

 

[dmt32]

Re: Help me in pushing data from userform to a specific row in a sheet

Hi,

I have tried that, but not working.

my ID_Search is a Numeric, as we mentioned as .text will that be a reason for error?

Hi,
always worth mentioning things like that.

You need to coerce the string using one of the built in conversion functions

RowNo = Application.Match(Val(Search), .Range("A:A"), 0)

Update code shown in red & see if helps

Dave

 

[ananthkrish]

Re: Help me in pushing data from userform to a specific row in a sheet

And also my ID_Search is a combobox

 

[ananthkrish]

Re: Help me in pushing data from userform to a specific row in a sheet

Hey dave,


i changed little and it worked, thank you so much..

Private Sub Save_1_Click()
        
 Dim RowNo As Variant
 Dim Search As Integer
 
    Search = Me.ID_Search.Value
    If Len(Search) = 0 Then Exit Sub
    
    With Worksheets("Sheet2")
        
    RowNo = Application.Match(Search, .Range("A:A"), 0)
        
    If Not IsError(RowNo) Then
           
'add records here
    .Cells(CLng(RowNo), 2).Value = Me.DTPicker1.Value
    .Cells(CLng(RowNo), 3).Value = Me.DTPicker2.Value
    .Cells(CLng(RowNo), 4).Value = Me.DTPicker3.Value
    .Cells(CLng(RowNo), 5).Value = Me.Block_1.Value
    .Cells(CLng(RowNo), 6).Value = Me.Floor_1.Value
    .Cells(CLng(RowNo), 7).Value = Me.Room_1.Value
    .Cells(CLng(RowNo), 8).Value = Me.Dept_1.Value
    .Cells(CLng(RowNo), 9).Value = Me.Req_by1.Value
    .Cells(CLng(RowNo), 10).Value = Me.Book_by1.Value
    Else
'inform user
    MsgBox Search & Chr(10) & "Record Not Found", 48, "Not Found"
    End If
    End With
    
End Sub

 

[dmt32]

Re: Help me in pushing data from userform to a specific row in a sheet

Hey dave,


i changed little and it worked, thank you so much..

welcome - thanks for feedback

Dave