Error 1004

D

Denny57

Board Regular
Joined
Nov 23, 2015
Messages
209
Office Version
  1. 365
Platform
  1. Windows
I am receiving error 1004 when I try to run the Update Command and select "Yes" from the message box. I have compared the code to similar I use successfully in another file.

So far this error only happens for the "UPDATE" Command following a DATE SEARCH request, however I have provided the entire code as the problem might be caused by another Private Sub
DEBUG shows the error initially occurs at Cells(CurrentRow, 1).Value = DTPicker1.Value and when I exclude this line and "CONTINUE" the error moves on to the next line. This repeats as each line is excluded.

VBA Code: 
Dim CurrentRow As Long

Private Sub UserForm_Initialize()

    DTPicker1.Value = ""
    cboSchedulingType.Value = ""
    cboLocation.Value = ""
    txtStartTime.Value = "00:00"
    txtStartTime.MaxLength = 5
    txtFinishTime.Value = "00:00"
    txtFinishTime.MaxLength = 5
    cboPayRate.Value = ""
    CheckBoxPete.Value = False
    CheckBoxKirsty.Value = False
    CheckBoxJan.Value = False
    CheckBoxKelly.Value = False
    CheckBoxCarla.Value = False
    txtWorkDate.Value = ""
    txtDailyPayMonth.Value = ""
    txtDailyWorkHours.Value = ""
    txtDailyLeaveHours.Value = ""
    txtDailyNonWorkingDay.Value = ""
    txtDailyEarningsGross.Value = ""
    txtMonthlyWorkHours.Value = ""
    txtDailyEarningsGross.Value = ""
    txtMonthlyPayMonth.Value = ""
    txtMonthlyWorkHours.Value = ""
    txtMonthlyWorkEarningsGross.Value = ""
    txtMonthlyLeaveHours.Value = ""
    txtMonthlyLeaveEarningsGross.Value = ""
    txtTotalMonthlyEarningsGross.Value = ""
    txtDateSearch.Value = ""
    cboPayMonthSearch.Value = ""
    
    
    DTPicker1.SetFocus
           
End Sub
Private Sub txtStartTime_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If IsDate(txtStartTime.Value) And Len(txtStartTime.Text) = 5 Then
    Else
        MsgBox "Input Start Time as for example 09:15"
        txtStartTime.Text = ""
    End If

End Sub
Private Sub txtEndTime_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If IsDate(txtEndTime.Value) And Len(txtEndTime.Text) = 5 Then
    Else
        MsgBox "Input End Time as for example 09:15"
        txtEndTime.Text = ""
    End If

End Sub
Private Sub cmdInputRecords_Click()
Dim lastrow As Long
answer = MsgBox("Add the Record?", vbYesNo + vbQuestion, "Add Record?")
If answer = vbNo Then
Call UserForm_Initialize
DTPicker1.SetFocus
Else
lastrow = Sheets("Daily Hours Input").Range("A" & Rows.Count).End(xlUp).Row
Cells(lastrow + 1, "A").Value = DTPicker1
Cells(lastrow + 1, "B").Value = cboSchedulingType
Cells(lastrow + 1, "C").Value = cboLocation
Cells(lastrow + 1, "E").Value = txtStartTime
Cells(lastrow + 1, "F").Value = txtFinishTime
Cells(lastrow + 1, "K").Value = cboPayRate
Cells(lastrow + 1, "M").Value = CheckBoxPete
Cells(lastrow + 1, "N").Value = CheckBoxKirsty
Cells(lastrow + 1, "O").Value = CheckBoxJan
Cells(lastrow + 1, "P").Value = CheckBoxKelly
Cells(lastrow + 1, "Q").Value = CheckBoxCarla

MsgBox "Record has been added to the database", 0, "Record Added"

With ActiveSheet
    Application.Goto Reference:=.Cells(.Rows.Count, "A").End(xlUp).Offset(-20), Scroll:=True
