search, edit and save data based on two or more variables using userform and macros

dss28

dss28

Board Regular
Joined
Sep 3, 2020
Messages
165
Office Version
  1. 2007
Platform
  1. Windows
just joined the group. No experience on vba coding or excel as such but got impressed about vba coding and started learning from online tutorials.

Based on some tutorials I have been able to design a userform to my need, working well with data addition serially. however not able to search and modify the data based on two variables in column B and column G.
in my database, first row is a header,
column A - is serial number autogenerated
column B - is main data entry number (say 1, 2, 3...)
column G - is sub serial number under main data entry number (say 1,2,3,4,5,6)
there are about 10 to 15 columns with different data. my data looks like the below table:
ABCDEFG
111
212
313
414
521
631


I need help to recall whole row for data editing based on column B and column G one by one by entering B and G values in the user form:
For example:
Column B(1) , Column G(1), then edit the data as required, saving back to its place again, then recall next sub item number for editing eg.
Column B(1) , Column G(2)
Column B(1) , Column G(3)
VBA Code: 
Sub Save()
‘save data
    Dim frm As Worksheet
    Dim database As Worksheet
    Dim iRow As Long
    Dim iSerial As Long
    
    Set frm = ThisWorkbook.Sheets("Form")
    Set database = ThisWorkbook.Sheets("PODetails")
  
    If Trim(frm.Range("M1").Value) = "" Then
        iRow = database.Range("A" & Application.Rows.Count).End(xlUp).Row + 1
        If iRow = 2 Then
            iSerial = 1
        Else
            iSerial = database.Cells(iRow - 1, 1).Value + 1
        End If
    Else
        iRow = frm.Range("L1").Value
        iSerial = frm.Range("M1").Value
    End If
        Sheets("PODetails").Cells(iRow, 1).Value = iSerial
        Sheets("PODetails").Cells(iRow, 2).Value = frm.Range("I6").Value
        Sheets("PODetails").Cells(iRow, 3).Value = frm.Range("I8").Value
        Sheets("PODetails").Cells(iRow, 4).Value = frm.Range("I10").Value
        Sheets("PODetails").Cells(iRow, 5).Value = frm.Range("I12").Value
        Sheets("PODetails").Cells(iRow, 6).Value = frm.Range("I14").Value
        Sheets("PODetails").Cells(iRow, 7).Value = frm.Range("I16").Value
        Sheets("PODetails").Cells(iRow, 8).Value = frm.Range("I18").Value
        Sheets("PODetails").Cells(iRow, 9).Value = frm.Range("I22").Value
        Sheets("PODetails").Cells(iRow, 10).Value = frm.Range("I24").Value
        Sheets("PODetails").Cells(iRow, 11).Value = frm.Range("I26").Value
        Sheets("PODetails").Cells(iRow, 12).Value = frm.Range("I28").Value
        Sheets("PODetails").Cells(iRow, 16).Value = frm.Range("I32").Value
        Sheets("PODetails").Cells(iRow, 17).Value = frm.Range("I30").Value
        Sheets("PODetails").Cells(iRow, 20).Value = Application.UserName
        
        Sheets("PODetails").Cells(iRow, 21).Value = [Text(Now(), "DD-MM-YYYY HH:MM")]
    End
    frm.Range("L1").Value = ""
    frm.Range("M1").Value = ""
        
MsgBox "data saved succesfully", vbOKOnly + vbCritical, "Save"
    With Sheets("Form")
         .Range("I8").Interior.ColorIndex = 40
         .Range("I8").Value = ""
         .Range("I10").Interior.ColorIndex = 40
         .Range("I10").Value = ""
         .Range("I12").Interior.ColorIndex = 40
         .Range("I12").Value = ""
         .Range("I14").Interior.ColorIndex = 40
         .Range("I14").Value = ""
         .Range("I16").Interior.ColorIndex = 40
         .Range("I16").Value = ""
         .Range("I18").Interior.ColorIndex = 40
         .Range("I18").Value = ""
         .Range("I22").Interior.ColorIndex = 40
         .Range("I22").Value = ""
         .Range("I24").Interior.ColorIndex = 40
         .Range("I24").Value = ""
         .Range("I26").Interior.ColorIndex = 40
         .Range("I26").Value = ""
         .Range("I28").Interior.ColorIndex = 40
         .Range("I28").Value = ""
         .Range("I30").Interior.ColorIndex = 40
         .Range("I30").Value = ""
         .Range("I32").Interior.ColorIndex = 1
        .Range("I32").Value = ""
    'End
