Referencing Cell Value Difficulty

[dhunton]

I am using a variable to reference the name of a sheet in my workbook. It works in one place, but not in another. I know (KNOW!) that I am missing something simple, but I'm not seeing it. Below are the appropriate lines, copied exactly from my VBA in case there are typos (but the error doesn't happen in compile, only when running).


SETUP SCRIPT:
Dim lastrow As Integer
Dim sourcesheet As String
Dim Var1 As Integer
Dim Var2 As Integer
sourcesheet = "Final"
Var1 = 2
Var2 = 2


WORKING SCRIPT:
lastrow = Worksheets(sourcesheet).Range("B1").End(xlDown).Row


NOT WORKING SCRIPT:
Debug.Print Worksheets(sourcesheet).Cells(Var1, Var2)


What am I missing? I get "Run-time error '1004': Application-defined or object-defined error" when I try to run the debug.print line. I've checked that Var1 and Var2 are holding the right values, that the cell isn't blank (just in case).

I tried the following, but none of these works, either:
Debug.Print Worksheets(sourcesheet).Cells(Var1, Var2).Value
Debug.Print ThisWorkbook.Worksheets(sourcesheet).Cells(Var1, Var2)
Debug.Print ThisWorkbook.Worksheets(sourcesheet).Cells(Var1, Var2).Value

Thanks for any help you can give me.

 

[Kenneth Hobson]

There must be something missing that you have not posted. While you can sometimes rely on a default property like Value, it is best to be explicit as in your last Debug.

Keep in mind that variables like lastrow and Var1 should be dimmed as Long. Since you are going to end of B1, then lastrow could well not have been an Integer if no value was found after B1.

If sheet Final does not exist, it could error. I could show you how to safely check for that.

 

[dhunton]

There must be something missing that you have not posted. While you can sometimes rely on a default property like Value, it is best to be explicit as in your last Debug.

What do you mean by not relying on default like Value? What I'm eventually doing is assigning the value of the cell to another cell... but I'm printing to check what the value of the cell is (during troubleshooting).

Keep in mind that variables like lastrow and Var1 should be dimmed as Long. Since you are going to end of B1, then lastrow could well not have been an Integer if no value was found after B1.

Not so worried about that, as this is working with some pre-filled worksheets where there is always a value further down, but not too far down. But you're right - definitely didn't show my error checking in my posted code.

If sheet Final does not exist, it could error. I could show you how to safely check for that.

Actually, it never gets to this point if the sheet doesn't exist. I have checked long before this subroutine is called.

 

[dhunton]

I really am stuck on this. My code:

Dim sourcesheet As String
Dim Var1 As Integer
Dim Var2 As Integer
sourcesheet = "Final"
Var1 = 2
Var2 = 2

'The following works
Debug.Print Worksheets("Final").Cells(2,2)
'But the next line doesn't
Debug.Print Worksheets(sourcesheet).Cells(Var1, Var2)


Any idea why the line with the variables in it doesn't work? I've done the debug, and checked the values for sourcesheet, Var1 and Var2. All are correct. I'm completely at a loss here.

I thought possibly it could be an issue of the subroutine being placed in the wrong module, but all of my VBA is in one Module (Module1, as I wasn't feeling creative).

None of my cells are protected, so that's not the cause.

I am continuing to research and debug and test... but I'm really at a loss.

Anyone have any ideas or even a direction I should look in?