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

    lookin for data in previous worksheets (excel/vba 2003)

    checking previous worksheets for data.

    --------------------------------------------------------------------------------

    the way I currently have my excel database set up is that if the active sheet say is March and the user wants to release a job, it only searches the mar2008 worksheet (active sheet).
    but lets say that the user put the job on hold in january. I need for it to be able to look in all the monthly spreedsheets for when the job was put on hold then add the release info that the user adds through the userform.

    Any Ideas.

    Here is my code so far:


    <code>
    Option Explicit

    Private Sub cmdRJFH_Click()
    Dim myDate As Date, newDate As Date, oldSheet As String, newSheet As String, irow As Integer
    Dim ws As Worksheet
    Set ws = Worksheets("MAR2008")
    'check for a job name!
    If Trim(Me.txtRLJname.Value) = "" Then
    Me.txtRLJname.SetFocus
    MsgBox "Please enter the Job name that has been requested to be taken off hold!"
    Exit Sub
    End If

    Dim Found As Range, temp As Range, JOBNAME As String, FOUND1 As Range, found2 As Range, found3 As Range

    JOBNAME = frmRLSEHOLD.txtRLJname.Text
    frmRLSEHOLD.Hide
    Set temp = ws.Columns(2).Find(What:=JOBNAME)
    If temp Is Nothing Then
    MsgBox JOBNAME & " Not found"
    Exit Sub
    End If
    If temp <> "" And temp.Offset(0, 8).Value <> "" Then
    ' It ain't this one
    Set Found = ws.Columns(2).FindNext(after:=temp)
    Else
    Set Found = temp
    End If
    If Found <> "" And Found.Offset(0, 8).Value <> "" Then
    ' It ain't this one
    Set FOUND1 = ws.Columns(2).FindNext(after:=Found)
    Else
    Set FOUND1 = Found
    End If
    If FOUND1 <> "" And FOUND1.Offset(0, 8).Value <> "" Then
    ' It ain't this one
    Set found2 = ws.Columns(2).FindNext(after:=FOUND1)
    Else
    Set found2 = FOUND1
    End If
    If found2 <> "" And found2.Offset(0, 8).Value <> "" Then
    ' It ain't this one
    Set found3 = ws.Columns(2).FindNext(after:=found2)
    If temp Is Nothing Then
    MsgBox JOBNAME & " Not found"
    Exit Sub
    End If
    Else
    Set found3 = found2
    End If


    irow = Found.Row
    irow = FOUND1.Row
    irow = found2.Row
    irow = found3.Row


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


    'clear the data
    Me.txtRLJname.Value = ""
    Me.txtNop2.Value = ""
    Me.txtCOMments.Value = ""
    Me.txtRLJname.SetFocus

    MsgBox " DATABASE HAS BEEN UPDATED WITH YOUR RELEASE INFORMATION"


    End Sub
    Private Sub cmdCTF_Click()
    'clear the data
    Me.txtRLJname.Value = ""
    Me.txtNop2.Value = ""
    Me.txtCOMments.Value = ""
    Me.txtRLJname.SetFocus
    End Sub
    Private Sub cmdExit_Click()
    Unload Me
    MsgBox "THANK YOU, PLEASE COME AGAIN"

    End Sub
    Private Sub UserForm_QueryClose(Cancel As Integer, _
    CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
    Cancel = True
    MsgBox "Please use the exit button!"
    End If
    End Sub
    </code>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: lookin for data in previous worksheets (excel/vba 2003)

    It would be easier to store all data in one worksheet, so that you don't have to search across multiple sheets.
    You can always filter the sheet for reporting purposes.

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

    Re: lookin for data in previous worksheets (excel/vba 2003)

    sure would be easier that way, but with an average of 1400 hits in a month, it's not do able.

    do you have any ideas

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: lookin for data in previous worksheets (excel/vba 2003)

    With 1400 new rows a month, it'd take almost 4 years before you run into the 65,536 rows limit of a worksheet in Excel 2003. By that time, you'll surely have upgraded to Excel 2007 or later, with more than a million rows available...

  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: lookin for data in previous worksheets (excel/vba 2003)

    Does something like this help ?

    <pre> <font color=red>Dim iMonth As Integer</font color=red>
    JOBNAME = frmRLSEHOLD.txtRLJname.Text
    frmRLSEHOLD.Hide

    Set temp = ws.Columns(2).Find(What:=JOBNAME)
    <font color=red> If temp Is Nothing Then
    iMonth = Month(Date) - 1
    Do While iMonth > 0 And temp Is Nothing
    Set ws = Worksheets(Format(DateSerial(Year(Date), iMonth, 1), "MMMYYYY"))
    Set temp = ws.Columns(2).Find(What:=JOBNAME)
    iMonth = iMonth - 1
    Loop
    End If</font color=red>
    If temp Is Nothing Then
    MsgBox JOBNAME & " Not found"
    Exit Sub
    End If</pre>


    If the JOBNAME is not found in the current worksheet (ws), the code gets the current month number and starts searching from the previous one until it gets to January or finds it. If it does not find it in the prvious months it pops up the message, if it finds it the ws has been set to that sheet and your code will then work on the previous sheet.

    The code presumes that the sheets are named with the format MMMYYYY and all do exist if not the case then you would have to test for this or adapt accordingly

    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: lookin for data in previous worksheets (excel/vba 2003)

    sorry have not gotten around to this one yet today. but I do have another question.

    in vba is there such a thing as a referback.

    if the user has more than one job to put in the ticket I need the spreedsheet to reflect this.

    so what I did was from the frmholdbook2 (userform name) - i have the user input the jobname, number, requestor, and their initials.
    a msgbox opens and asks them if they have more than one job to put inside this ticket. if no exit else show frmmulti (another userform) that allows them to input just the jobname and the ticket number and then add it to the very next line on the spreedsheet.
    Here's where the referback come in....
    if they have a third or more jobs to input into the same ticket than show frmmulti again and have them input the jobname and job ticket all over again etc..

    any ideas?

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

    Re: lookin for data in previous worksheets (excel/vba 2003)

    steve - what is temp... i dimmed jobname as string.

    but what about TEMP, it's flagging that now saying object required. please assist.

    this is my current code below

    <code>
    Private Sub cmdAdd_Click()
    Dim strValue As String
    Dim rng As Range
    Dim irow As Long
    Dim myDate As Date, newDate As Date, oldSheet As String, newSheet As String
    Dim ws As Worksheet
    Set ws = Worksheets("mar2008")
    strValue = Me.txtSdnumber
    '************************************************* ***************
    Dim iMonth As Integer
    Dim jobname As String
    Dim temp As String

    jobname = frmRLSEHOLD.txtRLJname.Text
    frmRLSEHOLD.Hide
    Set temp = ws.Columns(2).Find(What:=jobname)
    If temp Is Nothing Then
    iMonth = Month(Date) - 1
    Do While iMonth > 0 And temp Is Nothing
    Set ws = Worksheets(Format(DateSerial(Year(Date), iMonth, 1), "MMMYYYY"))
    Set temp = ws.Columns(2).Find(What:=jobname)
    iMonth = iMonth - 1
    Loop
    End If
    If temp Is Nothing Then MsgBox jobname & " Not found"
    Exit Sub
    End If
    </code>

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: lookin for data in previous worksheets (excel/vba 2003)

    Your declaration

    Dim temp As String

    is incorrect, it should be

    Dim temp As Range

    The result of the Find method is a range object - either the cell where the search value was found, or Nothing if the search value was not found.

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

    Re: lookin for data in previous worksheets (excel/vba 2003)

    ok hans i changed it to range and the error went away, only problem is that it's not finding the jobname in the previous months worksheet (feb2008).

    did I do something wrong?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: lookin for data in previous worksheets (excel/vba 2003)

    The last part of the code that you posted should probably be

    If temp Is Nothing Then
    MsgBox jobname & " Not found"
    Exit Sub
    End If

    With that modification it works OK for me.

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

    Re: lookin for data in previous worksheets (excel/vba 2003)

    it's flagging this

    <code>
    Set temp = ws.Columns(2).Find(What:=jobname)
    </code>

    temp is nothing and jobname is the jobname i'm looking for.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: lookin for data in previous worksheets (excel/vba 2003)

    Did you change the declaration of temp?

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

    Re: lookin for data in previous worksheets (excel/vba 2003)

    ok, this is what's happening. If I comment out the following code

    <code>
    Set ws = Worksheets("MAR2008")
    </code>

    I still get that error (even after fixing it like you suggested)

    if I don't comment out that line of code I get an error on this line of code

    <code>
    Set ws = Worksheets(Format(DateSerial(Year(Date), iMonth, 1), "MMMYYYY"))
    </code>

    any ideas on what i'm doing incorrectly?

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

    Re: lookin for data in previous worksheets (excel/vba 2003)

    this is my screwy code for the release of jobs....

    <code>
    Option Explicit

    Private Sub cmdRJFH_Click()
    Dim myDate As Date, newDate As Date, oldSheet As String, newSheet As String, irow As Integer
    Dim ws As Worksheet
    Set ws = Worksheets("MAR2008")
    Dim iMonth As Integer
    Dim jobname As String
    Dim temp As Range

    If Trim(Me.txtRLJname.Value) = "" Then
    Me.txtRLJname.SetFocus
    MsgBox "Please enter the Job name that has been requested to be taken off hold!"
    Exit Sub
    End If
    '************************************************* *********************************
    '+++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++++++++++
    '************************************************* *********************************
    Dim Found As Range, FOUND1 As Range, found2 As Range, found3 As Range

    jobname = frmRLSEHOLD.txtRLJname.Text
    frmRLSEHOLD.Hide
    Set temp = ws.Columns(2).Find(What:=jobname)
    If temp Is Nothing Then iMonth = Month(Date) - 1
    Do While iMonth > 0 And temp Is Nothing
    Set ws = Worksheets(Format(DateSerial(Year(Date), iMonth, 1), "MMMYYYY"))
    Set temp = ws.Columns(2).Find(What:=jobname)
    iMonth = iMonth - 1
    Loop

    If temp Is Nothing Then
    MsgBox jobname & " Not found"
    Exit Sub
    End If

    If temp <> "" And temp.Offset(0, 8).Value <> "" Then
    ' It ain't this one
    Set Found = ws.Columns(2).FindNext(after:=temp)
    Else
    Set Found = temp
    End If
    If Found <> "" And Found.Offset(0, 8).Value <> "" Then
    ' It ain't this one
    Set FOUND1 = ws.Columns(2).FindNext(after:=Found)
    Else
    Set FOUND1 = Found
    End If
    If FOUND1 <> "" And FOUND1.Offset(0, 8).Value <> "" Then
    ' It ain't this one
    Set found2 = ws.Columns(2).FindNext(after:=FOUND1)
    Else
    Set found2 = FOUND1
    End If
    If found2 <> "" And found2.Offset(0, 8).Value <> "" Then
    ' It ain't this one
    Set found3 = ws.Columns(2).FindNext(after:=found2)
    If temp Is Nothing Then
    MsgBox jobname & " Not found"
    Exit Sub
    End If
    Else
    ' Set found3 = found2
    End If


    irow = Found.Row
    irow = FOUND1.Row
    irow = found2.Row
    irow = found3.Row


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


    'clear the data
    Me.txtRLJname.Value = ""
    Me.txtNop2.Value = ""
    Me.txtCOMments.Value = ""
    Me.txtRLJname.SetFocus

    MsgBox " DATABASE HAS BEEN UPDATED WITH YOUR RELEASE INFORMATION"


    End Sub
    Private Sub cmdCTF_Click()
    'clear the data
    Me.txtRLJname.Value = ""
    Me.txtNop2.Value = ""
    Me.txtCOMments.Value = ""
    Me.txtRLJname.SetFocus
    End Sub
    Private Sub cmdExit_Click()
    Unload Me
    MsgBox "THANK YOU, PLEASE COME AGAIN"

    End Sub
    Private Sub UserForm_QueryClose(Cancel As Integer, _
    CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
    Cancel = True
    MsgBox "Please use the exit button!"
    End If
    End Sub

    </code>

    i'm pulling my hair out on this dang project... everytime I get it the way they want they freakin change it on me. I'm about to tell them to shove it up their ...(expletive)

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: lookin for data in previous worksheets (excel/vba 2003)

    There is an error in your code but that shouldn't cause this message. It seems as if the declaration

    Dim ws As Worksheet

    isn't working correctly. Does any of the worksheets, modules, userforms or variables in your workbook have the name Worksheet? That would confuse VBA.

Page 1 of 2 12 LastLast

Posting Permissions

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