fadee2
Active Member
- Joined
- Nov 7, 2020
- Messages
- 404
- Office Version
- 2021
- Platform
- Windows
Hy all,
I am trying to edit/save my current record via VBA. The data is being updated from text boxes and listview into worksheet. The data is being transferred in two steps.
Step 1: updates the details of the client present in textboxes.
Step 2: updates details of items present in listview.
I using findvalue as string to find existing record number and then looping through for editing the client details, and 2nd loop to update item details.
J loops for all records in relevant to specific record number and i loops for number of items present in the listview. Loop J updates from rec_no to party and while staying in itself moves to loop i, which then updates remaining columns. loop i runs for three times (listview count) in a single row and moves out to loop j.
I want the loop to run in a way that starting from rec_no the row is update on the whole.
how can I do that? any help is appreciated.
I am trying to edit/save my current record via VBA. The data is being updated from text boxes and listview into worksheet. The data is being transferred in two steps.
Step 1: updates the details of the client present in textboxes.
Step 2: updates details of items present in listview.
I using findvalue as string to find existing record number and then looping through for editing the client details, and 2nd loop to update item details.
VBA Code:
Dim LastRow As Long
LastRow = Sheets("dc").Range("b2").SpecialCells(xlCellTypeLastCell).Row
'<<<<<<<<< FIND EXISTING RECORD
Dim findvalue As Range
Dim DataSH As Worksheet
Set DataSH = Sheets("dc")
Set findvalue = DataSH.Range(Cells(2, 2), Cells(LastRow, 2)). _
Find(What:=Me.txt_chl_no.Text, LookIn:=xlValues, LookAt:=xlWhole)
S = lst_dc.ListItems.Count
If Not findvalue Is Nothing Then
For j = findvalue.Row To findvalue.Row + Int(S) - 1
Cells(j, 1) = lbl_recno.Caption
Cells(j, 2) = txt_chl_no.Text
Cells(j, 3) = Format(txt_date.Text, shortdate)
Cells(j, 4) = txt_PO.Text
Cells(j, 5) = txt_PO_date.Text
Cells(j, 6) = cmb_party.Value
For i = 1 To lst_dc.ListItems.Count
Cells(j, 7) = lst_dc.ListItems(i).SubItems(1)
Cells(j, 8) = lst_dc.ListItems(i).SubItems(2)
Cells(j, 9) = lst_dc.ListItems(i).SubItems(3)
Cells(j, 10) = lst_dc.ListItems(i).SubItems(4)
Cells(j, 11) = lst_dc.ListItems(i).SubItems(5)
Next i
Next j
Else
'<<<<<<<<< END FIND EXISTING RECORD
Rec_no | DC_no | DC_date | PO_No | PO_Date | Party | Item_serial | Item_Name | Qty | Unit | Rate | Remarks |
5 | 5 | 05-Nov-20 | AAAAAAAAAAA | 1 | FD | 1 | AS | 222 | |||
5 | 5 | 05-Nov-20 | AAAAAAAAAAA | 2 | ASD | 2 | ASD | 12 | |||
5 | 5 | 05-Nov-20 | AAAAAAAAAAA | 3 | AAAA | 1 | ASD | 1 |
J loops for all records in relevant to specific record number and i loops for number of items present in the listview. Loop J updates from rec_no to party and while staying in itself moves to loop i, which then updates remaining columns. loop i runs for three times (listview count) in a single row and moves out to loop j.
I want the loop to run in a way that starting from rec_no the row is update on the whole.
how can I do that? any help is appreciated.
Last edited by a moderator: