View Full Version : Cell References
Jeff_PD
08-23-2009, 08:12 AM
Hi all,
When I reference a particular cell using VBA in Excel 2003, the cell picked up isn't always the one I've asked for.
e.g. worksheets("Sheet 1").range("A2").select
will sometimes select cell A2, other times it will select B3, A100... I get the feeling that it is effected by the cell previous selected, but not always.
Is their a function that will reset the references?
I've had this problem on previous projects and it's confusing me - there must be something simple I'm missing!
Cheers,
Jeff
lucas
08-23-2009, 08:24 AM
Jeff, I think you will have to show us some kind of an example because your code is pretty specific other than a space between Sheet and 1 which might cause a problem if the sheet is named sheet1.
mdmackillop
08-23-2009, 08:33 AM
Hi Jeff,
Your code should work (subject to the sheet name being correct), but will only work if Sheet 1 is active. To go to a specific cell from another sheet, you need
Application.GoTo Worksheets("Sheet 1").range("A2")
Jeff_PD
08-23-2009, 08:45 AM
Thanks lucas. In this particular example I want the value in a cell to change depending on the state of a check box.
(typed this from another computer so there may be spelling errors...)
private sub ckHighlightHighRisk_Click()
if ckHighlightHighRisk.value = true then
worksheets("Pts without risk score").select
selection.range("AQ9").value = "Yes"
else
worksheets("Pts without risk score").select
selection.range("AQ9").value = "No"
end if
end sub
Clicking on the check box a couple of times added "Yes" to cell AW18 and "No" to CM26.
Jeff_PD
08-23-2009, 08:53 AM
Now that's really interesting mdmackillop. I've just tried you code
application.goto worksheets("Headlines").range("A2")
and running it a couple of times always takes me to A2.
The code I had been using was
worksheets("Headlines").select
selection.range("A2").select
and this took me to A3, then A4 followed by A5!
Now I know I can change it, but what's wrong with my code?
mdmackillop
08-23-2009, 09:09 AM
Worksheets("Headlines").select
Range("A2").select
Selection.Range("A2").Select is going to a reference relative to the ActiveCell, not the Sheet.
Try
Selection.Range("B3").Select
or
Selection.Range("C4").Select
with say E5 as your Activecell. You should be taken to F8 or G9 respectively
mikerickson
08-23-2009, 02:50 PM
Or ActiveSheet.Range("B3").Select
Paul_Hossler
08-23-2009, 04:28 PM
For your example, there's no need to select the sheet or the cell, you should be able to just update it's value
Private Sub ckHighlightHighRisk_Click()
If ckHighlightHighRisk.value = True Then
Worksheets("Pts without risk score").range("AQ9").value = "Yes"
Else
Worksheets("Pts without risk score").range("AQ9").value = "No"
End If
End Sub
Paul
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.