Excel fails to read out variable

Question

I have the following code:

Dim FinalAddr As Range
Dim Final As String

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next


Select Case UCase(Target.Value)


   Case "NEW-BOX"
        Selection.Offset(-1, 2).Select
        FinalAddr = Range(Selection.Address)
        MsgBox FinalAddr ' Debug, nothing comes up however
        Selection.ClearContents
        Selection.Offset(1, -2).Select

   Case "END-BOX"
        Final = FinalAddr.Value
        Application.Speech.Speak (Final)

    End Select
End Sub

However, when I scan new box nothing happens, is all of the syntax correct? I get no errors, any input will be greatly appreciated.

Insert a Case Else: MsgBox "You provided " & UDase(Target.Value)before the End Select statement to see what is being checked. — Peter Albert
– Peter Albert, Commented Feb 26, 2013 at 10:25
@AndreyGordeev I have tried a breakpoint however the code executes fine and just does not set the variable. — user2087008
– user2087008, Commented Feb 26, 2013 at 10:32
@mattboy I would like it to print the range of the selection, so I can check that the variable is being set, however it does not set at all — user2087008
– user2087008, Commented Feb 26, 2013 at 10:33

mattboy · Accepted Answer · 2013-02-26 10:46:00Z

2

Is this what you're after?

   Case "NEW-BOX"
        Selection.Offset(-1, 2).Select
        MsgBox Selection.Address ' Debug, nothing comes up however
        Selection.ClearContents
        Selection.Offset(1, -2).Select

EDIT: If you want to set the FinalAddr to the selection and then test that it works:

   Case "NEW-BOX"
        Selection.Offset(-1, 2).Select
        Set FinalAddr = Selection
        MsgBox FinalAddr ' Debug, nothing comes up however
        Selection.ClearContents
        Selection.Offset(1, -2).Select

edited Feb 26, 2013 at 10:46

answered Feb 26, 2013 at 10:36

mattboy

2,9085 gold badges29 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user2087008 Over a year ago

Sorry no, I am trying to set the FinalAddr variable to the range, and then get the value of that range and pass it to the voice synthesis in the next case

glh Over a year ago

Then just remove the .address

mattboy Over a year ago

Right, just remove .Address in second example to get the value, see updated post.

user2087008 Over a year ago

Thank you, this is correct and solved my problem, everything now works fine.

glh · Accepted Answer · 2013-02-26 10:43:57Z

0

If you want the value in the cell you should be using Finaladdr = Selection.Value, but if you want the address the selection you should use = Selection.AddressLocal()

However if you want to refer to the range youll need to use Set and then .AddressLocal() when refering to the cells address.

edited Feb 26, 2013 at 10:43

answered Feb 26, 2013 at 10:37

glh

4,9824 gold badges26 silver badges42 bronze badges

Collectives™ on Stack Overflow

Excel fails to read out variable

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related