Data Modification

[Syed Haider Ali]

Hi Dears

I have following codes to Enter New data into Sheet.

Private Sub CommandButton1_Click()
Dim irow As Long
Dim ws As Worksheet
Dim rng As Range
Dim res As Variant

Set ws = Worksheets("CIssue")

With ws
Set rng = .Range(.Range("a2"), .Range("a2").End(xlDown))
End With

res = Application.Match(CLng(CDate(Me.TextBox1.Value)), rng, 0)

If IsError(res) Then

irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

ws.Cells(irow, 1).Value = Me.TextBox1.Value
ws.Cells(irow, 2).Value = Me.TextBox2.Value
ws.Cells(irow, 3).Value = Me.TextBox3.Value
Me.TextBox1 = ""
Me.TextBox2 = ""
Me.TextBox3 = ""
Me.TextBox1.SetFocus
Else
MsgBox "Data Already Exist", vbCritical
TextBox2 = Sheet8.Range("a1").Offset(res, 1).Value
TextBox3 = Sheet8.Range("a1").Offset(res, 2).Value
End If
Me.TextBox1.SetFocus

End Sub

I would like to ask the codes for modification of data. Suppose when i
put the required code in textbox1.

1. It search the data
2. if found then retrive the data in Textbox2 and textbox3
3. Put the replaced data in the existing row.

Thanks,

Syed Haider Ali

 

[Tom Ogilvy]

Private Sub CommandButton1_Click()
Dim irow As Long
Dim ws As Worksheet
Dim rng As Range
Dim res As Variant

Set ws = Worksheets("CIssue")

With ws
Set rng = .Range(.Range("a2"), .Range("a2").End(xlDown))
End With

res = Application.Match(CLng(CDate(Me.TextBox1.Value)), rng, 0)

If IsError(res) Then

irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

ws.Cells(irow, 1).Value = Me.TextBox1.Value
ws.Cells(irow, 2).Value = Me.TextBox2.Value
ws.Cells(irow, 3).Value = Me.TextBox3.Value
Me.TextBox1 = ""
Me.TextBox2 = ""
Me.TextBox3 = ""
Me.TextBox1.SetFocus
Else
ans = MsgBox( "Data Already Exist, Update?", vbCritical+vbYesNo)
if ans = vbYes then
set rng1 = rng(res)
rng1.offset(0,1).Value = TextBox2
rng1.offset(0,2).Value = Textbox3
End If
End If
Me.TextBox1.SetFocus

End Sub

I would like to ask the codes for modification of data. Suppose when i
put the required code in textbox1.


--
Regards,
Tom Ogilvy


"Syed Haider Ali"