editing existing record via VBA loop

fadee2

Active Member
Joined
Nov 7, 2020
Messages
367
Office Version
  1. 2019
Platform
  1. 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.

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_noDC_noDC_datePO_NoPO_DatePartyItem_serialItem_NameQtyUnitRateRemarks
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:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

fadee2

Active Member
Joined
Nov 7, 2020
Messages
367
Office Version
  1. 2019
Platform
  1. Windows
Found a solution to the problem. Looks like nested loops were not required after all.
First had to dump text box values in the listview and then moved all list values to the worksheet, one at a time.
Hence, existing record was updated.

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)         'find the value in the worksheet

s = lst_dc.ListItems.Count               ' count listview items
    
    If Not findvalue Is Nothing Then      
        For j = findvalue.Row To findvalue.Row + Int(s) - 1
            For i = 1 To lst_dc.ListItems.Count
                Cells(j, 1) = lbl_recno.Caption
                Cells(j, 2) = lst_dc.ListItems(i).SubItems(1)
                Cells(j, 6) = lst_dc.ListItems(i).SubItems(3)
                Cells(j, 4) = lst_dc.ListItems(i).SubItems(4)
                Cells(j, 5) = lst_dc.ListItems(i).SubItems(5)
                Cells(j, 7) = lst_dc.ListItems(i).SubItems(7)
                Cells(j, 8) = lst_dc.ListItems(i).SubItems(8)
                Cells(j, 9) = lst_dc.ListItems(i).SubItems(9)
                Cells(j, 10) = lst_dc.ListItems(i).SubItems(10)
                Cells(j, 11) = lst_dc.ListItems(i).SubItems(11)
                j = j + 1
            Next i
        Next j
    Else
'<<<<<<<<< END FIND EXISTING RECORD

cheers!!!
 
Solution

Forum statistics

Threads
1,136,449
Messages
5,675,907
Members
419,591
Latest member
mersanko

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
Top