PDA

View Full Version : VBA dilemma - Passing Variable from Input box



Sgt Rock
11-02-2016, 11:35 AM
Before a For Next loop I have an input box for the user to pick they year they would like info on:

YearToInput = InputBox("Enter the first year you want to extract to a new sheet")

YearToInput is Dim a Variant

The loop is quite simple, but the value entered into the inputbox is not passed to inside the loop,
am at a loss

I can hard code in a year 2014, or 2015 in place of YearToInput and it works fine.

For Each Cell In Wks.Range("D2:D" & TheLastRow)
If Cell.Value = YearToInput Then

r = Cell.Row

Wks.Range("A" & r & ":" & "C" & r).Copy Worksheets("2014").Range("A" & x)

x = x + 1

End If

Next Cell

Thanks for any suggestions.
Mort in Dallas

Leith Ross
11-02-2016, 12:16 PM
Hello Mort,

The value from the InputBox is a Text String. You will need to convert it to a number to use it as the year.



Dim YearToInput As Variant

YearToInput = InputBox("Enter the first year you want to extract to a new sheet")

If YearToInput = "" Then Exit Sub

YearInput = CInt(YearToInput)

For Each Cell In Wks.Range("D2" & TheLastRow)
If Cell.Value = YearToInput Then

r = Cell.Row

Wks.Range("A" & r & ":" & "C" & r).Copy Worksheets("2014").Range("A" & x)

x = x + 1

End If

Next Cell