Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Find 2 Cell Values On Same Row

  1. #1
    Join Date
    6th August 2005
    Posts
    7

    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

    Thanks
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th March 2007
    Location
    LONDON, UK
    Posts
    1,377
    Here's one part

    VB:
    Dim rRange As Worksheet 
    Set rRange = ActiveSheet 
    
    
    VB:
    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" 
    Else 
         '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 at 01:24.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    6th August 2005
    Posts
    7

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

    Zimitry
    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.

    thanks

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,784

  5. #5
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

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

    Hi,

    VB:
    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 
        Next 
        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 
    
    
    HTH

  6. #6
    Join Date
    6th August 2005
    Posts
    7

    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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,784

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

    WOW! Individual thanks. Thank YOU stockpick

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Search/Find Multiple Cell Values On Another Worksheet
    By mprice1988 in forum Excel General
    Replies: 13
    Last Post: May 23rd, 2008, 06:11
  2. Find All Cell Values In Column And Delete Row
    By camross in forum Excel General
    Replies: 9
    Last Post: January 25th, 2008, 12:45
  3. Replies: 10
    Last Post: December 16th, 2007, 12:35
  4. Replies: 7
    Last Post: September 30th, 2006, 12:53
  5. Find value in a cell with multiple values
    By amesic in forum Excel General
    Replies: 7
    Last Post: August 13th, 2006, 12:16

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno