PDA

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