Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    EXCEL/VBA (2003)

    Ok, my problem now lies with finding more than one occurrence of a jobname in column2. If i find an occurrence of said jobname It should look for column 9 to see if it is blank, if not end if. If so then it should enter the rest of my code. This is what I have so far. But it does not seem to work correctly. It just goes to the last occurrence or pops up with the msgbox of date not found.

    any ideas

    Dim Found As Range, temp As Range, JOBNAME As String
    JOBNAME = frmRLSEHOLD.txtRLJname.Text

    Set temp = ws.Columns(2).Find(what:=JOBNAME)
    If temp Is Nothing Then
    MsgBox JOBNAME & " Not found"
    Exit Sub
    End If
    Set Found = temp
    If Found.Offset(0, 8).Value = "" Then
    Do
    Set temp = ws.Columns(2).FindNext(After:=temp)
    If Found.Row >= temp.Row Then Exit Do
    Set Found = temp
    If Found.Offset(0, 8).Value <> "" Then Exit Do
    Loop
    End If
    If Found.Offset(0, 8).Value = "" Then
    MsgBox JOBNAME & " with date not found"
    Else
    MsgBox Found.Address(False, False)
    End If

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: EXCEL/VBA (2003)

    Are you trying to find the first instance of the job number where the value in column 9 is not blank?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: EXCEL/VBA (2003)

    I am not sure what you want the code to do. Based on what you describe it seems to work. If the Jobname is not in Column b you get the msg that it is not found.

    If the jobname is found in Col B, it checks col J for a blank, if it is not a blank it returns the address where it was found.

    If the jobname is found in Col B, and col J is blank, it tries to find the next one(s). If it finds all blank you get the "date not found" message" but the first one that is not blank gives the address of that cell.

    What do you want the code to do?
    Steve

  4. #4
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EXCEL/VBA (2003)

    ok say that in column 2 i have 4 occurrences of job et5555, in column 9 I have the date (=date) so that when the user makes an entry it is automatically inputted into the ws.

    the code I have shown only finds the first occurrence of the jobname or it put up the msgbox.

    I need for it to check column2 for the first occurrence of said job then check column 9 to see if that is blank if it is blank then
    row = Found.Row

    ws.Cells(irow, 8).Value = Me.txtNop2.Value
    If Me.txtNop2.Value = "" Then
    Me.txtNop2.SetFocus
    MsgBox "Please enter the Requestor's Name!"
    End If
    ws.Cells(irow, 9).Value = Date
    ws.Cells(irow, 10).Value = Time
    ws.Cells(irow, 11).Value = Me.txtCOMments.Value
    'End If

    if it's not blank then I need it to look for the next occurrence in column 2. if it finds the next occurrence then check column 9 for blanks if it is = to ""
    then
    row = Found.Row

    ws.Cells(irow, 8).Value = Me.txtNop2.Value
    If Me.txtNop2.Value = "" Then
    Me.txtNop2.SetFocus
    MsgBox "Please enter the Requestor's Name!"
    End If
    ws.Cells(irow, 9).Value = Date
    ws.Cells(irow, 10).Value = Time
    ws.Cells(irow, 11).Value = Me.txtCOMments.Value
    'End If

    else check column2 for next occurrence of same job name if found then check column 9 etc etc etc

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: EXCEL/VBA (2003)

    I am still not clear what you have and what you want. Could you elalborate?

    Do you want to find all occurences of the jobname in Col B and if the value in Column J is blank to add the date/time and comment to Cols J-L?

    Essentially, You want to ignore any rows without that Job name or any rows with the jobname that have J filled in...

    Steve

  6. #6
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EXCEL/VBA (2003)

    i'm looking for any duplicate jobnames in column b,

    if found then i need to check column 9 to see if it is blank
    if it is not blank then search for another occurrence of the same jobname in columnb, if found then check column 9 for blanks.
    if column 9 is blanks than

    irow = Found.Row

    ws.Cells(irow, 8).Value = Me.txtNop2.Value
    If Me.txtNop2.Value = "" Then
    Me.txtNop2.SetFocus
    MsgBox "Please enter the Requestor's Name!"
    End If
    ws.Cells(irow, 9).Value = Date
    ws.Cells(irow, 10).Value = Time
    ws.Cells(irow, 11).Value = Me.txtCOMments.Value


    if column 9 is not blank the search for another occurrence of the same jobname in columnb, if found check column 9 for blanks if its blank than
    irow = Found.Row

    ws.Cells(irow, 8).Value = Me.txtNop2.Value
    If Me.txtNop2.Value = "" Then
    Me.txtNop2.SetFocus
    MsgBox "Please enter the Requestor's Name!"
    End If
    ws.Cells(irow, 9).Value = Date
    ws.Cells(irow, 10).Value = Time
    ws.Cells(irow, 11).Value = Me.txtCOMments.Value


    else check column b again etc etc...

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: EXCEL/VBA (2003)

    Something like this (modified from your code). I don't see why you are checking for the "requestor's name" since you are not using it in this snippet. I checked to ensure there was a jobname.

    I presume this is run from some command button on the userform. I would not run it from the txtRLJname "change" to eliminate the msgbox when it can not find the intermediate values:

    <pre> Dim Found As Range, temp As Range, JOBNAME As String
    With Me.txtRLJname
    If .Value = "" Then
    .SetFocus
    MsgBox "Please enter the JobName!"
    Exit Sub
    End If
    JOBNAME = .Text
    End With

    Set temp = ws.Columns(2).Find( _
    what:=JOBNAME, lookat:=xlWhole)
    If temp Is Nothing Then
    MsgBox JOBNAME & " Not found"
    Exit Sub
    End If
    Set Found = temp
    With Found
    If .Offset(0, 8).Value = "" Then
    .Offset(0, 8) = Date
    .Offset(0, 9) = Time
    .Offset(0, 10) = Me.txtCOMments
    End If
    End With
    Do
    Set temp = ws.Columns(2).FindNext(After:=temp)
    If Found.Row >= temp.Row Then Exit Do
    Set Found = temp
    With Found
    If .Offset(0, 8).Value = "" Then
    .Offset(0, 8) = Date
    .Offset(0, 9) = Time
    .Offset(0, 10) = Me.txtCOMments
    End If
    End With
    Loop
    </pre>


    Steve

Posting Permissions

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