VBA Hint Needed - Change Way An Existing Project Grabs Data



  • I know it is difficult to seek development help but perhaps a little boost in the right direction could help.

    We have a spreadsheet that looks like this:

    0_1464371789886_platebar01.jpg

    I added the column W when I started to work on this project and will explain below...

    Basically this sheet grabs data (job numbers) from another sheet according to a date range and calculates them with other subroutines and places those job numbers in the cells starting at C2. For the sake of this post, no need to worry about all those other cells because if I can just get this started, the rest will fall into place.

    We want to eliminate the need to look at that other sheet based upon date range to get the job numbers and just have the macro (RUN REPORT, top left) grab the "jobs" listed in column W that the user will manually enter.

    Just don't know how to tell it, with VBA, to take each job number in column W and do what it normally did the other way and then put a warning up if it doesn't match exactly and to STOP when it teaches a null cell.

    Any suggestions on where to find some examples of this? If I can just get this part going, I am fairly confident I can do the rest. I can post the existing subroutines code if needed.

    Thanks so much!



  • How about something like this?... Keep in mind, you'll need to code around blank boxes, etc...

    Dim myRange As Range
    
    Set myRange = Sheet1.Range("W1", "W5")
    
    For Each ritem In myRange
     msgbox (ritem.Value)
     
    Next
    

    In the Code Above, Sheet1 is the name of the worksheet. If the Sheet has a space in its name, you may have to get the Sheet1 object a different way.


  • Service Provider

    yeah, you need to look at the existing code and replace the bit that looks at the other sheet with something like what @dafyre gave you.



  • Here's the original Subroutine and Function it originally ran...as stated, want to rid the method of going to another sheet to pull job numbers by date and rather get them from column W and then it will continue to do the same arrays and calculations as it did before...

    Sub IMPORT_ALL_INFORMATION()

    'Set variables
    Dim file_in As Long
    Dim strInput As Variant
    Dim i As Integer
    Dim j As Integer
    Dim l As Integer
    Dim sTmp As String
    Dim sJob As String
    Dim sSchedPath As String

    'end setting variables
    Sheets("REPORT").Select
    Range("C2").Select

    sSchedPath = "C:\Temp"
    Call apiCopyFile("\servername\Applications\Schedule\schedule-s\schedule, S.xls", "C:\Temp\schedule, S.xls", 0)
    dteStart = Application.Sheets("Report").Range("$G$27").Value
    dteEnd = Application.Sheets("Report").Range("$J$27").Value
    l = 4 ' First data row of schedule, S.xls
    j = 2 ' First job row of Plate & Bar Spreadsheet
    Do Until CDate(GetDate(GetValue(sSchedPath, "schedule, S.xls", "LOG (2)", "N" & CStr(l)))) >= dteStart
    l = l + 1
    ' Changed from 754 to 854...may be total jobs for year...went close to 800 jobs this year . BAW
    If l = 854 Then
    MsgBox ("Hello")
    End If
    sTmp = Trim$(GetValue(sSchedPath, "schedule, S.xls", "LOG (2)", "N" & CStr(l)))
    If sTmp = "0" Or Len(sTmp) = 0 Or sTmp = "HOLIDAY" Then
    l = l + 1
    End If
    Loop
    Do
    sJob = ParseJob(GetValue(sSchedPath, "schedule, S.xls", "LOG (2)", "B" & CStr(l)))
    ' Debug.Print sJob
    vJobFolders = Split(FindJobDir(strpathtofile & sJob), ",")
    For i = 0 To UBound(vJobFolders)
    On Error GoTo ErrorExit
    Application.Sheets("report").Range("C" & CStr(j)).Value = vJobFolders(i)
    j = j + 1
    file_in = FreeFile 'file number
    strFileToOpen = strpathtofile & vJobFolders(i) & strFilename
    If Dir(strFileToOpen) <> "" Then
    Open strFileToOpen For Input As #file_in
    Put_Data_In_Array (file_in)
    Organize_Array_For_Print
    Close #file_in ' close the file
    End If
    ErrorExit:
    Next i
    l = l + 1
    sTmp = Trim$(GetValue(sSchedPath, "schedule, S.xls", "LOG (2)", "N" & CStr(l)))
    If sTmp = "0" Or Len(sTmp) = 0 Or sTmp = "HOLIDAY" Then
    l = l + 1
    End If
    Loop Until CDate(GetDate(sTmp)) >= dteEnd
    Sheets("REPORT").Select

    End Sub

    Function GetValue(path, file, sheet, ref) As String
    ' Retrieves a value from a closed workbook
    Dim arg As String
    Dim pos As Integer
    ' Make sure the file exists
    If Right(path, 1) <> "" Then path = path & ""
    If Dir(path & file) = "" Then
    GetValue = "File Not Found"
    Exit Function
    End If
    ' Create the argument
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
    Range(ref).Range("A1").Address(, , xlR1C1)
    ' Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
    ' Strip Any time from beginning of Date string
    pos = InStr(GetValue, ":")
    If pos <> 0 Then GetValue = Mid$(GetValue, pos + 3)
    End Function



  • Easier to read:

    Sub IMPORT_ALL_INFORMATION()
        REM Set variables
        Dim file_in As Long
        Dim strInput As Variant
        Dim i As Integer
        Dim j As Integer
        Dim l As Integer
        Dim sTmp As String
        Dim sJob As String
        Dim sSchedPath As String
    
        REM end setting variables
        Sheets("REPORT").Select
        Range("C2").Select
    
        sSchedPath = "C:\Temp"
        Call apiCopyFile("\servername\Applications\Schedule\schedule-s\schedule, S.xls", "C:\Temp\schedule, S.xls", 0)
        dteStart = Application.Sheets("Report").Range("$G$27").Value
        dteEnd = Application.Sheets("Report").Range("$J$27").Value
        
        REM    First data row of schedule, S.xls
        l = 4 
        
        REM    First job row of Plate & Bar Spreadsheet
        j = 2 
    
        Do Until CDate(GetDate(GetValue(sSchedPath, "schedule, S.xls", "LOG (2)", "N" & CStr(l)))) >= dteStart
            l = l + 1
            
            REM    Changed from 754 to 854...may be total jobs for year...went close to 800 jobs this year . BAW
            If l = 854 Then
                MsgBox ("Hello")
            End If
    
            sTmp = Trim$(GetValue(sSchedPath, "schedule, S.xls", "LOG (2)", "N" & CStr(l)))
    
            If sTmp = "0" Or Len(sTmp) = 0 Or sTmp = "HOLIDAY" Then
                l = l + 1
            End If
        Loop
    
        Do
            sJob = ParseJob(GetValue(sSchedPath, "schedule, S.xls", "LOG (2)", "B" & CStr(l)))
            REM    Debug.Print sJob
            vJobFolders = Split(FindJobDir(strpathtofile & sJob), ",")
            For i = 0 To UBound(vJobFolders)
                On Error GoTo ErrorExit
                Application.Sheets("report").Range("C" & CStr(j)).Value = vJobFolders(i)
                j = j + 1
                
                REM file number
                file_in = FreeFile 
                
                strFileToOpen = strpathtofile & vJobFolders(i) & strFilename
    
                If Dir(strFileToOpen) <> "" Then
                    Open strFileToOpen For Input As #file_in
                    Put_Data_In_Array (file_in)
                    Organize_Array_For_Print
                    Close #file_in REM    close the file
                End If
                ErrorExit:
    
            Next i
    
            l = l + 1
            sTmp = Trim$(GetValue(sSchedPath, "schedule, S.xls", "LOG (2)", "N" & CStr(l)))
            If sTmp = "0" Or Len(sTmp) = 0 Or sTmp = "HOLIDAY" Then
                l = l + 1
            End If
            
        Loop Until CDate(GetDate(sTmp)) >= dteEnd
        Sheets("REPORT").Select
    
    End Sub
    
    Function GetValue(path, file, sheet, ref) As String
        REM    Retrieves a value from a closed workbook
        Dim arg As String
        Dim pos As Integer
        
        REM    Make sure the file exists
        If Right(path, 1) <> "" Then 
            path = path & ""
        End If
        
        If Dir(path & file) = "" Then
            GetValue = "File Not Found"
            Exit Function
        End If
        
        REM    Create the argument
        arg = "REM " & path & "[" & file & "]" & sheet & "REM !" & _
        Range(ref).Range("A1").Address(, , xlR1C1)
        REM    Execute an XLM macro
        GetValue = ExecuteExcel4Macro(arg)
        REM    Strip Any time from beginning of Date string
        pos = InStr(GetValue, ":")
        If pos <> 0 Then GetValue = Mid$(GetValue, pos + 3)
    End Function


  • @garak0410 Just don't know how to tell it, with VBA, to take each job number in column W and do what it normally did the other way and then put a warning up if it doesn't match exactly and to STOP when it teaches a null cell.

    Little helper function

    Function IsNullOrEmpty(val as String) as Boolean
      REM Strings are always initialized as "" in VB/VBA/VBS, so checking for Null is redundant. At least In theory.
      If (val is Null or val = "") Then
        IsNullOrEmpty = True
      Else
        IsNullOrEmpty = False
      End If
    End Function
    

    To process your values (like @dafyre suggested):

    Dim myRange As Range
    
    Set myRange = Sheet1.Range("W1", "W5")
    
    For Each ritem In myRange
      If (IsNullOrEmpty(ritem.Value)) Then 
        REM Empty string, processing finished
        Exit For
      Else
        REM Do something here with ritem.Value
      End If
    Next
    

    The original script is a bit... ugly. Could be easier to have the XLS with test data only.



  • Maybe I am just thinking a little simplistic, but was thinking that this could be all I need, after commenting out the code that goes to the other sheet:

    sJob = Report.Range("W2:W50").Select(CStr(l))

    But alas, it doesn't work...



  • @garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    Maybe I am just thinking a little simplistic, but was thinking that this could be all I need, after commenting out the code that goes to the other sheet:

    sJob = Report.Range("W2:W50").Select(CStr(l))

    But alas, it doesn't work...

    I am going to play around with variations of this but each time I change something, new errors (mostly syntax) pop up...it is maddening.



  • @garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    @garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    Maybe I am just thinking a little simplistic, but was thinking that this could be all I need, after commenting out the code that goes to the other sheet:

    sJob = Report.Range("W2:W50").Select(CStr(l))

    But alas, it doesn't work...

    I am going to play around with variations of this but each time I change something, new errors (mostly syntax) pop up...it is maddening.

    Welcome to the world of software devleopment, lol.

    @thwr is right. I'd take a little while to clean up the code and make it more readable (see his code post)... and then go back to make your changes.

    Are all of these jobs separated out into their own Excel files (or are they some other type of file that we don't care about?)



  • @dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    @garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    @garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    Maybe I am just thinking a little simplistic, but was thinking that this could be all I need, after commenting out the code that goes to the other sheet:

    sJob = Report.Range("W2:W50").Select(CStr(l))

    But alas, it doesn't work...

    I am going to play around with variations of this but each time I change something, new errors (mostly syntax) pop up...it is maddening.

    Welcome to the world of software devleopment, lol.

    @thwr is right. I'd take a little while to clean up the code and make it more readable (see his code post)... and then go back to make your changes.

    Are all of these jobs separated out into their own Excel files (or are they some other type of file that we don't care about?)

    The "jobs" are metal building jobs that are in folders on a network share. The folders the "job numbers." When searching for these jobs, it goes out to these job folders, finds a file called o PltSum.out and then takes that data and calculates the needed materials to order for the week. That calculation isn't my focus as that should still work if I get this to work differently.



  • In my weeks long struggle with this, I did at least try somethiing...I commented out all of the lines that grabbed the data from the other sheet (see code in posts above) and made a varient called rcell and tried this line of code:

    For Each rcell In Worksheets("Sheet1").Range("W2:W50"): Debug.Print rcell.Value: Next rcell
    sJob = rcell.Value

    I keep getting SUBSCRIPT OUT OF RANGE.

    I guess this is a lack of my own education mixed with bad coding in the first place...



  • @garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    In my weeks long struggle with this, I did at least try somethiing...I commented out all of the lines that grabbed the data from the other sheet (see code in posts above) and made a varient called rcell and tried this line of code:

    For Each rcell In Worksheets("Sheet1").Range("W2:W50"): Debug.Print rcell.Value: Next rcell
    sJob = rcell.Value

    I keep getting SUBSCRIPT OUT OF RANGE.

    I guess this is a lack of my own education mixed with bad coding in the first place...

    A little formatting my help you see the problem...

    For Each rcell In Worksheets("Sheet1").Range("W2:W50")
     Debug.Print rcell.Value: 
    Next rcell
    
    sJob = rcell.Value
    

    It looks like you're trying to assing sJob after you come out of the For Each loop... Try:

    For Each rcell In Worksheets("Sheet1").Range("W2:W50")
     Debug.Print rcell.Value: 
     sJob = rcell.Value
    
    Next rcell
    


  • @dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    @garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    In my weeks long struggle with this, I did at least try somethiing...I commented out all of the lines that grabbed the data from the other sheet (see code in posts above) and made a varient called rcell and tried this line of code:

    For Each rcell In Worksheets("Sheet1").Range("W2:W50"): Debug.Print rcell.Value: Next rcell
    sJob = rcell.Value

    I keep getting SUBSCRIPT OUT OF RANGE.

    I guess this is a lack of my own education mixed with bad coding in the first place...

    A little formatting my help you see the problem...

    For Each rcell In Worksheets("Sheet1").Range("W2:W50")
     Debug.Print rcell.Value: 
    Next rcell
    
    sJob = rcell.Value
    

    It looks like you're trying to assing sJob after you come out of the For Each loop... Try:

    For Each rcell In Worksheets("Sheet1").Range("W2:W50")
     Debug.Print rcell.Value: 
     sJob = rcell.Value
    
    Next rcell
    

    OK...I am giving this a shot...I am at the point in the old code where I see when SJOB is equal to the value of the job on the date range on the other spreadsheet. Since I am moving these jobs to manually entered job numbers in Column W, will se how this works. Curretly, SJOB is equal to: sJob = ParseJob(GetValue(sSchedPath, "Schedule S.xls", "LOG (2)", "B" & CStr(l)))



  • @garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    @dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    @garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    In my weeks long struggle with this, I did at least try somethiing...I commented out all of the lines that grabbed the data from the other sheet (see code in posts above) and made a varient called rcell and tried this line of code:

    For Each rcell In Worksheets("Sheet1").Range("W2:W50"): Debug.Print rcell.Value: Next rcell
    sJob = rcell.Value

    I keep getting SUBSCRIPT OUT OF RANGE.

    I guess this is a lack of my own education mixed with bad coding in the first place...

    A little formatting my help you see the problem...

    For Each rcell In Worksheets("Sheet1").Range("W2:W50")
     Debug.Print rcell.Value: 
    Next rcell
    
    sJob = rcell.Value
    

    It looks like you're trying to assing sJob after you come out of the For Each loop... Try:

    For Each rcell In Worksheets("Sheet1").Range("W2:W50")
     Debug.Print rcell.Value: 
     sJob = rcell.Value
    
    Next rcell
    

    OK...I am giving this a shot...I am at the point in the old code where I see when SJOB is equal to the value of the job on the date range on the other spreadsheet. Since I am moving these jobs to manually entered job numbers in Column W, will se how this works. Curretly, SJOB is equal to: sJob = ParseJob(GetValue(sSchedPath, "Schedule S.xls", "LOG (2)", "B" & CStr(l)))

    sJob=ParaseJob(rcell.value) ?



  • @dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    @garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    @dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    @garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    In my weeks long struggle with this, I did at least try somethiing...I commented out all of the lines that grabbed the data from the other sheet (see code in posts above) and made a varient called rcell and tried this line of code:

    For Each rcell In Worksheets("Sheet1").Range("W2:W50"): Debug.Print rcell.Value: Next rcell
    sJob = rcell.Value

    I keep getting SUBSCRIPT OUT OF RANGE.

    I guess this is a lack of my own education mixed with bad coding in the first place...

    A little formatting my help you see the problem...

    For Each rcell In Worksheets("Sheet1").Range("W2:W50")
     Debug.Print rcell.Value: 
    Next rcell
    
    sJob = rcell.Value
    

    It looks like you're trying to assing sJob after you come out of the For Each loop... Try:

    For Each rcell In Worksheets("Sheet1").Range("W2:W50")
     Debug.Print rcell.Value: 
     sJob = rcell.Value
    
    Next rcell
    

    OK...I am giving this a shot...I am at the point in the old code where I see when SJOB is equal to the value of the job on the date range on the other spreadsheet. Since I am moving these jobs to manually entered job numbers in Column W, will se how this works. Curretly, SJOB is equal to: sJob = ParseJob(GetValue(sSchedPath, "Schedule S.xls", "LOG (2)", "B" & CStr(l)))

    sJob=ParaseJob(rcell.value) ?

    The more I work on this (uninterrupted from day to day issues that I am NOT getting today, wooo!), the more I think I am "getting it."...I don't think we need ParseJob because what that did before was remove spaces in the job numbers that were lisited on Schedule, S. So I just need it to be rcell.value...about to code and see what happens.



  • @garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    @dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    @garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    @dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    @garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    In my weeks long struggle with this, I did at least try somethiing...I commented out all of the lines that grabbed the data from the other sheet (see code in posts above) and made a varient called rcell and tried this line of code:

    For Each rcell In Worksheets("Sheet1").Range("W2:W50"): Debug.Print rcell.Value: Next rcell
    sJob = rcell.Value

    I keep getting SUBSCRIPT OUT OF RANGE.

    I guess this is a lack of my own education mixed with bad coding in the first place...

    A little formatting my help you see the problem...

    For Each rcell In Worksheets("Sheet1").Range("W2:W50")
     Debug.Print rcell.Value: 
    Next rcell
    
    sJob = rcell.Value
    

    It looks like you're trying to assing sJob after you come out of the For Each loop... Try:

    For Each rcell In Worksheets("Sheet1").Range("W2:W50")
     Debug.Print rcell.Value: 
     sJob = rcell.Value
    
    Next rcell
    

    OK...I am giving this a shot...I am at the point in the old code where I see when SJOB is equal to the value of the job on the date range on the other spreadsheet. Since I am moving these jobs to manually entered job numbers in Column W, will se how this works. Curretly, SJOB is equal to: sJob = ParseJob(GetValue(sSchedPath, "Schedule S.xls", "LOG (2)", "B" & CStr(l)))

    sJob=ParaseJob(rcell.value) ?

    The more I work on this (uninterrupted from day to day issues that I am NOT getting today, wooo!), the more I think I am "getting it."...I don't think we need ParseJob because what that did before was remove spaces in the job numbers that were lisited on Schedule, S. So I just need it to be rcell.value...about to code and see what happens.

    puts on hard hat...

    Here... Hold my beer?



  • @dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    @garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    @dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    @garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    @dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    @garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    In my weeks long struggle with this, I did at least try somethiing...I commented out all of the lines that grabbed the data from the other sheet (see code in posts above) and made a varient called rcell and tried this line of code:

    For Each rcell In Worksheets("Sheet1").Range("W2:W50"): Debug.Print rcell.Value: Next rcell
    sJob = rcell.Value

    I keep getting SUBSCRIPT OUT OF RANGE.

    I guess this is a lack of my own education mixed with bad coding in the first place...

    A little formatting my help you see the problem...

    For Each rcell In Worksheets("Sheet1").Range("W2:W50")
     Debug.Print rcell.Value: 
    Next rcell
    
    sJob = rcell.Value
    

    It looks like you're trying to assing sJob after you come out of the For Each loop... Try:

    For Each rcell In Worksheets("Sheet1").Range("W2:W50")
     Debug.Print rcell.Value: 
     sJob = rcell.Value
    
    Next rcell
    

    OK...I am giving this a shot...I am at the point in the old code where I see when SJOB is equal to the value of the job on the date range on the other spreadsheet. Since I am moving these jobs to manually entered job numbers in Column W, will se how this works. Curretly, SJOB is equal to: sJob = ParseJob(GetValue(sSchedPath, "Schedule S.xls", "LOG (2)", "B" & CStr(l)))

    sJob=ParaseJob(rcell.value) ?

    The more I work on this (uninterrupted from day to day issues that I am NOT getting today, wooo!), the more I think I am "getting it."...I don't think we need ParseJob because what that did before was remove spaces in the job numbers that were lisited on Schedule, S. So I just need it to be rcell.value...about to code and see what happens.

    puts on hard hat...

    Here... Hold my beer?

    Funny...it is amazing what a closed door and no distractions can do to your focus and clarity...and maybe "beer."... 🙂



  • @garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    @dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    @garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    @dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    @garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    @dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    @garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    In my weeks long struggle with this, I did at least try somethiing...I commented out all of the lines that grabbed the data from the other sheet (see code in posts above) and made a varient called rcell and tried this line of code:

    For Each rcell In Worksheets("Sheet1").Range("W2:W50"): Debug.Print rcell.Value: Next rcell
    sJob = rcell.Value

    I keep getting SUBSCRIPT OUT OF RANGE.

    I guess this is a lack of my own education mixed with bad coding in the first place...

    A little formatting my help you see the problem...

    For Each rcell In Worksheets("Sheet1").Range("W2:W50")
     Debug.Print rcell.Value: 
    Next rcell
    
    sJob = rcell.Value
    

    It looks like you're trying to assing sJob after you come out of the For Each loop... Try:

    For Each rcell In Worksheets("Sheet1").Range("W2:W50")
     Debug.Print rcell.Value: 
     sJob = rcell.Value
    
    Next rcell
    

    OK...I am giving this a shot...I am at the point in the old code where I see when SJOB is equal to the value of the job on the date range on the other spreadsheet. Since I am moving these jobs to manually entered job numbers in Column W, will se how this works. Curretly, SJOB is equal to: sJob = ParseJob(GetValue(sSchedPath, "Schedule S.xls", "LOG (2)", "B" & CStr(l)))

    sJob=ParaseJob(rcell.value) ?

    The more I work on this (uninterrupted from day to day issues that I am NOT getting today, wooo!), the more I think I am "getting it."...I don't think we need ParseJob because what that did before was remove spaces in the job numbers that were lisited on Schedule, S. So I just need it to be rcell.value...about to code and see what happens.

    puts on hard hat...

    Here... Hold my beer?

    Funny...it is amazing what a closed door and no distractions can do to your focus and clarity...and maybe "beer."... 🙂

    Is debug.print rCell.value showing anything in the immediate window (CTRL G or View -> Immediate window) ?



  • @garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    I'm making progress even if still getting errors...

    I did put job numbers in rows W2 and W3. SJOB is still a null value even after this:

    For Each rCell In Worksheets("REPORT").Range("W2:W50")
    Debug.Print rCell.Value:
    sJob = rCell.Value
    Next rCell

    And some how, it is still pulling data from Schedule, S and putting it in Column C...I am trying to track that down...

    While debugging, it DOES pull from column W and I see SJOB being assigned a job number both by hovering over the variable and in the intermediate window. It then does the same for the next job number (only entered two for this example, cell W2 and W3. The intermediate Window does show both jobs:

    0_1465916853111_vba10.jpg

    But it is not holding the SJOB data as it eventually goes null...

    When it gets to this part:

    Function FindJobDir(ByVal strPath As String) As String
    Dim sResult As String

    sResult = Dir(strPath & "*", vbDirectory)
    FindJobDir = UCase$(sResult)
    Do While sResult <> ""
    sResult = Dir
    If Len(sResult) > 0 Then FindJobDir = FindJobDir & "," & UCase$(sResult)
    Loop
    End Function

    It just starts grabbing all job folder information from the server location...luckily it fails when it hits a ZIP folder otherwise it would have been a data overrun...

    When it is executing this line: vJobFolders = Split(FindJobDir(strpathtofile & sJob), ",")

    SJOB Shows Null like this:

    0_1465917979827_vba11.jpg



  • That is right. The way the code works, is it just prints the rcell.value to the screen, sets SJOB =rcell.value, and then at the Next rCell line, it goes to the next cell. It's not actually doing anything beneath that.

    The trick will be to comment out the "Next rCell" line

    and find out where the end of the process is, and then on the next line add next rCell and see if that works.

    If this thing is actively modifying files and such, I'd hightly recommend backing everything up before running it while you are working on that part. 🙂



  • @dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    That is right. The way the code works, is it just prints the rcell.value to the screen, sets SJOB =rcell.value, and then at the Next rCell line, it goes to the next cell. It's not actually doing anything beneath that.

    The trick will be to comment out the "Next rCell" line

    and find out where the end of the process is, and then on the next line add next rCell and see if that works.

    If this thing is actively modifying files and such, I'd hightly recommend backing everything up before running it while you are working on that part. 🙂

    Question...if I comment out the Next rCell line to test this, then what do I do with the FOR line? It will tell me there is a FOR without a NEXT.



  • @garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    @dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    That is right. The way the code works, is it just prints the rcell.value to the screen, sets SJOB =rcell.value, and then at the Next rCell line, it goes to the next cell. It's not actually doing anything beneath that.

    The trick will be to comment out the "Next rCell" line

    and find out where the end of the process is, and then on the next line add next rCell and see if that works.

    If this thing is actively modifying files and such, I'd hightly recommend backing everything up before running it while you are working on that part. 🙂

    Question...if I comment out the Next rCell line to test this, then what do I do with the FOR line? It will tell me there is a FOR without a NEXT.

    Yeah. You need to read through the code and figure out where to put that Next rCell statement.

    It is most likely near the end of the code... but look and see where it looks like everything is done, and put the Next statement there.



  • @dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    @garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    @dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    That is right. The way the code works, is it just prints the rcell.value to the screen, sets SJOB =rcell.value, and then at the Next rCell line, it goes to the next cell. It's not actually doing anything beneath that.

    The trick will be to comment out the "Next rCell" line

    and find out where the end of the process is, and then on the next line add next rCell and see if that works.

    If this thing is actively modifying files and such, I'd hightly recommend backing everything up before running it while you are working on that part. 🙂

    Question...if I comment out the Next rCell line to test this, then what do I do with the FOR line? It will tell me there is a FOR without a NEXT.

    Yeah. You need to read through the code and figure out where to put that Next rCell statement.

    It is most likely near the end of the code... but look and see where it looks like everything is done, and put the Next statement there.

    Gotcha...call me nuts but this is kind of fun...learning a lot and trying not to get frustrated at this crazy code...



  • @garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    @dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    @garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    @dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    That is right. The way the code works, is it just prints the rcell.value to the screen, sets SJOB =rcell.value, and then at the Next rCell line, it goes to the next cell. It's not actually doing anything beneath that.

    The trick will be to comment out the "Next rCell" line

    and find out where the end of the process is, and then on the next line add next rCell and see if that works.

    If this thing is actively modifying files and such, I'd hightly recommend backing everything up before running it while you are working on that part. 🙂

    Question...if I comment out the Next rCell line to test this, then what do I do with the FOR line? It will tell me there is a FOR without a NEXT.

    Yeah. You need to read through the code and figure out where to put that Next rCell statement.

    It is most likely near the end of the code... but look and see where it looks like everything is done, and put the Next statement there.

    Gotcha...call me nuts but this is kind of fun...learning a lot and trying not to get frustrated at this crazy code...

    Welcome to the life of software development! lol. I actually enjoy it too.



  • Yeah, this one will be a toughie because it won't even let me step through past a break-point with this error:
    0_1465927901497_vba12.jpg



  • @garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    Yeah, this one will be a toughie because it won't even let me step through past a break-point with this error:
    0_1465927901497_vba12.jpg

    You may have to resort to moving the Next rCell down a few lines at the time until you find the right spot... following OPC's can be tough if it's not nicely written... (OPC = other people's code).



  • I've dusted off this project because, well, the time has truly come where they want the bulk of my time in development.

    So, through some suggestions and research, I have created this part of the code:

    For Each rCell In Worksheets("REPORT").Range("W2:W50")
    Debug.Print rCell.Value:
    sJob = rCell.Value

    It grabs the data I want it to grab in column W.

    Shortly after that, it kicks off a function that is in this block of code:

    vJobFolders = Split(FindJobDir(strpathtofile & sJob), ",")
    For i = 0 To UBound(vJobFolders)

    And that function, called FindJobDir, looks like this:

    Function FindJobDir(ByVal strPath As String) As String
    Dim sResult As String

    sResult = Dir(strPath & "*", vbDirectory)
    FindJobDir = UCase$(sResult)
    Do While sResult <> ""
    sResult = Dir
    If Len(sResult) > 0 Then FindJobDir = FindJobDir & "," & UCase$(sResult)
    Loop
    End Function

    Now, this works fine as long as there is data in Column W but once it is null or empty, it proceed to pull ALL jobs from the path/directory.

    What I need it to do, is somewhere, tell it to stop once there is no more data (null/empty cell) in the Column W range...any suggestions?



  • if rCell.value = "" or rCell.value is null then end ?

    edit: Right above or below sJob = rCell.value ?



  • @dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    if rCell.value = "" or rCell.value is null then end ?

    Let me give it a shot... 🎬



  • @garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    @dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

    if rCell.value = "" or rCell.value is null then end ?

    Let me give it a shot... 🎬

    Interesting...I now get a NEXT without a FOR error...on this line:
    Next rCell
    Sheets("REPORT").Select


Log in to reply
 

Looks like your connection to MangoLassi was lost, please wait while we try to reconnect.