Snasui Excel Forum

Code: Select all

Private Sub CommandButton1_Click()
Dim rAll As Range, r As Range
    With Sheets("sheet1")
        Set rAll = .Range("b3", .Range("b" & Rows.Count).End(xlUp))
        For Each r In rAll
            If CStr(r) = ComboBox1.Text Then
                r.Offset(0, 1) = TextBox1.Text
                r.Offset(0, 2) = TextBox2.Text
                r.Offset(0, 3) = TextBox3.Text
                r.Offset(0, 4) = TextBox4.Text
               
                Exit For
            End If
        Next r
    End With
End Sub

Code: Select all

Private Sub CommandButton1_Click()
    Dim rAll As Range, r As Range
    Set ws = Worksheets("sheet1")
    With ws
        Set rAll = .Range("b3", .Range("b" & Rows.Count).End(xlUp))
        For Each r In rAll
            If CStr(r) = ComboBox1.Text Then
                r.Offset(0, 1) = TextBox1.Text
                r.Offset(0, 2) = TextBox2.Text
                r.Offset(0, 3) = TextBox3.Text
                r.Offset(0, 4) = TextBox4.Text
                ComboBox1.Text = ""
                 TextBox1.Text = ""
                 TextBox2.Text = ""
                 TextBox3.Text = ""
                 TextBox4.Text = ""
              ElseIf CStr(r) <> ComboBox1.Text Then
                Irow = Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row + 1
                ws.Cells(Rows, 1).Value = ComboBox1.Text
                 ws.Cells(Rows, 2).Value = TextBox1.Text
                 ws.Cells(Rows, 3).Value = TextBox2.Text
                 ws.Cells(Rows, 4).Value = TextBox3.Text
                 ws.Cells(Rows, 5).Value = TextBox4.Text
                 ComboBox1.Text = ""
                 TextBox1.Text = ""
                 TextBox2.Text = ""
                 TextBox3.Text = ""
                 TextBox4.Text = ""
                Exit For
            End If
        Next r
    End With
End Sub

Code: Select all

Private Sub CommandButton1_Click()
    Dim rAll As Range, r As Range
    Set ws = Worksheets("sheet1")
    With ws
        Set rAll = .Range("b3", .Range("b" & Rows.Count).End(xlUp))
        For Each r In rAll
            If CStr(r) = ComboBox1.Text Then
                r.Offset(0, 1) = TextBox1.Text
                r.Offset(0, 2) = TextBox2.Text
                r.Offset(0, 3) = TextBox3.Text
                r.Offset(0, 4) = TextBox4.Text
                ComboBox1.Text = ""
                 TextBox1.Text = ""
                 TextBox2.Text = ""
                 TextBox3.Text = ""
                 TextBox4.Text = ""
              ElseIf CStr(r) <> ComboBox1.Text Then
                'Irow = Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row + 1
                Irow = Sheets("sheet1").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
                'ws.Cells(Rows, 1).Value = ComboBox1.Text
                ws.Cells(Irow, 2).Value = ComboBox1.Text
                 ws.Cells(Irow, 3).Value = TextBox1.Text
                 ws.Cells(Irow, 4).Value = TextBox2.Text
                 ws.Cells(Irow, 5).Value = TextBox3.Text
                 ws.Cells(Irow, 6).Value = TextBox4.Text
                 ComboBox1.Text = ""
                 TextBox1.Text = ""
                 TextBox2.Text = ""
                 TextBox3.Text = ""
                 TextBox4.Text = ""
                Exit For
            End If
        Next r
    End With
End Sub

Code: Select all

Private Sub ComboBox1_Change()
    On Error Resume Next
    Set myRange = Worksheets("sheet1").Range("num")
    With Application.WorksheetFunction
'        ComboBox1.Value = .VLookup(CLng(ComboBox1.Text), myRange, 1, False)
        TextBox1.Text = .VLookup(CLng(ComboBox1.Text), Range("data"), 2, False)
        TextBox2.Text = .VLookup(CLng(ComboBox1.Text), Range("data"), 3, False)
        TextBox3.Text = .Text(.VLookup(CLng(ComboBox1.Text), Range("data"), 4, False), "dd/mm/yyyy")
        TextBox4.Text = .VLookup(CLng(ComboBox1.Text), Range("data"), 5, False)
    End With
End Sub


Private Sub CommandButton1_Click()
    Dim rAll As Range, r As Range
    Dim iRow As Long
    Set ws = Worksheets("sheet1")
    With ws
        Set rAll = .Range("b3", .Range("b" & Rows.Count).End(xlUp))
        If Application.CountIf(rAll, ComboBox1.Text) > 0 Then
            iRow = Application.Match(CLng(ComboBox1.Text), .Range("b:b"), 0)
        Else
            iRow = .Range(rAll.Rows.Count).Row + 1
        End If
        ws.Cells(iRow, 3).Value = TextBox1.Text
        ws.Cells(iRow, 4).Value = TextBox2.Text
        ws.Cells(iRow, 5).Value = Application.Text(TextBox3.Text, "dd/mm/yyyy")
        ws.Cells(iRow, 6).Value = TextBox4.Text
        ws.Cells(iRow, 2).Value = ComboBox1.Text
        ComboBox1.Text = ""
        TextBox1.Text = ""
        TextBox2.Text = ""
        TextBox3.Text = ""
        TextBox4.Text = ""
    End With
End Sub

Code: Select all

Private Sub CommandButton1_Click()
    Dim rAll As Range, r As Range
    Dim iRow As Long
    Set ws = Worksheets("sheet1")
    With ws
        Set rAll = .Range("b3", .Range("b" & Rows.Count).End(xlUp))
        If Application.CountIf(rAll, ComboBox1.Text) > 0 Then
            iRow = Application.Match(CLng(ComboBox1.Text), .Range("b:b"), 0)
        Else
            iRow = .Range(rAll.Rows.Count).Row + 1
        End If
        ws.Cells(iRow, 3).Value = TextBox1.Text
        ws.Cells(iRow, 4).Value = TextBox2.Text
        ws.Cells(iRow, 5).Value = Application.Text(TextBox3.Text, "dd/mm/yyyy")
        ws.Cells(iRow, 6).Value = TextBox4.Text
        ws.Cells(iRow, 2).Value = ComboBox1.Text
        ComboBox1.Text = ""
        TextBox1.Text = ""
        TextBox2.Text = ""
        TextBox3.Text = ""
        TextBox4.Text = ""
    End With
End Sub

Code: Select all

iRow = rAll(rAll.Rows.Count).Row + 1