Retrieve Value of Cell Using VBA, using functionality similar to Indirect Function in Excel

[bpfeffer]

Hi All,
I have a main spreadsheet that contains a column listing cell references that appear on a worksheet in a 2nd spreadsheets. So the columns of data appears with the following
A1 = B55
A2 = B66
A3 = B44
I have written VBA code to pull the cell references from cells A1 to A3 in the main spreadsheet. I am then trying to write code that pulls the value in the 2nd spreadsheet based on the reference text string as pulled from the main spreadsheet. So I want to pull in the value of cell B55 in the second spreadsheet. I know this is easy to do in Excel using the INDIRECT worksheet function but I am not sure how to do it in Excel VBA.

Dim Cell Value as string
Dim Actual_Value_Second_File as String
CellValue= Sheets("Main").Range("A1")).Value
Actual_Value_Second_File = Sheets("Second").Range(CellValue).Value

Where A1 = B55 in the Main 1st spreadsheet
and I am tryiing to find the value in the 2nd spreadsheet in Cell B55 on the set Second

Any suggestons? I greatly appreciate any help.

 

[Marcelo Branco]

Hi,

Welcome to the forum!

It seems you have a wrong parenthesis in this code-line
CellValue= Sheets("Main").Range("A1")).Value

Try this
CellValue= Sheets("Main").Range("A1").Value

HTH

M.

 

[bpfeffer]

Marcelo,
Thanks for responding. The extra ) was a typo when I was simplying some of my original code for the posting and is an obvious error. Please refer back to my original post as you solution does not solve my underlying problem. How do I in VBA use a string whose value is B55 in my main spreadsheet to reference that cell in a second spreadsheet?
Thanks,
BP

 

[Marcelo Branco]

This worked for me with
Main!A1 = B55
and
Second!B55 = "blahblah"

Sub test()
    Dim CellValue As String
    Dim Actual_Value_Second_File As String
    
    CellValue = Sheets("Main").Range("A1").Value
    Actual_Value_Second_File = Sheets("Second").Range(CellValue).Value
    MsgBox Actual_Value_Second_File
    
End Sub

HTH

M.

 

[Peter_SSs]

Welcome to the MrExcel board!

Apart from the ')' typo already referred to, and reomoving the unwanted space in 'Dim Cell Value as string' to become 'Dim CellValue As String' your code appears to work for me. In what way is it not working for you?

 

[mikerickson]

The OP code (less the typo) looks like it should work. What happens when you run it?

 

[repairman615]

Hello,
Welcome to the Message Board!

Works here too.

Just a guess,
??error 9 subscript out of range?? .... Be sure your sheet names are exact and do not contain leading or trailing spaces and spelling is correct.

 

[bpfeffer]

Hi All,
Thanks for the help! I had dumbed down my actual code when making things simple so I could explain to the group. And when I did so I made the ) and extra space type-o-s. In reality, after looking at the simple example that you gave I realized that I had a problem with the data in the 2nd spreadsheet which was causing my problem. Thank you all again for all of your help and time.
Best,
Brandon

 

[Marcelo Branco]

You are welcome and tks for the feedback

M.