Urgent help with input box & loop

  • Thread starter Thread starter mju
  • Start date Start date
M

mju

I am trying to write a program using input box and a loop. I want the
program to ask the user for info for column c(blank cell), which is the
miles, then info for coln a, & B. The program will then take the given info
for col C then loop thru column A . Col C will then be populated with the
given miles until the state(col A) changes. Then another input box will pop
up prompting the user for the new state, miles, qty.

Below is a sample of my data
Col A(state) Col B(qty) Col C(Miles)
NY 2
NY 2
NY 2
TX 1
TX 1
CA 3
 
Try something like this:

Sub InputBoxLoop()
Dim r, c As Range, _
sState As String, _
lQty, lMiles As Long

sState = Range("A1").Value
lQty = InputBox("Input Quantity for " & sState)
lMiles = InputBox("Input Miles for " & sState)

Set r = Range("A1:A" & Range("A65536").End(xlUp).Row)

For Each c In r
If Not c.Value Like sState Then
sState = c.Value
lQty = InputBox("Input Quantity for " & sState)
lMiles = InputBox("Input Miles for " & sState)
End If
c.Offset(0, 1).Value = lQty
c.Offset(0, 2).Value = lMiles
Next c
End Sub

Regards,
David Miller
 
Back
Top