(VBA) Dynamically edit or update my database

[kelly mort]

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:

For i = 1 to (Something)

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

Thanks

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]

 

[kelly mort]

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.

 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.

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.

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

 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]

Bump.

I Still need help with this please