CODEWITHSUNDEEP
vbaexcel
Josh
Josh

Reputation: 77

Ranges in VBA excel

I am quite new to VBA and trying to create an excel template that uses VBA to change a path and automatically gather data from other workbooks. Basically creating a master document to store the information.

At the moment I am using the following code: 

Sub CallOtherWB()

  Dim JobNumber As String

  JobNumber = Range("C4").Value

  With Range("D4")
     .Formula = "='Y:\Public\QA Other\Scorecards\Copy of Scorecard v0.6 " & 
       JobNumber & ".xlsm'!Total"
 End With

End Sub

However I haven't figured out how to make the range go through C4:C50 and grab the data entered there to edit "='Y:\Public\QA Other\Scorecards\Copy of Scorecard v0.6 " & JobNumber & ".xlsm'!Total" which would go in D4:D50.

I searched through other questions and tried to do a sort of loop but was not able to understand how to make the range work. To summarize I am using that code to enter the information in the C column and for that information to replace the "JobNumber" part in the D column so that it has the correct file name.

Please let me know if I need to clarify this further and thanks in advance for the help. This is also my first question in Stackoverflow so I apologize for any mistakes.

Upvotes: 1

Views: 114

Answers (3)

Bharat Anand
Bharat Anand

Reputation: 484

The below code worked for me. 

Sub CallOtherWB()

Dim JobNumber As String
Dim srcCell As String

Dim id As Integer

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

        JobNumber = Range(srcCell).Value

        Range("D" & id).Value = "='C:\Users\banand\Desktop\test\[" & JobNumber & ".xlsx]Total'!D4"
    Next

End Sub

Trick here is to set the file path accurately. You can try replacing the file path as below and test to see if it works.

    Range("D4").Value = "='Y:\Public\QA Other\Scorecards\[Copy of Scorecard v0.6 " & JobNumber & ".xlsm]Total'!D4"

If the above doesn't work, you can try to key in the below value in a cell, D3 (or some other cell of your choice in the target excel) with just the complete filepath as below to see if the filepath part of the code is translating correctly.

    ='Y:\Public\QA Other\Scorecards\[Copy of Scorecard v0.6 " & JobNumber & ".xlsm]Total'!D4:D50"

Hope this helps!

Upvotes: 0

maciek
maciek

Reputation: 541

I believe a path is not correct, it should be like this (please see where are ' characters and exclamation mark):

='C:\path_to_directory\[file_name.xlsx]sheet_name'!A1

What you want to achieve can be done without VBA. Please have a look at INDIRECT function. You'll need to enter following formula in your D4 cell:

=INDIRECT("'Y:\Public\QA Other\Scorecards\[Copy of Scorecard v0.6 " & C4 & ".xlsm]Total'!A1"

Upvotes: 0

n8.
n8.

Reputation: 1738

Looping is overkill. By putting the value into a variable you are fixing it per cell. This is inefficient for your purposes.

Sub CallOtherWB()

  Range("D4:D50").FormulaR1C1= "='Y:\Public\QA Other\Scorecards\Copy of Scorecard v0.6 "" & RC[-1] & "".xlsm'!Total"

End Sub

Upvotes: 0

Related Questions