(VBA) Dynamically edit or update my database

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,885
Office Version
  1. 2016
Platform
  1. Windows
So I used this code at the early stage to update my database. But as I move forward, I realized that there are series of pitfalls that await me.

I think I have a problem with the loop counter of my first loop and I would need some form of help.

1. I want to first count the number of filled boxes immediately after loading or fetching the data from the database.

And this is when I click on the "Edit/Update button":

a. If the currently filled boxes are more than those I recorded during the data loading process, then I would want to expand my database by adding more rows just after the string "DAILY TOTALS" under column A so that I can accommodate the new expansion coming from the form controls.

b. If the filled boxes are less than those I recorded during the data loading process, then I would want to contract or shrink my database by deleting more rows in between the date and the string "DAILY TOTALS" so that I can accommodate the new contraction/reduction coming from the form controls.

2. Once I have points/steps a and b verified and passed, I proceed with sending/updating the database. By then, the data should fit exactly at the respective locations.

3. This means that I need to adapt/adjust the for loop as:

Code:
For i = 1 to (Something)

I really need your help to fix this (Better ways are appreciated)

Thanks

Code:
Dim findvalue As Range, lr&, j&
    Dim EXP As Worksheet, c&, i&
    Set EXP = Sheets("EXPENSE")
    lr = EXP.Cells(Rows.Count, "A").End(xlUp).Row
    If lr < 4 Then lr = 4
    Set findvalue = EXP.Range("A4:A" & lr).Find(what:=CDate(exp1), LookIn:=xlValues, lookat:=xlWhole)
    findvalue = CDate(exp1)
    c = 2

    For i = 1 To 15
        For j = 0 To 1
            findvalue.Offset(i, j) = Controls("exp" & c + j).Value
        Next j
        c = c + 2
    Next i

[//code]

See sample image for worksheet below:
[ATTACH type="full"]30692[/ATTACH]
 

Attachments

  • copy_data_image.jpg
    copy_data_image.jpg
    95.3 KB · Views: 9

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,885
Office Version
  1. 2016
Platform
  1. Windows
So I managed to fix my problem to this level and I think with a small push from you guys, I should be able to go to sleep soon.

Code:
 For i = 1 To 31
        If Controls("exp" & i) = "" Then
            Count2 = i - 1
            Exit For
        End If
    Next i
The lines above load both the Count1 and Count2 variables at different events. I load the Count1 when I fetch data into the controls from the database.

Then as shown below here, I load the Count2 variable when I click the edit button.

Code:
Option Explicit
Dim Count1&, Count2&

Private Sub cmdEdit_Click()
    Dim findvalue As Range, lr&, j&
    Dim exp As Worksheet, c& ', i&
    Set exp = Sheets("EXPENSE")
    lr = exp.Cells(Rows.Count, "A").End(xlUp).Row
    If lr < 4 Then lr = 4
    Set findvalue = exp.Range("A4:A" & lr).Find(what:=CDate(exp1), LookIn:=xlValues, lookat:=xlWhole)
    Dim i&, newBoxes&, addrw&
    
    For i = 1 To 31
        If Controls("exp" & i) = "" Then
            Count2 = i - 1
            Exit For
        End If
    Next i
    
    If Count2 = Count1 Then
        newBoxes = Int(Count1 / 2)
    ElseIf Count2 < Count1 Then
        newBoxes = Int(Count1 / 2)
        addrw = (Count1 - Count2) / 2
        findvalue.Offset(1).Resize(addrw).EntireRow.Delete
    ElseIf Count2 > Count1 Then
        newBoxes = Int(Count2 / 2)
        addrw = (Count2 - Count1) / 2
        
        findvalue.Offset(1).Resize(addrw).EntireRow.Insert
    End If
    
    findvalue = CDate(exp1)
    c = 2
    For i = 1 To newBoxes
        For j = 0 To 1
            findvalue.Offset(i, j) = Controls("exp" & c + j).Value
        Next j
        c = c + 2
    Next i
    
    If Count2 > Count1 Then
        Set findvalue = exp.Cells(Rows.Count, exp.[A4].Column).End(xlUp) '.Offset(1)
    ElseIf Count2 < Count1 Then
        Set findvalue = exp.Cells(Rows.Count, exp.[A4].Column).End(xlUp).Offset(1)
    End If
    
    findvalue = "DAILY TOTALS"
    findvalue.Offset(, 1) = TotExp.Value
    
    
    For i = 1 To 31
        Controls("exp" & i) = ""
    Next i
    exp1 = Format(Date, "dd-mm-yy")
    exp2.SetFocus
    exp1.Enabled = True
    cmd_exp_add.Enabled = True
    cmdDelete.Enabled = False
    cmdEdit.Enabled = False
    Me.eBack.Enabled = False
    Me.eNext.Enabled = False
    Me.Height = 141
    Me.exp2.SetFocus
End Sub

The challenge I am facing now is that I do not know how to locate the last cell before the next date. Since I am pointing the findvalue variable to the date in the combobox, my goal is to reset the findvalue variable so that I can place the "DAILY TOTALS" there. That "DAILY TOTALS" at row 16 should have been at row 10 instead.
CODE_UPDATE_NEEDS_HELP.jpg


From my current state of mind, I think this is where the issue is coming from:

Code:
 If Count2 > Count1 Then
        Set findvalue = exp.Cells(Rows.Count, exp.[A4].Column).End(xlUp) '.Offset(1)
    ElseIf Count2 < Count1 Then
        Set findvalue = exp.Cells(Rows.Count, exp.[A4].Column).End(xlUp).Offset(1)
    End If

But I am sure you guys know better than I do and can fix this better for me.

Thanks in advance.
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,885
Office Version
  1. 2016
Platform
  1. Windows
Bump.

I Still need help with this please
 

Watch MrExcel Video

Forum statistics

Threads
1,127,420
Messages
5,624,688
Members
416,040
Latest member
patriciocabello

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