Using InputBox to Fill Down a Range

  • Hi guys,


    I have tried to use the input box to fill down a range, and used this code:


    Code
    Sub test()
    a = InputBox("Enter the value you want")
    b = "a" & WorksheetFunction.CountA(Range("a1", "a30000")) + 1
    Range(b) = a
    End Sub


    Now when I tried to make the inputbox send text to a specific sheet, I tried to use this:


    Code
    Sub Dynamic_Input()
    a = InputBox("Enter the value you want")
    b = WorksheetFunction.CountA(Worksheets("Sheet2").Range("a1", "a30000")) + 1
    c = Worksheets("Sheet2").Range("A1"&b)
    Range(c) = a
    End Sub


    This second try did'nt work, can some one tell me what i am doing wrong? Also, I specifically wanted to try and see how to use the counta instead of using a loop function


    thanks!

  • Re: Using InputBox to Fill Down a Range


    I'm not 100% sure what you are trying to do but to fix your 2nd code try this.

    Code
    Sub Dynamic_Input() 
        a = InputBox("Enter the value you want") 
        b = WorksheetFunction.CountA(Worksheets("Sheet2").Range("a1", "a30000")) + 1 
        Range("A1:A" & b) = a 
    End Sub

    Boo!:yikes:

  • Re: Using InputBox to Fill Down a Range


    Try This:


    Code
    Sub Dynamic_Input()
        a = InputBox("Enter the value you want")
         b = WorksheetFunction.CountA(Sheet2.Range("a1", "a30000")) + 1
           Sheet2.Range("A" & b) = a
    End Sub


    This is if you are trying to do the same thing on page 2 as you did it on page 1, except controll is on sheet1 and code goes to sheet2. Mine works different than Nori's, but maybe I didn't understand.

  • Re: Using InputBox to Fill Down a Range


    Thanks Norie, Brister!


    Actually Brister's code is what i needed, as I wanted to input new values into a chosen sheet, and then have the next value come underneath it.....


    Thanks guys!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!