'MsgBox "data saved succesfully", vbOKOnly + vbCritical, "Save"
End With
End Sub

Sub AddItem()
‘add another item in same PO
    Dim frm As Worksheet
   'Dim database As Worksheet
    Dim iRow As Long
    Dim iSerial As Long
    Set frm = ThisWorkbook.Sheets("Form")
    'Set database = ThisWorkbook.Sheets("PODetails")
    If Trim(frm.Range("M1").Value) = "" Then
        iRow = Sheets("PODetails").Range("B" & Application.Rows.Count).End(xlUp).Row + 1
        If iRow = 2 Then
            iSerial = 1
        Else
            iSerial = Sheets("PODetails").Cells(iRow - 1, 1).Value + 1
        End If
    Else
        iRow = frm.Range("L1").Value
        iSerial = frm.Range("M1").Value
    End If
    With Sheets("PODetails")
        Sheets("PODetails").Cells(iRow, 1).Value = iSerial
        Sheets("PODetails").Cells(iRow, 2).Value = frm.Range("I6").Value
        Sheets("PODetails").Cells(iRow, 3).Value = frm.Range("I8").Value
        Sheets("PODetails").Cells(iRow, 4).Value = frm.Range("I10").Value
        Sheets("PODetails").Cells(iRow, 5).Value = frm.Range("I12").Value
        Sheets("PODetails").Cells(iRow, 6).Value = frm.Range("I14").Value
        Sheets("PODetails").Cells(iRow, 7).Value = frm.Range("I16").Value
        Sheets("PODetails").Cells(iRow, 8).Value = frm.Range("I18").Value
        Sheets("PODetails").Cells(iRow, 9).Value = frm.Range("I22").Value
        Sheets("PODetails").Cells(iRow, 10).Value = frm.Range("I24").Value
        Sheets("PODetails").Cells(iRow, 11).Value = frm.Range("I26").Value
        Sheets("PODetails").Cells(iRow, 12).Value = frm.Range("I28").Value
        Sheets("PODetails").Cells(iRow, 16).Value = frm.Range("I32").Value
        Sheets("PODetails").Cells(iRow, 17).Value = frm.Range("I30").Value
        Sheets("PODetails").Cells(iRow, 20).Value = Application.UserName
         Sheets("PODetails").Cells(iRow, 21).Value = [Text(Now(), "DD-MM-YYYY HH:MM")]
    End With
    frm.Range("L1").Value = ""
    frm.Range("M1").Value = ""
    
    With Sheets("Form")
         .Range("I16").Interior.ColorIndex = 40
         .Range("I16").Value = ""
         .Range("I18").Interior.ColorIndex = 40
         .Range("I18").Value = ""
         .Range("I22").Interior.ColorIndex = 40
         .Range("I22").Value = ""
         .Range("I24").Interior.ColorIndex = 40
         .Range("I24").Value = ""
         .Range("I26").Interior.ColorIndex = 40
         .Range("I26").Value = ""
         .Range("I28").Interior.ColorIndex = 40
         .Range("I28").Value = ""
         .Range("I30").Interior.ColorIndex = 40
         .Range("I30").Value = ""
         .Range("I32").Interior.ColorIndex = 1
        .Range("I32").Value = ""
    End With
End Sub

