Referencing in VBA excel

I am trying to have 2 for loops in order to get 2 different pieces of information from a different excel. The first for loops works fine there is no problems with it. I want the second for loop to use as a source a cell that is in a different excel (the first for loop uses the current workbook as a source cell). The problem is the name of the excel changes as shown in the path (Scorecard " & JobNumber & ".xlsm'). Do I need to create new variables and define that variable as the path to the new excel? Or is there a better way to make this work? Basically it should take a piece of information in a specific cell and populate it on another excel (the master excel).

Sub Narrative()

Dim JobNumber As String
Dim srcCell As String

Dim id As Integer

For id = 4 To 150
    srcCell = "C" & id

    JobNumber = Range(srcCell).Value

    Range("P" & id).Value = "=IFERROR('Y:\Public\QA Other\Scorecards\Scorecard " & JobNumber & ".xlsm'!TotalN, "" "")"
Next

For id = 4 To 150
    srcCell = "S" & id

    JobNumber = Range(srcCell).Value

    Range("R" & id).Value = "=IFERROR('Y:\Public\QA Other\Scorecards\Scorecard " & JobNumber & ".xlsm'!Analyst, "" "")"
Next

End Sub

2 answers

  • answered 2018-01-11 19:38 user2731076

    If you are looking for the JobNumber variable to pull information from a different workbook, you need to qualify which workbook and worksheet you are calling the Range method on. It defaults to the active workbook and worksheet. Try changing your second for loop to:

    For id = 4 To 150
        srcCell = "S" & id
    
        JobNumber = Workbooks(wbkName).Worksheets(shtName).Range(srcCell).Value
    
        Range("R" & id).Value = "=IFERROR('Y:\Public\QA Other\Scorecards\Scorecard " & JobNumber & ".xlsm'!Analyst, "" "")"
    Next
    

    where wbkName is a string variable with the workbook name (presumably "Scorecard " & JobNumber & ".xlsm") and shtName is a string variable containing the worksheet name in that workbook.

    I would suggest creating variables for each workbook/worksheet you are using and qualifying all of your Range methods.

  • answered 2018-01-11 19:38 YowE3K

    Based on your comments that column S does not contain the job number, that column S is simply the column in some other workbook that contains the range name Analyst, I believe you want your code to be:

    Sub Narrative()
    
        Dim JobNumber As String
        Dim srcCell As String
    
        Dim id As Long
    
        For id = 4 To 150
            'Obtain the "job number" from column C of the master sheet
            srcCell = "C" & id
            JobNumber = Range(srcCell).Value
            'Create formulas in column P and R to retrieve values from the 
            'individual job workbooks
            Range("P" & id).Value = "=IFERROR('Y:\Public\QA Other\Scorecards\Scorecard " & JobNumber & ".xlsm'!TotalN, "" "")"
            Range("R" & id).Value = "=IFERROR('Y:\Public\QA Other\Scorecards\Scorecard " & JobNumber & ".xlsm'!Analyst, "" "")"
        Next
    
    End Sub
    

    If you really want to have two loops (and there really is no need to do so!) then you would still need to get the job number from column C of the master sheet, i.e.

    Sub Narrative()
    
        Dim JobNumber As String
        Dim srcCell As String
    
        Dim id As Long
    
        For id = 4 To 150
            'Obtain the "job number" from column C of the master sheet
            srcCell = "C" & id
            JobNumber = Range(srcCell).Value
            'Create formula in column P to retrieve value from the 
            'individual job workbooks
            Range("P" & id).Value = "=IFERROR('Y:\Public\QA Other\Scorecards\Scorecard " & JobNumber & ".xlsm'!TotalN, "" "")"
        Next
        For id = 4 To 150
            'Obtain the "job number" from column C of the master sheet
            srcCell = "C" & id
            JobNumber = Range(srcCell).Value
            'Create formula in column R to retrieve value from the 
            'individual job workbooks
            Range("R" & id).Value = "=IFERROR('Y:\Public\QA Other\Scorecards\Scorecard " & JobNumber & ".xlsm'!Analyst, "" "")"
        Next
    
    End Sub