Excel vba select from cell contents

Question

This is probably a really basic one for the wizards on here.

I'm trying to format a particular cell in a worksheet by calculating it from information entered and the code I have for that part works fine. By entering a name I get a cell reference such as Y5 in another cell. I'm trying to select that value (the cell address) as the defined variable celref. Then on the worksheet I want updated, I want the cell from celref to receive the assigned formatting. The code I'm using is:

Sub confirm()
Dim NAM As String
Dim celref As String
Worksheets("Morning").Select
NAM = Range("C3").Value
celref = Range("D3").Value
answer = MsgBox("Confirm sign in for " + NAM, vbYesNo + vbQuestion, "Confirm  Sign In")
If answer = vbNo Then GoTo Bye

    Worksheets("details").Select
Range(celref).Select      '<========= This is where I get the error!
With Selection.Font
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = 0
End With
With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With
Selection.Font.Bold = True

There's some more code afterwards but it appears to be the range select which is causing the problem. Which newbie error am I making please?

Do a `Debug.Print Range("D3").Value` and report back on what it is. — Chrismas007, Sep 02 '16 at 18:34
Also, you need to [learn how to avoid using `.select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — Chrismas007, Sep 02 '16 at 18:35
The value in D3 is an evaluation of this formula: =$M$1&MATCH(C3,kidnames,0)+4 currently give the result 'Y5', which is the cell I want to format in "details" — Keith T, Sep 02 '16 at 18:59
Yes, I'm sure I do need to learn how to avoid using selects, but at the moment what I mostly need to do is get this code working :-) — Keith T, Sep 02 '16 at 19:02
Don't seem to get anything from the Debug.Print code, but if I do a 'msgbox(celref) after populating the variable I get 'Y5' — Keith T, Sep 02 '16 at 19:04

Tyeler · Accepted Answer · 2016-09-02T19:32:23.217

0

If you want to achieve this with better form, try something like this:

Sub confirm()
    Dim morningWS, detailsWS As Worksheet
    Dim NAM, celref As String

    Set morningWS = ThisWorkbook.Sheets("Morning")
    Set detailsWS = ThisWorkbook.Sheets("details")
    celref = morningWS.Range("D3").Value
    NAM = morningWS.Range("C3").Value

    answer = MsgBox("Confirm sign in for " + NAM, vbYesNo + vbQuestion, "Confirm  Sign In")
    If answer = vbNo Then GoTo Bye

    With detailsWS.Range(celref)
        With .Font
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
            .Bold = True
        End With
        With .Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorLight1
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End With

This makes it clear which cells you're referencing and should make it easier for you to edit things without running into errors.

Another way you can achieve this:

Sub confirm()
    Dim morningWS, detailsWS As Worksheet
    Dim NAM As String
    Dim celref as Range

    Set morningWS = ThisWorkbook.Sheets("Morning")
    Set detailsWS = ThisWorkbook.Sheets("details")
    Set celref = detailsWS.Range(morningWS.Range("D3").Value) 'making celref a range object
    NAM = morningWS.Range("C3").Value

    answer = MsgBox("Confirm sign in for " + NAM, vbYesNo + vbQuestion, "Confirm  Sign In")
    If answer = vbNo Then GoTo Bye

    With celref 'celref is the object and doesnt need to be otherwise addressed
        'rest of the code

edited Sep 02 '16 at 19:32

answered Sep 02 '16 at 19:02

Tyeler

1,088
1
12
26

Thanks for the suggestion, much appreciated. Unfortunately when it gets to the 'Set celref' line I get "Run-time error '424': Object required – Keith T Sep 02 '16 at 19:10
did you copy and paste the whole code? Like the part where we `Dim celref As Range`? – Tyeler Sep 02 '16 at 19:14
Hi, yes I unashamedly copied/pasted the whole thing: Sub confirm() Dim morningWS, detailsWS As Worksheet Dim NAM As String Dim celref As Range Set morningWS = ThisWorkbook.Sheets("Morning") Set detailsWS = ThisWorkbook.Sheets("details") NAM = morningWS.Range("C3").Value Set celref = morningWS.Range("D3").Value ... and so on – Keith T Sep 02 '16 at 19:19
@KeithT That was totally my bad. Turns out I still have a thing or two to learn about VBA. Try the new updated code, it worked for me. That's what I get for throwing about code without testing it. – Tyeler Sep 02 '16 at 19:21
I shall have to buy you an official Wizard's hat! That works, thank you so much. – Keith T Sep 02 '16 at 19:26
I showed what I was originally trying to do, but properly this time. I set the range object improperly. Both codes work, and depending on your needs, one may work more for you than the other. If you like the answer, could you accept it as such? Thanks! – Tyeler Sep 02 '16 at 19:33

Excel vba select from cell contents

1 Answers1