current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have used this code in the past, but today it is not working for some reason. I have scoured the internet for answers, and from what I have read my InputBox is set up correctly. When i use the hard coded range my messageBox returns the correct cell values, but when I use the inputbox no values are returned.

I am fairly new to coding in VBA, and this seems like it should be an easy fix. Hopefully someone can help :)

Dim rRng As Range
Dim rCell As Range

Set rRng = Application.InputBox(Prompt:="Select Cells to check", Type:=8)
'Set rRng = Sheets("Table 3-1").Range("F11:F13")
For Each rCell In rRng.cells
    a = rCell.Value
    MsgBox "Cell Value is: " & a
Next
share|improve this question
    
I tried running this macro on another computer, and it worked just fine. Any one have an idea of what is happening on my computer? –  user2913433 Oct 23 '13 at 23:16

2 Answers 2

up vote 0 down vote

Make sure you have select cells containing values. Your code works just fine. Make sure you are on the proper sheet when you run the macro.

share|improve this answer
    
The cells do contain values, and I would like to use this macro in any workbook. When reading the MS help about InputBoxes the Application.InputBox is supposed to work with the 'Active' worksheet. –  user2913433 Oct 23 '13 at 23:01
    
You are correct.........are you using the mouse to set the range??? –  Gary's Student Oct 23 '13 at 23:46
up vote 0 down vote
  • Check if the value in rRng variable is set properly by using If rRng Is Nothing Then
  • Make sure there is no unwanted reference in the workbook.
  • VBA IDE > Standard tool bar > Goto Debug > Compile VBA code may highlight the possible issue.

.

  Sub test()

    Dim rRng As Range
    Dim rCell As Range

    On Error Resume Next
    Set rRng = Application.InputBox(Prompt:="Select Cells to check", Type:=8)
    On Error GoTo 0

    If rRng Is Nothing Then
        MsgBox "No Range selected"
        Exit Sub
    End If

    For Each rCell In rRng.Cells
        a = rCell.Value
        MsgBox "Cell Value is: " & a
    Next

End Sub
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.