MACAM MACAM INPUT DATA VBA EXCEL

MACAM MACAM INPUT DATA VBA EXCEL

1. Input data hanya 1 sheet

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets(“Barang”)
‘menemukan baris kosong pada database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
‘check untuk sebuah kode
If Trim(TextBox1.Value) = “” Then
TextBox1.SetFocus
MsgBox “Masukan Kode Barang”
Exit Sub
End If
‘copy data ke database
ws.Cells(iRow, 1).Value = TextBox1.Value
ws.Cells(iRow, 2).Value = TextBox2.Value
ws.Cells(iRow, 3).Value = TextBox3.Value
ws.Cells(iRow, 4).Value = TextBox4.Value
‘clear data
TextBox1.Value = “”
TextBox2.Value = “”
TextBox3.Value = “”
TextBox4.Value = “”
TextBox1.SetFocus
END SUB


2. input data berayarat manual sesuai nama sheet
yaitu tambahkan kode pada tombo pesan
Private Sub CommandButton1_Click()
Set ws = Worksheets("BUAH")
Set ws2 = Worksheets("KUE")
Set ws3 = Worksheets("PERMEN")
Set ws4 = Worksheets("NASI")

'menemukan baris kosong pada database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
iRow2 = ws2.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
iRow3 = ws3.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
iRow4 = ws4.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check untuk sebuah kode
If Trim(Me.TextBox1.Value) = "" Then
Me.TextBox1.SetFocus
MsgBox "Masukan Kode Pembeli"
Exit Sub
End If
'copy data ke database
If ComboBox1.Value = "BUAH" Then
With ws
ws.Cells(iRow, 1).Value = Me.TextBox1.Value
ws.Cells(iRow, 2).Value = Me.TextBox2.Value
End With
ElseIf ComboBox1.Value = "KUE" Then
With ws2
ws2.Cells(iRow2, 1).Value = Me.TextBox1.Value
ws2.Cells(iRow2, 2).Value = Me.TextBox2.Value
End With
ElseIf ComboBox1.Value = "PERMEN" Then
With ws3
ws3.Cells(iRow3, 1).Value = Me.TextBox1.Value
ws3.Cells(iRow3, 2).Value = Me.TextBox2.Value
End With
ElseIf ComboBox1.Value = "NASI" Then
With ws
ws4.Cells(iRow4, 1).Value = Me.TextBox1.Value
ws4.Cells(iRow4, 2).Value = Me.TextBox2.Value
End With

End If

'clear data
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox1.SetFocus
MsgBox "DATA TELAH DI SIMPAN"
End Sub

3.A. Input data berdasarkan kolom pencarian 1
Dim Kode
Dim CellTujuan As Range
Kode = TextBox1.Value
Set CellTujuan = Range("C3:ZZ3").Find(What:=Kode,
LookIn:=xlValues, LookAt:=xlWhole)
If Not CellTujuan Is Nothing Then
TextBox2 = Cells(4, CellTujuan.Column)
End If
iRow = ws.Cells(Rows.Count, CellTujuan.Column) _
.End(xlUp).Offset(1, CellTujuan.Column).Row
ws.Cells(iRow, CellTujuan.Column).Value = Textbox2.Value

3.B.Input data berdasarkan kolom pencarian 2
Dim x As Range
For Each x In Range("A1:Z100")
If x.Value = CStr(TextBox1.Value) Then
TextBox2.Value = x.Offset(1, 0).Value
End If
Next
iRow = ws.Cells(Rows.Count, x.Column) _
.End(xlUp).Offset(1, x.Column).Row
ws.Cells(iRow, x.Column).Value = Textbox2.Value

4. Input data KEDALAM SHEET, NAMA SHEET SESUAI TEXTBOX

Dim iRow As Long
Dim ws As Worksheet
nama = TextBulan.value
Set ws = Worksheets(nama)
‘menemukan baris kosong pada database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
‘check untuk sebuah kode
If Trim(TextBox1.Value) = “” Then
TextBox1.SetFocus
MsgBox “Masukan Kode Barang”
Exit Sub
End If
‘copy data ke database
ws.Cells(iRow, 1).Value = TextBox1.Value
ws.Cells(iRow, 2).Value = TextBox2.Value
ws.Cells(iRow, 3).Value = TextBox3.Value
ws.Cells(iRow, 4).Value = TextBox4.Value
‘clear data
TextBox1.Value = “”
TextBox2.Value = “”
TextBox3.Value = “”
TextBox4.Value = “”
TextBox1.SetFocus
END SUB