PDA

View Full Version : Input boxes to change row each time



GribbiN
06-12-2015, 02:13 PM
Hi,
What i am trying to achieve with these text boxes is for each time the code runs i want it to search column A for the next blank field or automatically use the next row each time it runs. Column A will always be a DATE. The current next available row is row 13, the next time i run the code i want the data in row 14, then 15 and so on.

You will see in the code that not every column requires data inputting, I have done the easy part

Probably a easy fix for the Guru's but any help greatly appreciated



Private Sub Worksheet_Activate()
Sheets("Supercentre").Select
Dim InputValue As Variant
InputValue = InputBox("Date")
Range("A13") = InputValue
InputValue = InputBox("Store")
Range("B13") = InputValue
InputValue = InputBox("£ Counted")
Range("C13") = InputValue
InputValue = InputBox("Expected Units")
Range("D13") = InputValue
InputValue = InputBox("Actual Units")
Range("E13") = InputValue
InputValue = InputBox("Warehouse Units")
Range("G13") = InputValue
InputValue = InputBox("George Units")
Range("I13") = InputValue
InputValue = InputBox("CPH")
Range("K13") = InputValue
InputValue = InputBox("Crew Size")
Range("M13") = InputValue
InputValue = InputBox("Net Error Rate %")
Range("N13") = InputValue
InputValue = InputBox("Gross Error Rate %")
Range("O13") = InputValue
InputValue = InputBox("SKU Error")
Range("P13") = InputValue
InputValue = InputBox("Core Error")
Range("Q13") = InputValue
InputValue = InputBox("Off The Floor Time")
Range("R13") = InputValue
InputValue = InputBox("Live Comps")
Range("S13") = InputValue
InputValue = InputBox("Pallets")
Range("T13") = InputValue
InputValue = InputBox("Rails")
Range("U13") = InputValue
End Sub

SamT
06-13-2015, 06:28 AM
I did the first two to show you how.


Option Explicit 'Put at top of all code Modules

Private Sub Worksheet_Activate()

Dim NR As String 'Next Row
Sheets("Supercentre").Select
NR = CStr(Cells(Rows.Count, 1).End(xlUp).Row + 1)

Range("A" & NR) = InputBox("Date")
Range("B" & NR) = InputBox("Store")
'Continue As above



'Below not done
InputValue = InputBox("£ Counted")
Range("C13") = InputValue
InputValue = InputBox("Expected Units")
Range("D13") = InputValue
InputValue = InputBox("Actual Units")
Range("E13") = InputValue
InputValue = InputBox("Warehouse Units")
Range("G13") = InputValue
InputValue = InputBox("George Units")
Range("I13") = InputValue
InputValue = InputBox("CPH")
Range("K13") = InputValue
InputValue = InputBox("Crew Size")
Range("M13") = InputValue
InputValue = InputBox("Net Error Rate %")
Range("N13") = InputValue
InputValue = InputBox("Gross Error Rate %")
Range("O13") = InputValue
InputValue = InputBox("SKU Error")
Range("P13") = InputValue
InputValue = InputBox("Core Error")
Range("Q13") = InputValue
InputValue = InputBox("Off The Floor Time")
Range("R13") = InputValue
InputValue = InputBox("Live Comps")
Range("S13") = InputValue
InputValue = InputBox("Pallets")
Range("T13") = InputValue
InputValue = InputBox("Rails")
Range("U13") = InputValue

End Sub

This would be a great place for a UserForm.

snb
06-13-2015, 06:36 AM
or

Sub M_snb()
Sheets("Supercentre").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 3) = Array(InputBox("nr 1", "Snb"), InputBox("nr 2", "Snb"), InputBox("nr 3", "Snb"))
End Sub