Update data on new line if person name is found already

hsolanki · Dec 7, 2020

Hi i have an code in combobox9 and i want also to update in column H when selected the names from the combobox9 and again update column H if the same person name found from the combobox on a new raw.

this is what i have so far and need some help. it needs to continue to look for the person name which is in column C and it need to check if that raw it is not already completed on column H until it finds a blank cell against the same person name.

VBA Code:

If Me.ComboBox9.Value <> "" Then



If VBA.CVar(Application.Match(VBA.CVar(Me.ComboBox9.Value), sh.Range("C:C"), 0)) = True Then

MsgBox "Record Not found for this PO-Number", vbCritical

Exit Sub





Else

i = Application.Match(VBA.CStr(Me.ComboBox9.Value), sh.Range("C:C"), 0)



End If



lCol = Me.ComboBox9.Value

Set findvalue2 = sh.Range("C:C").Find(What:=lCol, LookIn:=xlValues)

If Not findvalue2 Is Nothing Then

adr = findvalue2.Address

Do



If findvalue2.Offset(0, -1).Value = Me.TextBox19 Then



sh.Unprotect "Bhaji2020"

findvalue2.Offset(0, 6).Value = Me.TextBox19.Value = ""

Exit Do

End If

Set findvalue2 = sh.Range("C:C").FindNext(findvalue2)

Loop While findvalue2.Address <> adr

Set findvalue2 = Nothing

End If

hope i am making sense in here

hsolanki · Dec 15, 2020

Hi Can anyone help me resolve the problem please as i am really stuck. i can provide sample book if required

thank you

dmt32 · Dec 15, 2020

Hi,
sounds like you probably need to consider using Rang.FindNext Method

Have a read here:Range.FindNext method (Excel)
There are some code examples you can take & may be able to adapt to meet your specific requirement

Dave

hsolanki · Dec 15, 2020

Hi thank you for your promote respond. sorry i new to VBA and i have tried and doe not know what im doing wrong.

would you kindly be able to help me with the code? if you can

dmt32 · Dec 15, 2020

Hi,
post all your code with clear explanation of results you require - I am time limited this week but will have a look - there are others here who may also be able to assist.

Dave

hsolanki · Dec 15, 2020

Hi Thank you so much. i have placed the code below. basically on tab 4 call (Attendance). for example on attendance tab there guy call Emanuele on cell D6 and again on D11 so bascially i want an code when you update RTW his record from the user form Attendance, code needs to find if cell i6 is completed already against cel D6 to match and loop for the same name to update next cell which would be the i11 which will match against D11.

i hope i am making a sense.

VBA Code:

Private Sub ComboBox9_Change()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Attendance")

Dim i As Long

Dim x As Long

Dim findvalue

Dim adr As String

Dim lCol As Variant



On Error GoTo errhandler:



If Me.ComboBox9.Value <> "" Then



If VBA.CVar(Application.Match(VBA.CVar(Me.ComboBox9.Value), sh.Range("D:D"), 0)) = True Then

MsgBox "Record Not found", vbCritical

Exit Sub





Else

i = Application.Match(VBA.CStr(Me.ComboBox9.Value), sh.Range("D:D"), 0)

End If



Me.OptionButton3.Enabled = False

Me.CommandButton5.Enabled = False

Me.CommandButton6.Enabled = False



Me.TextBox14.Value = sh.Range("A" & i).Value

Me.TextBox14.Enabled = False

Me.TextBox14.BackColor = RGB(155, 295, 155)



Me.ComboBox5.Value = sh.Range("B" & i).Value

Me.ComboBox5.Enabled = False

'change the button back color

Me.ComboBox5.BackColor = RGB(155, 295, 155)



Me.TextBox34.Value = sh.Range("C" & i).Value

Me.TextBox34.Enabled = False

Me.TextBox34.BackColor = RGB(155, 295, 155)



Me.TextBox15.Value = sh.Range("D" & i).Value

Me.TextBox15.Enabled = False

'change the button back color

Me.TextBox15.BackColor = RGB(155, 295, 155)



Me.ComboBox6.Value = sh.Range("E" & i).Value

Me.ComboBox6.Enabled = False

'change the button back color

Me.ComboBox6.BackColor = RGB(155, 295, 155)



Me.TextBox16.Value = sh.Range("F" & i).Value