End With
Call UserForm_Initialize
DTPicker1.SetFocus
End If
End Sub
Private Sub cboPayRate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim payrate As Double
payrate = cboPayRate.Value
cboPayRate.Value = Format(cboPayRate.Value, "Currency")
End Sub
Private Sub cmdClearForm_Click()
'Clears the User Form
Call UserForm_Initialize
End Sub
Private Sub cmdCloseForm_Click()
'Closes the User Form
    Unload Me
End Sub
Private Sub cmdDateSearch_Click()
'Used to search for records for a specific date

Dim Res As Variant
Dim lastrow
Dim myFind As String
Dim CurrentRow As Long

    Res = Application.Match(CDbl(CDate(txtDateSearch)), Sheets("Daily Hours Input").Range("A:A"), 0)
    If IsError(Res) Then
        MsgBox "Date Not Found", vbInformation, "Date Not Found"
    Call UserForm_Initialize
    txtDateSearch.SetFocus
    Exit Sub
    End If
    
lastrow = Sheets("Daily Hours Input").Range("A" & Rows.Count).End(xlUp).Row
myFind = txtDateSearch
For CurrentRow = 2 To lastrow
If Cells(CurrentRow, 1).Value = myFind Then
DTPicker1.Value = Cells(CurrentRow, 1)
cboSchedulingType.Value = Cells(CurrentRow, 2)
cboLocation.Value = Cells(CurrentRow, 3)
txtStartTime.Value = Cells(CurrentRow, 5)
txtFinishTime.Value = Cells(CurrentRow, 6)
cboPayRate.Value = Cells(CurrentRow, 11)
CheckBoxPete.Value = Cells(CurrentRow, 13)
CheckBoxKirsty.Value = Cells(CurrentRow, 14)
CheckBoxJan.Value = Cells(CurrentRow, 15)
CheckBoxKelly.Value = Cells(CurrentRow, 16)
CheckBoxCarla.Value = Cells(CurrentRow, 17)
txtWorkDate.Value = Cells(CurrentRow, 1).Value
txtDailyPayMonth.Value = Cells(CurrentRow, 4).Value
txtDailyEarningsGross.Value = Cells(CurrentRow, 12).Text
If Cells(CurrentRow, 2).Value = "Work" Then
txtDailyWorkHours.Value = Cells(CurrentRow, 10).Value
ElseIf Cells(CurrentRow, 2).Value = "Leave" Then
txtDailyLeaveHours.Value = Cells(CurrentRow, 10).Value
ElseIf Cells(CurrentRow, 2).Value = "Non-Working Day" Then
txtDailyNonWorkingDay.Value = "Yes"

End If
End If
Next CurrentRow
End Sub
Private Sub cmdUpdateRecords_Click()
'Used to update existing records
Dim CurrentRow As Long
answer = MsgBox("Update the Record?", vbYesNo + vbQuestion, "Update Record?")
If answer = vbNo Then
Call UserForm_Initialize
DTPicker1.SetFocus
Else
Cells(CurrentRow, 1).Value = DTPicker1.Value
Cells(CurrentRow, 2).Value = cboSchedulingType.Value
Cells(CurrentRow, 3).Value = cboLocation.Value
Cells(CurrentRow, 5).Value = txtStartTime.Value
Cells(CurrentRow, 6).Value = txtFinishTime.Value
Cells(CurrentRow, 11).Value = cboPayRate.Value
Cells(CurrentRow, 13).Value = CheckBoxPete.Value
Cells(CurrentRow, 14).Value = CheckBoxKirsty.Value
Cells(CurrentRow, 15).Value = CheckBoxJan.Value
Cells(CurrentRow, 16).Value = CheckBoxKelly.Value
Cells(CurrentRow, 17).Value = CheckBoxCarla.Value

MsgBox "Record has been Updated", 0, "Record Updated"
Call UserForm_Initialize
DTPicker1.SetFocus
End If
End Sub

Any help is most appreciated
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
CurrentRow has not value, imho.
Add:
VBA Code: 
Debug.Print CurrentRow
before
VBA Code: 
Cells(CurrentRow, 1).Value = DTPicker1.Value
and check that value been properly populate.
 
Upvote 0
Apologies but this did nothing that I could not already see when the error message appeared and I select DEBUG.

I have successfully completed a SearchCommand which has re- populated the required fields in the User Form. I then alter one (or more) of the fields and then wish to update the original records. As the field to be altered will vary, the code looks to updates all the fields in the Search Command eveni if the a detail has not been changed.

I am using similar code as in another file, being careful to ensure that the necessary details (sheet, Range and control names) are correct.
 
Upvote 0
Debug.Print print out into Immediate Window. You have to switch on to show it in VBA editor.
 
Upvote 0
Looks like yYour problem likely to be due to Declaring CurrentRow both at Module Level & again at Procedure Level in the same module – To use it across other procedures in the same module it should only exist once at module level.

BTW – you have yet to extend the courtesy of replying to your last post Date Issues with User Forms you were given assistance with.

Dave
 
Upvote 0
KOKOSEK

KOKOSEK said:
Debug.Print print out into Immediate Window. You have to switch on to show it in VBA editor.
Click to expand...
Thank You. The error 1004 has now been resolved however, instead of the original record being updated, a new record is being added to the worksheet. I have meticulously checked the code against another that works correctly. Please can you see any line of code which might prevent the original record from being updated and a new record being created. The full amended code follows.

Thank You in advance.

VBA Code: 
Dim CurrentRow As Long

Private Sub UserForm_Initialize()

    DTPicker1.Value = ""
    cboSchedulingType.Value = ""
    cboLocation.Value = ""
    txtStartTime.Value = "00:00"
    txtStartTime.MaxLength = 5
    txtFinishTime.Value = "00:00"
    txtFinishTime.MaxLength = 5
    cboPayRate.Value = ""
    CheckBoxPete.Value = False
    CheckBoxKirsty.Value = False
    CheckBoxJan.Value = False
    CheckBoxKelly.Value = False
    CheckBoxCarla.Value = False
    txtWorkDate.Value = ""
    txtDailyPayMonth.Value = ""
    txtDailyWorkHours.Value = ""
    txtDailyLeaveHours.Value = ""
    txtDailyNonWorkingDay.Value = ""
    txtDailyEarningsGross.Value = ""
    txtMonthlyWorkHours.Value = ""
    txtDailyEarningsGross.Value = ""
    txtMonthlyPayMonth.Value = ""
    txtMonthlyWorkHours.Value = ""
    txtMonthlyWorkEarningsGross.Value = ""
    txtMonthlyLeaveHours.Value = ""
    txtMonthlyLeaveEarningsGross.Value = ""
    txtTotalMonthlyEarningsGross.Value = ""
    txtDateSearch.Value = ""
    cboPayMonthSearch.Value = ""
    
    
    DTPicker1.SetFocus
           
End Sub
Private Sub txtStartTime_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If IsDate(txtStartTime.Value) And Len(txtStartTime.Text) = 5 Then
    Else
        MsgBox "Input Start Time as for example 09:15"
        txtStartTime.Text = ""
    End If

End Sub
Private Sub txtEndTime_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If IsDate(txtEndTime.Value) And Len(txtEndTime.Text) = 5 Then
    Else
        MsgBox "Input End Time as for example 09:15"
        txtEndTime.Text = ""
    End If

End Sub
Private Sub cmdInputRecords_Click()
Dim lastrow As Long
answer = MsgBox("Add the Record?", vbYesNo + vbQuestion, "Add Record?")
If answer = vbNo Then
Call UserForm_Initialize
DTPicker1.SetFocus
Else
lastrow = Sheets("Daily Hours Input").Range("A" & Rows.Count).End(xlUp).Row
Cells(lastrow + 1, "A").Value = DTPicker1
Cells(lastrow + 1, "B").Value = cboSchedulingType
Cells(lastrow + 1, "C").Value = cboLocation
Cells(lastrow + 1, "E").Value = txtStartTime
Cells(lastrow + 1, "F").Value = txtFinishTime
Cells(lastrow + 1, "K").Value = cboPayRate
Cells(lastrow + 1, "M").Value = CheckBoxPete
Cells(lastrow + 1, "N").Value = CheckBoxKirsty
Cells(lastrow + 1, "O").Value = CheckBoxJan
Cells(lastrow + 1, "P").Value = CheckBoxKelly
Cells(lastrow + 1, "Q").Value = CheckBoxCarla

MsgBox "Record has been added to the database", 0, "Record Added"

With ActiveSheet
    Application.Goto Reference:=.Cells(.Rows.Count, "A").End(xlUp).Offset(-20), Scroll:=True
End With
Call UserForm_Initialize
DTPicker1.SetFocus
End If
End Sub
Private Sub cboPayRate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim payrate As Double
payrate = cboPayRate.Value
cboPayRate.Value = Format(cboPayRate.Value, "Currency")
End Sub
Private Sub cmdClearForm_Click()
'Clears the User Form
Call UserForm_Initialize
End Sub
Private Sub cmdCloseForm_Click()
'Closes the User Form
    Unload Me
End Sub
Private Sub cmdDateSearch_Click()
'Used to search for records for a specific date

Dim Res As Variant
Dim lastrow
Dim myFind As String

    Res = Application.Match(CDbl(CDate(txtDateSearch)), Sheets("Daily Hours Input").Range("A:A"), 0)
    If IsError(Res) Then
        MsgBox "Date Not Found", vbInformation, "Date Not Found"
    Call UserForm_Initialize
    DTPicker1.SetFocus
    Exit Sub
    End If
    
lastrow = Sheets("Daily Hours Input").Range("A" & Rows.Count).End(xlUp).Row
myFind = txtDateSearch
For CurrentRow = 2 To lastrow
If Cells(CurrentRow, 1).Value = myFind Then
DTPicker1.Value = Cells(CurrentRow, 1)
cboSchedulingType.Value = Cells(CurrentRow, 2)
cboLocation.Value = Cells(CurrentRow, 3)
txtStartTime.Value = Cells(CurrentRow, 5)
txtFinishTime.Value = Cells(CurrentRow, 6)
cboPayRate.Value = Cells(CurrentRow, 11)
CheckBoxPete.Value = Cells(CurrentRow, 13)
CheckBoxKirsty.Value = Cells(CurrentRow, 14)
CheckBoxJan.Value = Cells(CurrentRow, 15)
CheckBoxKelly.Value = Cells(CurrentRow, 16)
CheckBoxCarla.Value = Cells(CurrentRow, 17)
txtWorkDate.Value = Cells(CurrentRow, 1).Value
txtDailyPayMonth.Value = Cells(CurrentRow, 4).Value
txtDailyEarningsGross.Value = Cells(CurrentRow, 12).Text
If Cells(CurrentRow, 2).Value = "Work" Then
txtDailyWorkHours.Value = Cells(CurrentRow, 10).Value
ElseIf Cells(CurrentRow, 2).Value = "Leave" Then
txtDailyLeaveHours.Value = Cells(CurrentRow, 10).Value
ElseIf Cells(CurrentRow, 2).Value = "Non-Working Day" Then
txtDailyNonWorkingDay.Value = "Yes"
Exit For
End If
End If
Next CurrentRow
DTPicker1.SetFocus
End Sub


Private Sub cmdUpdateRecords_Click()
'Used to update existing records
'Dim CurrentRow As Long
answer = MsgBox("Update the Record?", vbYesNo + vbQuestion, "Update Record?")
If answer = vbNo Then
Call UserForm_Initialize
DTPicker1.SetFocus
Else
Cells(CurrentRow, 1).Value = DTPicker1.Value
Cells(CurrentRow, 2).Value = cboSchedulingType.Value
Cells(CurrentRow, 3).Value = cboLocation.Value
Cells(CurrentRow, 5).Value = txtStartTime.Value
Cells(CurrentRow, 6).Value = txtFinishTime.Value
Cells(CurrentRow, 11).Value = cboPayRate.Value
Cells(CurrentRow, 13).Value = CheckBoxPete.Value
Cells(CurrentRow, 14).Value = CheckBoxKirsty.Value
Cells(CurrentRow, 15).Value = CheckBoxJan.Value
Cells(CurrentRow, 16).Value = CheckBoxKelly.Value
Cells(CurrentRow, 17).Value = CheckBoxCarla.Value

MsgBox "Record has been Updated", 0, "Record Updated"
Call UserForm_Initialize
DTPicker1.SetFocus
End If
End Sub
 
Upvote 0
Denny57 said:
KOKOSEK


Thank You. The error 1004 has now been resolved however, instead of the original record being updated, a new record is being added to the worksheet. I have meticulously checked the code against another that works correctly. Please can you see any line of code which might prevent the original record from being updated and a new record being created. The full amended code follows.

Thank You in advance.

VBA Code: 
Dim CurrentRow As Long

Private Sub UserForm_Initialize()

    DTPicker1.Value = ""
    cboSchedulingType.Value = ""
    cboLocation.Value = ""
    txtStartTime.Value = "00:00"
    txtStartTime.MaxLength = 5
    txtFinishTime.Value = "00:00"
    txtFinishTime.MaxLength = 5
    cboPayRate.Value = ""
    CheckBoxPete.Value = False
    CheckBoxKirsty.Value = False
    CheckBoxJan.Value = False
    CheckBoxKelly.Value = False
    CheckBoxCarla.Value = False
    txtWorkDate.Value = ""
    txtDailyPayMonth.Value = ""
    txtDailyWorkHours.Value = ""
    txtDailyLeaveHours.Value = ""
    txtDailyNonWorkingDay.Value = ""
    txtDailyEarningsGross.Value = ""
    txtMonthlyWorkHours.Value = ""
    txtDailyEarningsGross.Value = ""
    txtMonthlyPayMonth.Value = ""
    txtMonthlyWorkHours.Value = ""
    txtMonthlyWorkEarningsGross.Value = ""
    txtMonthlyLeaveHours.Value = ""
    txtMonthlyLeaveEarningsGross.Value = ""
    txtTotalMonthlyEarningsGross.Value = ""
    txtDateSearch.Value = ""
    cboPayMonthSearch.Value = ""
   
   
    DTPicker1.SetFocus
          
End Sub
Private Sub txtStartTime_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If IsDate(txtStartTime.Value) And Len(txtStartTime.Text) = 5 Then
    Else
        MsgBox "Input Start Time as for example 09:15"
        txtStartTime.Text = ""
    End If

End Sub
Private Sub txtEndTime_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If IsDate(txtEndTime.Value) And Len(txtEndTime.Text) = 5 Then
    Else
        MsgBox "Input End Time as for example 09:15"
        txtEndTime.Text = ""
    End If

End Sub
Private Sub cmdInputRecords_Click()
Dim lastrow As Long
answer = MsgBox("Add the Record?", vbYesNo + vbQuestion, "Add Record?")
If answer = vbNo Then
Call UserForm_Initialize
DTPicker1.SetFocus
Else
lastrow = Sheets("Daily Hours Input").Range("A" & Rows.Count).End(xlUp).Row
Cells(lastrow + 1, "A").Value = DTPicker1
Cells(lastrow + 1, "B").Value = cboSchedulingType
Cells(lastrow + 1, "C").Value = cboLocation
Cells(lastrow + 1, "E").Value = txtStartTime
Cells(lastrow + 1, "F").Value = txtFinishTime
Cells(lastrow + 1, "K").Value = cboPayRate
Cells(lastrow + 1, "M").Value = CheckBoxPete
Cells(lastrow + 1, "N").Value = CheckBoxKirsty
Cells(lastrow + 1, "O").Value = CheckBoxJan
Cells(lastrow + 1, "P").Value = CheckBoxKelly
Cells(lastrow + 1, "Q").Value = CheckBoxCarla

MsgBox "Record has been added to the database", 0, "Record Added"

With ActiveSheet
    Application.Goto Reference:=.Cells(.Rows.Count, "A").End(xlUp).Offset(-20), Scroll:=True
End With
Call UserForm_Initialize
DTPicker1.SetFocus
End If
End Sub
Private Sub cboPayRate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim payrate As Double
payrate = cboPayRate.Value
cboPayRate.Value = Format(cboPayRate.Value, "Currency")
End Sub
Private Sub cmdClearForm_Click()
'Clears the User Form
Call UserForm_Initialize
End Sub
Private Sub cmdCloseForm_Click()
'Closes the User Form
    Unload Me
End Sub
Private Sub cmdDateSearch_Click()
'Used to search for records for a specific date

Dim Res As Variant
Dim lastrow
Dim myFind As String

    Res = Application.Match(CDbl(CDate(txtDateSearch)), Sheets("Daily Hours Input").Range("A:A"), 0)
    If IsError(Res) Then
        MsgBox "Date Not Found", vbInformation, "Date Not Found"
    Call UserForm_Initialize
    DTPicker1.SetFocus
    Exit Sub
    End If
   
lastrow = Sheets("Daily Hours Input").Range("A" & Rows.Count).End(xlUp).Row
myFind = txtDateSearch
For CurrentRow = 2 To lastrow
If Cells(CurrentRow, 1).Value = myFind Then
DTPicker1.Value = Cells(CurrentRow, 1)
cboSchedulingType.Value = Cells(CurrentRow, 2)
cboLocation.Value = Cells(CurrentRow, 3)
txtStartTime.Value = Cells(CurrentRow, 5)
txtFinishTime.Value = Cells(CurrentRow, 6)
cboPayRate.Value = Cells(CurrentRow, 11)
CheckBoxPete.Value = Cells(CurrentRow, 13)
CheckBoxKirsty.Value = Cells(CurrentRow, 14)
CheckBoxJan.Value = Cells(CurrentRow, 15)
CheckBoxKelly.Value = Cells(CurrentRow, 16)
CheckBoxCarla.Value = Cells(CurrentRow, 17)
txtWorkDate.Value = Cells(CurrentRow, 1).Value
txtDailyPayMonth.Value = Cells(CurrentRow, 4).Value
txtDailyEarningsGross.Value = Cells(CurrentRow, 12).Text
If Cells(CurrentRow, 2).Value = "Work" Then
txtDailyWorkHours.Value = Cells(CurrentRow, 10).Value
ElseIf Cells(CurrentRow, 2).Value = "Leave" Then
txtDailyLeaveHours.Value = Cells(CurrentRow, 10).Value
ElseIf Cells(CurrentRow, 2).Value = "Non-Working Day" Then
txtDailyNonWorkingDay.Value = "Yes"
Exit For
End If
End If
Next CurrentRow
DTPicker1.SetFocus
End Sub


Private Sub cmdUpdateRecords_Click()
'Used to update existing records
'Dim CurrentRow As Long
answer = MsgBox("Update the Record?", vbYesNo + vbQuestion, "Update Record?")
If answer = vbNo Then
Call UserForm_Initialize
DTPicker1.SetFocus
Else
Cells(CurrentRow, 1).Value = DTPicker1.Value
Cells(CurrentRow, 2).Value = cboSchedulingType.Value
Cells(CurrentRow, 3).Value = cboLocation.Value
Cells(CurrentRow, 5).Value = txtStartTime.Value
Cells(CurrentRow, 6).Value = txtFinishTime.Value
Cells(CurrentRow, 11).Value = cboPayRate.Value
Cells(CurrentRow, 13).Value = CheckBoxPete.Value
Cells(CurrentRow, 14).Value = CheckBoxKirsty.Value
Cells(CurrentRow, 15).Value = CheckBoxJan.Value
Cells(CurrentRow, 16).Value = CheckBoxKelly.Value
Cells(CurrentRow, 17).Value = CheckBoxCarla.Value

MsgBox "Record has been Updated", 0, "Record Updated"
Call UserForm_Initialize
DTPicker1.SetFocus
End If
End Sub
Click to expand...
The additional record is only being added when the last record is being updated. I can probably live with this
 
Upvote 0
Since you have declared CurrentRow at the module level it is totally dependent on the value assigned to it in the module cmdDateSearch_Click.
In this module the only Exit For is when Cells(CurrentRow, 2).Value = "Non-Working Day"
For any other condition the For CurrentRow = 2 To lastrow loop only terminates when the loop recognises that CurrentRow > lastrow ie CurrentRow is lastrow + 1.
So except in the case of Non-Working Day, CurrentRow will always be lastrow + 1.
 
Upvote 0
Alex Blakenburg said:
Since you have declared CurrentRow at the module level it is totally dependent on the value assigned to it in the module cmdDateSearch_Click.
In this module the only Exit For is when Cells(CurrentRow, 2).Value = "Non-Working Day"
For any other condition the For CurrentRow = 2 To lastrow loop only terminates when the loop recognises that CurrentRow > lastrow ie CurrentRow is lastrow + 1.
So except in the case of Non-Working Day, CurrentRow will always be lastrow + 1.
Click to expand...
Alex Thank you for explaining why this is happening.
Would you be able to advise how the code should be stuctured as I need different controls on the user form to be populated depending on the value of columns B & J in the worksheet.?
REQUIREMENT
If Value Column B = "Work" then the value in Column J is to be displayed in control txtDailyWorkHours
If Value Column B = "Leave" then the value in Column J is to be displayed in control txtDailyLeaveHours
If Value Column B = "Non-Working Day" then "Yes" is to be displayed in control txtDailyNonWorkingDay

These are the only values that will be populated in Column B, so I suspect that the code needs to be "closed" to stop additional records being added.

Hoping thay you can help

Dave
 
Upvote 0
I have logged off for the night. So I won't be able to look at this until tomorrow.
In the following code each condition could be on a different CurrentRow.
Which row do you actually want to use as the CurrentRow ?
Or should the Exit For be in all 3 conditions so that whichever happens first is the one the CurrentRow gets set to ?

VBA Code: 
            If Cells(CurrentRow, 2).Value = "Work" Then
                txtDailyWorkHours.Value = Cells(CurrentRow, 10).Value
            ElseIf Cells(CurrentRow, 2).Value = "Leave" Then
                txtDailyLeaveHours.Value = Cells(CurrentRow, 10).Value
            ElseIf Cells(CurrentRow, 2).Value = "Non-Working Day" Then
                txtDailyNonWorkingDay.Value = "Yes"
                Exit For
            End If
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
VBA Subtotal 47,000+ Rows
Replies
1
Views
194
igold
igold
H
  • Solved
Macro to improve Optimisation
Replies
3
Views
146
Alex Blakenburg
A
theYaniac
  • Solved
I would like to insert a blank row between each new unique identifier in column A.
Replies
3
Views
653
MARK858
MARK858
S
  • Question Question
Searching Sheet Names using a wildcard
Replies
5
Views
265
mumps
mumps
S
  • Question Question
How do I sum without knowing the range?
Replies
1
Views
281
sGraham24
S

Forum statistics

Threads
1,222,833
Messages
6,168,523
Members
452,194
Latest member
Lowie27

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