Sub Modify()
‘earch, edit and save any entry in PO
    Dim iRow As Long
    Dim iSerial As Long
    iSerial = Application.InputBox("Please enter PO Number to make modification.", "Modify", , , , , , 1)
    iSerial = Application.InputBox("Please enter Sr.No. to make modification.", "Modify", , , , , , 1)
    On Error Resume Next
    iRow = Application.WorksheetFunction.IfError _
    (Application.WorksheetFunction.Match(iSerial, Sheets("PODetails").Range("B:B"), 0), 0)
    iRow = Application.WorksheetFunction.IfError _
    (Application.WorksheetFunction.Match(iSerial, Sheets("PODetails").Range("G:G"), 0), 0)
    
    On Error GoTo 0
    If iRow = 0 Then
        MsgBox "No record found.", vbOKOnly + vbCritical, "No Record"
        Exit Sub
    End If
    Sheets("Form").Range("L1").Value = iRow
    Sheets("Form").Range("M1").Value = iSerial
    Sheets("Form").Range("I6").Value = Sheets("PODetails").Cells(iRow, 2).Value
    Sheets("Form").Range("I8").Value = Sheets("PODetails").Cells(iRow, 3).Value
    Sheets("Form").Range("I10").Value = Sheets("PODetails").Cells(iRow, 4).Value
    Sheets("Form").Range("I12").Value = Sheets("PODetails").Cells(iRow, 5).Value
    Sheets("Form").Range("I14").Value = Sheets("PODetails").Cells(iRow, 6).Value
    Sheets("Form").Range("I16").Value = Sheets("PODetails").Cells(iRow, 7).Value
    Sheets("Form").Range("I18").Value = Sheets("PODetails").Cells(iRow, 8).Value
    Sheets("Form").Range("I22").Value = Sheets("PODetails").Cells(iRow, 9).Value
    Sheets("Form").Range("I24").Value = Sheets("PODetails").Cells(iRow, 10).Value
    Sheets("Form").Range("I26").Value = Sheets("PODetails").Cells(iRow, 11).Value
    Sheets("Form").Range("I28").Value = Sheets("PODetails").Cells(iRow, 12).Value
    Sheets("Form").Range("I32").Value = Sheets("PODetails").Cells(iRow, 16).Value
    Sheets("Form").Range("I30").Value = Sheets("PODetails").Cells(iRow, 17).Value
  
End Sub


Sub DeleteRecord()
    Dim iRow As Long
    Dim iSerial As Long
    iSerial = Application.InputBox("Please enter P.No. to delete the recor.", "Delete", , , , , , 1)
    On Error Resume Next
    iRow = Application.WorksheetFunction.IfError _
    (Application.WorksheetFunction.Match(iSerial, Sheets("PODetails").Range("B:B"), 0), 0)
    On Error GoTo 0
    If iRow = 0 Then
        MsgBox "No record found.", vbOKOnly + vbCritical, "No Record"
        Exit Sub
        
    End If
    Sheets("PODetails").Cells(iRow, 2).EntireRow.Delete shift:=xlUp
    Sheets("PODetails").Cells(iRow, 2).EntireRow.Delete shift:=xlUp
    Sheets("PODetails").Cells(iRow, 2).EntireRow.Delete shift:=xlUp
    Sheets("PODetails").Cells(iRow, 2).EntireRow.Delete shift:=xlUp
    Sheets("PODetails").Cells(iRow, 2).EntireRow.Delete shift:=xlUp
    Sheets("PODetails").Cells(iRow, 2).EntireRow.Delete shift:=xlUp
End Sub
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
also in continuation of above post, I want to delete the record of one main entry which may contain upto 6 sub entries, solicite your help in vba coding so that it should delete all record (6 rows) pertaining to one main entry. i have added the command 6 times - is it a right approach?
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
I keep getting error message "member already exists in an object module from which this project module derives
Replies
4
Views
478
Skyybot
Skyybot
E
  • Solved
Delete Rows using input box to multiple sheets
Replies
4
Views
253
Elle_mostlysunny
E
D
  • Question
Adding an image to a database
Replies
0
Views
313
doudy11
D
J
  • Question
Update Row on another sheet based on Cell Value
Replies
2
Views
768
jeremiah_j2k
J
A
  • Question
Autofilter code based on username
Replies
1
Views
173
aabrazil1
A

Forum statistics

Threads
1,215,059
Messages
6,122,913
Members
449,093
Latest member
dbomb1414

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top