VBA InputBox Error

Question

I am trying to have my inputbox dump a number into a specific cell. I have gotten this to work as far as prompting the box on the correct cell, however when I hit enter or click ok the cell remains blank. Does anyone have any idea how to fix?

Thanks!!

    Sub DiscardPipe()        
    Dim str As String
    Dim rtrn As String
    Dim impu As Integer
       With Worksheets(ActiveSheet.Name)
       rtrn = ActiveSheet.Name
       str = Range("e2").Value

    Set SrchRng = Worksheets("Information").Range("J8:J17").Find(what:=str)
     Sheets("information").Select
     Range("J8:J17").Find(what:=str).Select
     Selection.End(xlToRight).Select
       Selection.End(xlToRight).Select
        Selection.End(xlToRight).Select
        Selection.End(xlToRight).Select
        Selection.End(xlToRight).Select
        Selection.End(xlToRight).Select
        Selection.End(xlToRight).Select
        Selection.End(xlToRight).Select
        Selection.End(xlToRight).Select
        Selection.End(xlToRight).Select
        Selection.End(xlToRight).Select
        Selection.End(xlToLeft).Select
        ActiveCell.Offset(0, 1).Range("A1").Select
      impu = InputBox("Enter New Bottom Pipe Serial Number", "New Pipe", 1)

        End With
    End Sub

What are you trying to do here, and why are you using the `Selection.End(xlToRight).Select` repeatedly? Also, while you're assigning the value to `impu` variable, you never output that value to the worksheet, such as `Range("A1").Value = impu` . — David Zemens, Oct 25 '16 at 19:25
(See [how to avoid using `.Select`/`.Activate`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) to cut down on a lot of your code.) — BruceWayne, Oct 25 '16 at 19:25
In other words, the very last thing you're doing in this code is merely **storing** the value from `InputBox` into a **variable**. And the next line is your `End With` and then `End Sub`. Nothing happens to the value that you've stored, you do nothing with it, it's just there in memory. — David Zemens, Oct 25 '16 at 19:26
@DavidZemens There are blank cells inbetween my colomns and the ctrl+arrow doen't skip them so I have added it enough times to get me to the point in my sheet where the new data can be entered. This code will read e2, and find it in my range j8:j17, move the correct spot and then input box will appear once the cell has been selected, and enter the value that was entered in the input box. — deejaay, Oct 25 '16 at 19:27
@DavidZemens what can I do to have this entered in the sheet and not in memory? — deejaay, Oct 25 '16 at 19:30
deejaay, @DavidZemens answered this question already. To store the value on the sheet you should insert `Range("A1").Value = impu` after the line `impu = InputBox("Enter New Bottom Pipe Serial Number", "New Pipe", 1)`. This will output the value from the input box into cell `A1`. If you want it somewhere else on the sheet then you'll have to change that. If you want it in another sheet (than the currently active sheet) then you'll also have to change this line of code. — Ralph, Oct 25 '16 at 19:33
I doubt very much that will reliably find the correct cell for input, but without seeing your worksheet I can't be certain. If you could explain more about where the data will be going (i.e., is it always in the same column, etc.) then there is about a 99.99% chance that there is a better, more reliable, more concise way to get that output where you want it :) — David Zemens, Oct 25 '16 at 19:36
adding 'Range("A1").Value = impu' does not display any value, it continues appearing blank — deejaay, Oct 25 '16 at 19:54

score 0 · Answer 1 · answered Oct 25 '16 at 20:22

The following is still leaving a blank cell after number is added in input box.
Sub DiscardPipe() Dim str As String Dim rtrn As String Dim impu As Integer

       With Worksheets(ActiveSheet.Name)
       rtrn = ActiveSheet.Name
        str = Range("e2").Value
        End With
    Set SrchRng = Worksheets("Information").Range("J8:J17").Find(what:=str)
     Sheets("information").Select
     Range("J8:J17").Find(what:=str).Select
     Selection.End(xlToRight).Select
       Selection.End(xlToRight).Select
        Selection.End(xlToRight).Select
        Selection.End(xlToRight).Select
        Selection.End(xlToRight).Select
        Selection.End(xlToRight).Select
        Selection.End(xlToRight).Select
        Selection.End(xlToRight).Select
        Selection.End(xlToRight).Select
        Selection.End(xlToRight).Select
        Selection.End(xlToRight).Select
        Selection.End(xlToLeft).Select
        ActiveCell.Offset(0, 1).Range("A1").Select
        impu = InputBox("Enter New Bottom Pipe Serial Number", "New Pipe", 8)
        Range("A1").Value = impu
    End Sub

VBA InputBox Error

1 Answers1