No announcement yet.

Find 2 Cell Values On Same Row

  • Filter
  • Time
  • Show
Clear All
new posts

  • Find 2 Cell Values On Same Row

    Can anybody help please?

    I have attached an example worksheet which I think will explain better than I can but here goes.

    There is a user form which forces the user to select from all the three comboboxes boxes.
    The comboboxes are NOT populated from the worksheet.
    The Date of Shift get todayís system date and the previous days date.
    The shift pattern and shift duration are set entries.
    When the update button is hit the object is to look in column "A&B" to see if a row has an existing entry which matches the userís entry.
    If an existing entry is found then it will update the cell in column "C" in that row for the matching DATE and PATTERN
    If no matching entry exists it will append a new row with the user entry.

    I have been trying to do this for a while trying what I know and then I found the Find matching X sample written by ozgrid.
    Tried it out and found that it used the columns to populate the comboboxes and did not like using a date also I do not need a list box

    So I decided to try and modify ( 2 whole days over the weekend, so I am no programmer just try as best I can) it to suit the needs outlined above.
    I have reached the limits of my skill and now get "Object Variable or with block variable not set"

    If anybody can help in getting this code to work it would be appreciated

    Attached Files

  • #2
    Here's one part

    Dim rRange As Worksheet
        Set rRange = ActiveSheet
    On Error Resume Next
        Set rCell = rRange.Columns(1).Find(What:=strFind1, After:=rCell, _
                  LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                  SearchDirection:=xlNext, MatchCase:=False)
     'Check each find to see if strFind2 occur on the same row.
     'I do not know if any of this code will work as I can not get past the
     'Set rCell = rRange.Cells(1, 1) line of code.
     'This should check if the combox1 & combobox2 enteries are in the same row.
        If rCell Is Nothing Then
            MsgBox "rCell not found"
            'Code to handle value found
        End If
    If rCell Is Nothing Then
            MsgBox "rCell String2 is nothing"
            ElseIf rCell Like strFind2 Then
            bFound = True    'Used to check both cells are found.
            rCell.Activate     'select the found cell in column A.
            ActiveCell.Offset(0, 1) = ComboBox2.Value 'update the shift time in column C.
        End If
    If bFound = False Then
    Dim lRow As Long
        lRow = rRange.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    '    Range("A2").Select
    '    Do
    '    If IsEmpty(ActiveCell) = False Then
    '        ActiveCell.Offset(1, 0).Select
    '    End If
    '    Loop Until IsEmpty(ActiveCell) = True
            rRange.Cells(lRow, 1).Value = Trim(UserForm1.ComboBox1.Value)
            rRange.Cells(lRow, 2).Value = Trim(UserForm1.ComboBox2.Value)
            rRange.Cells(lRow, 3).Value = Trim(UserForm1.ComboBox3.Value)
    '    ActiveCell.Offset(0, 0) = ComboBox1.Value
    '    ActiveCell.Offset(0, 1) = ComboBox2.Value
    '    ActiveCell.Offset(0, 2) = ComboBox3.Value
    '    Range("A2").Select
    End If
    Last edited by Craig Ottley; July 3rd, 2007, 01:24.


    • #3
      Re: Find 2 cells in same row, then update, append cells

      Wow, thanks for the quick response at the first part gets it to do something. I have managed to use the F8function to step through but this is way above my level.
      It seems getting the result of rCell is an issue.
      Thanks again for the work you have already put in.
      I will see if I can understand this and do some reading but I do not hold out a lot of hope with my limited level.



      • #4
        Re: Find 2 cells in same row, then update, append cells

        See this page which finds x values on the same row

        Find 3 Matching Criteria


        • #5
          Re: Find 2 cells in same row, then update, append cells


          Private Sub CommandButton1_Click()
          Dim a, x, y, i As Long, Flg As Boolean

          With ActiveSheet
          a = .Range("a2", .Range("c" & Rows.Count).End(xlUp))
          End With
          x = Me.ComboBox1.Value & ";" & Me.ComboBox2.Value
          For i = 1 To UBound(a, 1)
          y = a(i, 1) & ";" & a(i, 2)
          If x = y Then
          Cells(i + 1, 3) = Me.ComboBox3
          Flg = True
          Exit For
          End If
          If Flg = False Then
          With Cells(UBound(a, 1) + 2, 1)
          .Value = Me.ComboBox1
          .Offset(, 1) = Me.ComboBox2
          .Offset(, 2) = Me.ComboBox3
          End With
          End If
          End Sub




          • #6
            Re: Find 2 cells in same row, then update, append cells

            Many thanks to all who replied
            Zimitry - your code allowed me to fix my original post.
            Dave - for the link
            Krishnakumar - for the example that worked perfectly

            I am now spoilt for choice

            Thanks again


            • #7

              Re: Find 2 cells in same row, then update, append cells

              WOW! Individual thanks. Thank YOU stockpick