Me.TextBox16.Enabled = False

'change the button back color

Me.TextBox16.BackColor = RGB(155, 295, 155)



Me.TextBox17.Value = sh.Range("G" & i).Value

Me.TextBox17.Enabled = False

'change the button back color

Me.TextBox17.BackColor = RGB(155, 295, 155)



Me.TextBox18.Value = sh.Range("H" & i).Value

Me.TextBox18.Enabled = False

'change the button back color

Me.TextBox18.BackColor = RGB(155, 295, 155)



If Me.TextBox19.Value = sh.Range("i" & i).Value = True Then

Me.TextBox19.Enabled = True

Me.TextBox19.BackColor = &H80000005

Else

Me.TextBox19.Enabled = False

'Me.CommandButton11.Enabled = False

Me.TextBox19.BackColor = RGB(155, 295, 155)

Me.TextBox19.Value = sh.Range("i" & i).Value

End If







Else



Me.TextBox14.Value = ""

Me.TextBox15.Value = ""

Me.TextBox16.Value = ""

Me.TextBox17.Value = ""

Me.TextBox18.Value = ""

Me.TextBox19.Value = ""

Me.TextBox34.Value = ""

Me.ComboBox5.Value = ""

Me.ComboBox6.Value = ""

Me.ComboBox9.Value = ""



End If



Exit Sub



errhandler:

MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _

& Err.Number & vbCrLf & Err.Description & vbCrLf & _

"Please Contact Admin", vbCritical, "Error Message"

End Sub





Private Sub CommandButton11_Click()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Attendance")

Dim n As Long

Dim answer As String

Dim findvalue

Dim adr As String

Dim lCol As Variant



Application.EnableCancelKey = xlDisabled



Application.ScreenUpdating = False

Application.EnableEvents = False



On Error GoTo errhandler:



If Me.ComboBox9.Value = "" Then

MsgBox "Please select the staff name for RTW", vbCritical, "Select name"

Exit Sub

End If



If Me.TextBox19.Value = "" Then

MsgBox "Please confirm RTW & enter your name", vbCritical, "RTW Confirmation"

Exit Sub

End If





n = Application.Match(VBA.CVar(Me.ComboBox9.Value), sh.Range("D:D"), 0)



answer = MsgBox(Me.TextBox19.Value & Chr(10) & Chr(10) & Me.TextBox15.Value & Chr(10) & Chr(10) & "Would you like to update RTW and confirmation for above person?", vbQuestion + vbYesNo, "Confirm to Add")



If answer = vbNo Then

Exit Sub

End If



Dim sname As String, sID As String, rng As Range

Dim firstAddress As String



sname = Me.ComboBox9

sID = Me.TextBox34



With sh.Range("D:D")

Set rng = .Find(What:=sname, LookIn:=xlValues)

' rng is col G

If Not rng Is Nothing Then

firstAddress = rng.Address

Do

If rng.Offset(0, -1) = sID Then ' col F

sh.Unprotect "123"

rng.Offset(0, 7) = Format(Now, "HH:MM:SS") ' col M

'Debug.Print rng.Address

End If

Set rng = .FindNext(rng)

Loop While rng.Address <> firstAddress

End If

End With









sh.Range("I" & n).Value = Me.TextBox19.Value



'''''''''' Clear boxes



Me.TextBox4.Value = ""

Me.TextBox15.Value = ""

Me.TextBox16.Value = ""

Me.TextBox17.Value = ""

Me.TextBox18.Value = ""

Me.TextBox19.Value = ""

Me.TextBox34.Value = ""



Me.ComboBox5.Value = ""

Me.ComboBox6.Value = ""

Me.ComboBox9.Value = ""



sh.Protect "123"



MsgBox "RTW Updated Successfully!", vbInformation





Unload Me



Worksheets("Attendance").Activate

Worksheets("Attendance").Cells(1, 3).Select



Application.EnableEvents = True

Application.ScreenUpdating = True



On Error GoTo 0

Exit Sub

errhandler:

MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _

& Err.Number & vbCrLf & Err.Description & vbCrLf & _

"Please Contact Admin"



End Sub

dmt32 · Dec 15, 2020

only managed a quick look but maybe something like this?

VBA Code:

Private Sub CommandButton11_Click()
   
    Dim sh          As Worksheet
    Dim rng         As Range
    Dim StaffName   As String, StaffID As String
    Dim firstAddress As String
   
   
    On Error GoTo errhandler
   
    StaffName = Me.ComboBox9.Text
   
    StaffID = Me.TextBox34.Text
   
     Set sh = ThisWorkbook.Sheets("Attendance")

         sh.Unprotect "123"
        
        Set rng = sh.Columns.Find(What:=StaffName, LookIn:=xlValues, lookat:=xlWhole)
       
        If Not rng Is Nothing Then
           
            firstAddress = rng.Address
           
            Do
                If rng.Offset(0, -1) = StaffID Then
                With rng.Offset(0, 5)
                    If Len(.Value) = 0 Then .Value = "Completed " & Date
                End With
            End If
           
            Set rng = rng.FindNext(rng)
            If rng Is Nothing Then Exit Do
           
        Loop While rng.Address <> firstAddress
       
        Else
            'search text not found
            Err.Raise 744
    End If
   
End With


errhandler:
    sh.Protect "123"
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Suggestion untested & may need to be adjusted to meet specific project need which is why I removed all the other stuff in the code to help you getting this bit right -

If still having difficulty, place copy of your workbook with dummy data in a dropbox & provide a link to it - may be able to find a moment this week to have a play

Dave

hsolanki · Dec 15, 2020

Hi it did not work. i uploaded an link below for sample of the book and also an complete code

VBA Code:

Option Explicit



Private Sub ComboBox9_Change()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Attendance")

Dim i As Long

Dim x As Long

Dim findvalue

Dim adr As String

Dim lCol As Variant



On Error GoTo errhandler:



If Me.ComboBox9.Value <> "" Then



If VBA.CVar(Application.Match(VBA.CVar(Me.ComboBox9.Value), sh.Range("D:D"), 0)) = True Then

MsgBox "Record Not found for this PO-Number", vbCritical

Exit Sub





Else

i = Application.Match(VBA.CStr(Me.ComboBox9.Value), sh.Range("D:D"), 0)

End If



Me.OptionButton3.Enabled = False

Me.CommandButton5.Enabled = False

Me.CommandButton6.Enabled = False



Me.TextBox14.Value = sh.Range("A" & i).Value

Me.TextBox14.Enabled = False

Me.TextBox14.BackColor = RGB(155, 295, 155)



Me.ComboBox5.Value = sh.Range("B" & i).Value

Me.ComboBox5.Enabled = False

'change the button back color

Me.ComboBox5.BackColor = RGB(155, 295, 155)



Me.TextBox34.Value = sh.Range("C" & i).Value

Me.TextBox34.Enabled = False

Me.TextBox34.BackColor = RGB(155, 295, 155)



Me.TextBox15.Value = sh.Range("D" & i).Value

Me.TextBox15.Enabled = False

'change the button back color

Me.TextBox15.BackColor = RGB(155, 295, 155)



Me.ComboBox6.Value = sh.Range("E" & i).Value

Me.ComboBox6.Enabled = False

'change the button back color

Me.ComboBox6.BackColor = RGB(155, 295, 155)



Me.TextBox16.Value = sh.Range("F" & i).Value

Me.TextBox16.Enabled = False

'change the button back color

Me.TextBox16.BackColor = RGB(155, 295, 155)



Me.TextBox17.Value = sh.Range("G" & i).Value

Me.TextBox17.Enabled = False

'change the button back color

Me.TextBox17.BackColor = RGB(155, 295, 155)



Me.TextBox18.Value = sh.Range("H" & i).Value

Me.TextBox18.Enabled = False

'change the button back color

Me.TextBox18.BackColor = RGB(155, 295, 155)



If Me.TextBox19.Value = sh.Range("i" & i).Value = True Then

Me.TextBox19.Enabled = True

Me.TextBox19.BackColor = &H80000005

Else

Me.TextBox19.Enabled = False

'Me.CommandButton11.Enabled = False

Me.TextBox19.BackColor = RGB(155, 295, 155)

Me.TextBox19.Value = sh.Range("i" & i).Value

End If




Else



Me.TextBox14.Value = ""

Me.TextBox15.Value = ""

Me.TextBox16.Value = ""

Me.TextBox17.Value = ""

Me.TextBox18.Value = ""

Me.TextBox19.Value = ""

Me.TextBox34.Value = ""

Me.ComboBox5.Value = ""

Me.ComboBox6.Value = ""

Me.ComboBox9.Value = ""



End If



Exit Sub



errhandler:

MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _

& Err.Number & vbCrLf & Err.Description & vbCrLf & _

"Please Contact Admin", vbCritical, "Error Message"

End Sub



Private Sub CommandButton11_Click()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Attendance")

Dim n As Long

Dim answer As String

Dim findvalue

Dim adr As String

Dim lCol As Variant



Application.EnableCancelKey = xlDisabled


Application.ScreenUpdating = False

Application.EnableEvents = False



On Error GoTo errhandler:



If Me.ComboBox9.Value = "" Then

MsgBox "Please select the staff name for RTW", vbCritical, "Select name"

Exit Sub

End If


If Me.TextBox19.Value = "" Then

MsgBox "Please confirm RTW & enter your name", vbCritical, "RTW Confirmation"

Exit Sub

End If



n = Application.Match(VBA.CVar(Me.ComboBox9.Value), sh.Range("D:D"), 0)



answer = MsgBox(Me.TextBox19.Value & Chr(10) & Chr(10) & Me.TextBox15.Value & Chr(10) & Chr(10) & "Would you like to update RTW and confirmation for above person?", vbQuestion + vbYesNo, "Confirm to Add")



If answer = vbNo Then

Exit Sub

End If



Dim sname As String, sID As String, rng As Range

Dim firstAddress As String



sname = Me.ComboBox9

sID = Me.TextBox34



With sh.Range("D:D")

Set rng = .Find(What:=sname, LookIn:=xlValues)

' rng is col D

If Not rng Is Nothing Then

firstAddress = rng.Address

Do

If rng.Offset(0, -1) = sID Then

sh.Unprotect "Bhaji2020"

sh.Range("I" & n).Value = Me.TextBox19.Value



End If

Set rng = .FindNext(rng)

Loop While rng.Address <> firstAddress

End If

End With



'''''''''' Clear boxes


Me.TextBox4.Value = ""

Me.TextBox15.Value = ""

Me.TextBox16.Value = ""

Me.TextBox17.Value = ""

Me.TextBox18.Value = ""

Me.TextBox19.Value = ""

Me.TextBox34.Value = ""



Me.ComboBox5.Value = ""

Me.ComboBox6.Value = ""

Me.ComboBox9.Value = ""



sh.Protect "Bhaji2020"



MsgBox "RTW Updated Successfully!", vbInformation



Unload Me


Worksheets("Attendance").Activate

Worksheets("Attendance").Cells(1, 3).Select



Application.EnableEvents = True

Application.ScreenUpdating = True



On Error GoTo 0

Exit Sub

errhandler:

MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _

& Err.Number & vbCrLf & Err.Description & vbCrLf & _

"Please Contact Admin"



End Sub



Private Sub UserForm_Activate()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Attendance")

Dim n As Long



Me.ComboBox9.Clear

Me.ComboBox9.AddItem ""



For n = 5 To sh.Range("D" & Application.Rows.Count).End(xlUp).Row

Me.ComboBox9.AddItem sh.Range("D" & n).Value

Next n



Application.ScreenUpdating = False

Application.EnableEvents = False




With Me.ComboBox5

.AddItem "Sick"

.AddItem "Absent"

End With



End Sub

Test book V1.xlsm

dmt32 · Dec 15, 2020

Hi,
I can see the issue - search code needs to be located in the Combobox change event to find correct record.
If I can find time, will see if I can update it for you

Dave.

hsolanki · Dec 15, 2020

Hi Excellent, many thanks for all your time and please if you could correct the code as i have been struggling for past few weeks.

yes you are right it needs to loop within the combo prior to updating the data sheet.

Thank you once again

Update data on new line if person name is found already

hsolanki

Board Regular

Excel Facts

hsolanki

Board Regular

dmt32

Well-known Member

hsolanki

Board Regular

dmt32

Well-known Member

hsolanki

Board Regular

Attachments

dmt32

Well-known Member

hsolanki

Board Regular

dmt32

Well-known Member

hsolanki

Board Regular

Similar threads

Forum statistics

Share this page

Update data on new line if person name is found already

Board Regular

Excel Facts

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Attachments

Well-known Member

Board Regular

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock