Reputation: 107
I have tried to implement the following code so that I can read a cell value and then places this value into another cell on the same Worksheet.
The worksheet has the following name: TestUserGuidance (Has no spaces) The code is as follows:
Sub GuideTest()
Dim dblPower, dblMass, dblRatedSpeed, dblRefLength, dblAwot, dblEngineSpeed, dblRoadSpeed As Double
Dim dblPMR As Double
dblPower = Worksheets("TestUserGuidance").Cell("B1").Value
Worksheets("TestUserGuidance").Cell("E1") = dblPower
End Sub
Could anyone please advise where I'm going wrong?
Thanks
Upvotes: 0
Views: 845
Reputation: 581
I think vba can't find the TestUserGuidance sheet. If I run the following in the immediate window I get an "Object doesn't support this property or method" error:
?worksheets(1).cell("A1")
If I run this, I get the error you mention "Subscript out of range":
?worksheets("non-existent sheet").cell("A1")
This suggests to me that the active workbook is incorrect since you would have gotten the other error if vba was able to find the worksheet. Can you try adding ThisWorkbook (and also use range)?
dblPower = ThisWorkbook.Worksheets("TestUserGuidance").Range("B1").Value
ThisWorkbook.Worksheets("TestUserGuidance").Range("E1") = dblPower
Another option would be to rename your worksheet:
Then you won't have to worry about the active workbook (and also get intellisence):
shtTestUserGuidance.Range("E1") = shtTestUserGuidance.Range("B1")
Also, I would suggest you make all the corrections suggested by others. You will likely have other issues moving forward otherwise.
Lastly, if the worksheet is like a template and the structure won't change, you'd be better off just using a formula (i.e. E1's formula: =B1). Even if there's conditional logic, it might be easier to maintain as a formula rather than vba code.
Here are some troubleshooting steps:
Toggle a break point on the line where dblPower is set by clicking on the grey area to the left:
Also open the Immediate window if it's not already there by pressing ctrl+g:
When you run the code, it should pause at the break point. Now you can inspect what's in scope. Try typing the following into the Immediate window and press enter:
?Worksheets("TestUserGuidance").Name
or
?ActiveWorkbook.Name
Note that you can also step through the code line by line by pressing F8 while the cursor is somewhere within the subroutine. Bottom line is that you may need to examine the code line by line in order to figure this out.
Upvotes: 2
Reputation: 43575
This is the way to make your code working:
Sub GuideTest()
Dim dblPower, dblEngineSpeed, dblRoadSpeed As Double ' first two are of variant type
Dim dblPMR As Double
dblPower = Worksheets(1).Range("B1").Value
Worksheets(1).Range("E1") = dblPower
End Sub
When you use "E1" or "B1" you should use Range
, not Cells. In my answer Worksheets(1)
refers to Worksheets("TestUserGuidance")
.
Upvotes: 1
Reputation:
Only dblRoadSpeed
is declared as double.
To fix it, declare all your variables as individual, in vba you can do in 2 ways.
Dim dblPower As Double, dblMass As Double
Or
Dim dblPower As Double
Dim dblMass As Double
Also, you don't need to declare any variable to do what you are doing. You can copy the value in one step.
Worksheets("TestUserGuidance").Range("E1").Value = Worksheets("TestUserGuidance").Range("B1").Value
Upvotes: 0