If first cell has a value in it, how can my formula below know this and skip to next available cell?

drdre77

New Member
Joined
Dec 27, 2017
Messages
13
Hi Everyone, been posting today a few things... have a few different ways now that I want to do this.. but I think if I can find a way to do this... it will help me out a lot...
Below is my formula. What I need help with is if someone can tell me how I can change it if possible that is... so that if I click on the button... and the first cell as you can see below (A24) is the first cell that asks you to fill out... now if that cell already has an item in there... I would like the formula to know that it has a value, and that it should jump onto the next box to fill...
Hope you know what I mean?

I would really love help on this. Thanks



Sub Button174_Click()
Reply = InputBox(Prompt:="SCAN OR ENTER SKU", _
Title:="SKU", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("A24").Value = Reply
End If
Reply = InputBox(Prompt:="QUANTITY", _
Title:="QTY", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("H24").Value = Reply
End If
If MsgBox("SCAN THE NEXT ITEM?", vbYesNo) = vbNo Then Exit Sub

Reply = InputBox(Prompt:="SCAN OR ENTER SKU", _
Title:="SKU", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("A25").Value = Reply
End If
Reply = InputBox(Prompt:="QUANTITY", _
Title:="QTY", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("H25").Value = Reply
End If
If MsgBox("SCAN THE NEXT ITEM?", vbYesNo) = vbNo Then Exit Sub
Reply = InputBox(Prompt:="SCAN OR ENTER SKU", _
Title:="SKU", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("A26").Value = Reply
End If
Reply = InputBox(Prompt:="QUANTITY", _
Title:="QTY", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("H26").Value = Reply
End If
If MsgBox("SCAN THE NEXT ITEM?", vbYesNo) = vbNo Then Exit Sub
Reply = InputBox(Prompt:="SCAN OR ENTER SKU", _
Title:="SKU", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("A27").Value = Reply
End If
Reply = InputBox(Prompt:="QUANTITY", _
Title:="QTY", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("H27").Value = Reply
End If
If MsgBox("SCAN THE NEXT ITEM?", vbYesNo) = vbNo Then Exit Sub
Reply = InputBox(Prompt:="SCAN OR ENTER SKU", _
Title:="SKU", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("A28").Value = Reply
End If
Reply = InputBox(Prompt:="QUANTITY", _
Title:="QTY", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("H28").Value = Reply
End If
If MsgBox("SCAN THE NEXT ITEM?", vbYesNo) = vbNo Then Exit Sub
Reply = InputBox(Prompt:="SCAN OR ENTER SKU", _
Title:="SKU", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("A29").Value = Reply
End If
Reply = InputBox(Prompt:="QUANTITY", _
Title:="QTY", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("H29").Value = Reply
End If
If MsgBox("SCAN THE NEXT ITEM?", vbYesNo) = vbNo Then Exit Sub
Reply = InputBox(Prompt:="SCAN OR ENTER SKU", _
Title:="SKU", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("A30").Value = Reply
End If
Reply = InputBox(Prompt:="QUANTITY", _
Title:="QTY", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("H30").Value = Reply
End If
If MsgBox("SCAN THE NEXT ITEM?", vbYesNo) = vbNo Then Exit Sub
Reply = InputBox(Prompt:="SCAN OR ENTER SKU", _
Title:="SKU", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("A31").Value = Reply
End If
Reply = InputBox(Prompt:="QUANTITY", _
Title:="QTY", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("H31").Value = Reply
End If
If MsgBox("SCAN THE NEXT ITEM?", vbYesNo) = vbNo Then Exit Sub
Reply = InputBox(Prompt:="SCAN OR ENTER SKU", _
Title:="SKU", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("A32").Value = Reply
End If
Reply = InputBox(Prompt:="QUANTITY", _
Title:="QTY", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("H32").Value = Reply
End If
If MsgBox("SCAN THE NEXT ITEM?", vbYesNo) = vbNo Then Exit Sub
Reply = InputBox(Prompt:="SCAN OR ENTER SKU", _
Title:="SKU", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("A33").Value = Reply
End If
Reply = InputBox(Prompt:="QUANTITY", _
Title:="QTY", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("H33").Value = Reply
End If
If MsgBox("SCAN THE NEXT ITEM?", vbYesNo) = vbNo Then Exit Sub
Reply = InputBox(Prompt:="SCAN OR ENTER SKU", _
Title:="SKU", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("A34").Value = Reply
End If
Reply = InputBox(Prompt:="QUANTITY", _
Title:="QTY", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("H34").Value = Reply
End If
If MsgBox("SCAN THE NEXT ITEM?", vbYesNo) = vbNo Then Exit Sub
Reply = InputBox(Prompt:="SCAN OR ENTER SKU", _
Title:="SKU", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("A35").Value = Reply
End If
Reply = InputBox(Prompt:="QUANTITY", _
Title:="QTY", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("H35").Value = Reply
End If
If MsgBox("SCAN THE NEXT ITEM?", vbYesNo) = vbNo Then Exit Sub
Reply = InputBox(Prompt:="SCAN OR ENTER SKU", _
Title:="SKU", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("A36").Value = Reply
End If
Reply = InputBox(Prompt:="QUANTITY", _
Title:="QTY", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("H36").Value = Reply
End If
If MsgBox("SCAN THE NEXT ITEM?", vbYesNo) = vbNo Then Exit Sub
Reply = InputBox(Prompt:="SCAN OR ENTER SKU", _
Title:="SKU", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("A37").Value = Reply
End If
Reply = InputBox(Prompt:="QUANTITY", _
Title:="QTY", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("H37").Value = Reply
End If
If MsgBox("SCAN THE NEXT ITEM?", vbYesNo) = vbNo Then Exit Sub
Reply = InputBox(Prompt:="SCAN OR ENTER SKU", _
Title:="SKU", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("A38").Value = Reply
End If
Reply = InputBox(Prompt:="QUANTITY", _
Title:="QTY", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("H38").Value = Reply
End If
If MsgBox("SCAN THE NEXT ITEM?", vbYesNo) = vbNo Then Exit Sub
Reply = InputBox(Prompt:="SCAN OR ENTER SKU", _
Title:="SKU", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("A39").Value = Reply
End If
Reply = InputBox(Prompt:="QUANTITY", _
Title:="QTY", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("H39").Value = Reply
End If
If MsgBox("SCAN THE NEXT ITEM?", vbYesNo) = vbNo Then Exit Sub
Reply = InputBox(Prompt:="SCAN OR ENTER SKU", _
Title:="SKU", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("A40").Value = Reply
End If
Reply = InputBox(Prompt:="QUANTITY", _
Title:="QTY", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("H40").Value = Reply
End If
If MsgBox("SCAN THE NEXT ITEM?", vbYesNo) = vbNo Then Exit Sub
Reply = InputBox(Prompt:="SCAN OR ENTER SKU", _
Title:="SKU", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("A41").Value = Reply
End If
Reply = InputBox(Prompt:="QUANTITY", _
Title:="QTY", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("H41").Value = Reply
End If
If MsgBox("SCAN THE NEXT ITEM?", vbYesNo) = vbNo Then Exit Sub
Reply = InputBox(Prompt:="SCAN OR ENTER SKU", _
Title:="SKU", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("A42").Value = Reply
End If
Reply = InputBox(Prompt:="QUANTITY", _
Title:="QTY", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("H42").Value = Reply
End If
If MsgBox("SCAN THE NEXT ITEM?", vbYesNo) = vbNo Then Exit Sub
Reply = InputBox(Prompt:="SCAN OR ENTER SKU", _
Title:="SKU", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("A43").Value = Reply
End If
Reply = InputBox(Prompt:="QUANTITY", _
Title:="QTY", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("H43").Value = Reply
End If
If MsgBox("SCAN THE NEXT ITEM?", vbYesNo) = vbNo Then Exit Sub
Reply = InputBox(Prompt:="SCAN OR ENTER SKU", _
Title:="SKU", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("A44").Value = Reply
End If
Reply = InputBox(Prompt:="QUANTITY", _
Title:="QTY", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("H44").Value = Reply
End If
If MsgBox("SCAN THE NEXT ITEM?", vbYesNo) = vbNo Then Exit Sub
Reply = InputBox(Prompt:="SCAN OR ENTER SKU", _
Title:="SKU", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("A45").Value = Reply
End If
Reply = InputBox(Prompt:="QUANTITY", _
Title:="QTY", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("H45").Value = Reply
End If
If MsgBox("SCAN THE NEXT ITEM?", vbYesNo) = vbNo Then Exit Sub
Reply = InputBox(Prompt:="SCAN OR ENTER SKU", _
Title:="SKU", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("A46").Value = Reply
End If
Reply = InputBox(Prompt:="QUANTITY", _
Title:="QTY", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("H46").Value = Reply
End If
If MsgBox("SCAN THE NEXT ITEM?", vbYesNo) = vbNo Then Exit Sub
Reply = InputBox(Prompt:="SCAN OR ENTER SKU", _
Title:="SKU", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("A47").Value = Reply
End If
Reply = InputBox(Prompt:="QUANTITY", _
Title:="QTY", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("H47").Value = Reply
End If
If MsgBox("SCAN THE NEXT ITEM?", vbYesNo) = vbNo Then Exit Sub
Reply = InputBox(Prompt:="SCAN OR ENTER SKU", _
Title:="SKU", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("A48").Value = Reply
End If
Reply = InputBox(Prompt:="QUANTITY", _
Title:="QTY", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("H48").Value = Reply
End If
If MsgBox("SCAN THE NEXT ITEM?", vbYesNo) = vbNo Then Exit Sub
Reply = InputBox(Prompt:="SCAN OR ENTER SKU", _
Title:="SKU", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("A49").Value = Reply
End If
Reply = InputBox(Prompt:="QUANTITY", _
Title:="QTY", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("H49").Value = Reply
End If
If MsgBox("SCAN THE NEXT ITEM?", vbYesNo) = vbNo Then Exit Sub
Reply = InputBox(Prompt:="SCAN OR ENTER SKU", _
Title:="SKU", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("A50").Value = Reply
End If
Reply = InputBox(Prompt:="QUANTITY", _
Title:="QTY", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("H50").Value = Reply
End If
If MsgBox("SCAN THE NEXT ITEM?", vbYesNo) = vbNo Then Exit Sub
Reply = InputBox(Prompt:="SCAN OR ENTER SKU", _
Title:="SKU", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("A51").Value = Reply
End If
Reply = InputBox(Prompt:="QUANTITY", _
Title:="QTY", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("H51").Value = Reply
End If
If MsgBox("SCAN THE NEXT ITEM?", vbYesNo) = vbNo Then Exit Sub
Reply = InputBox(Prompt:="SCAN OR ENTER SKU", _
Title:="SKU", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("A52").Value = Reply
End If
Reply = InputBox(Prompt:="QUANTITY", _
Title:="QTY", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("H52").Value = Reply
End If
If MsgBox("SCAN THE NEXT ITEM?", vbYesNo) = vbNo Then Exit Sub
Reply = InputBox(Prompt:="SCAN OR ENTER SKU", _
Title:="SKU", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("A53").Value = Reply
End If
Reply = InputBox(Prompt:="QUANTITY", _
Title:="QTY", Default:="")
If Reply = vbNullString Then

Else
ActiveSheet.Range("H53").Value = Reply
End If
End Sub
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,083
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
Sub Button174_Click()

   Dim Reply As String
   Dim NxtRw As Long
   
   NxtRw = Range("A" & Rows.Count).End(xlUp).Offset(1).Row
   If NxtRw < 24 Then NxtRw = 24
   Do
      Reply = InputBox(Prompt:="SCAN OR ENTER SKU", _
         title:="SKU", Default:="")
      If Not Reply = vbNullString Then
         Range("A" & NxtRw).Value = Reply
   
         Reply = InputBox(Prompt:="QUANTITY", _
            title:="QTY", Default:="")
         If Not Reply = vbNullString Then
            Range("H" & NxtRw).Value = Reply
         End If
      End If
      If MsgBox("SCAN THE NEXT ITEM?", vbYesNo) = vbNo Then Exit Sub
   Loop
End Sub
 

Richard U

Active Member
Joined
Feb 14, 2006
Messages
396
Office Version
  1. 365
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
This is much easier to maintain.

Code:
Sub Button174_Click()
    Dim strReply As String
    Dim rngMyRange As Range
    Dim rngMycell As Range
    
    
    With ActiveSheet
         Set rngMyRange = Union(.Range("A24:A53"), .Range("H24:H53"))
    
         For Each rngMycell In rngMyRange
    
             If rngMycell.Value = "" Then
               strReply = InputBox(Prompt:="SCAN OR ENTER SKU", _
               Title:="SKU", Default:="")
               If strReply <> vbNullString Then rngMycell.Value = strReply
            End If
         Next rngMycell

         If MsgBox("SCAN THE NEXT ITEM?", vbYesNo) = vbNo Then Exit Sub

    End With
End Sub

see if that works
 
Last edited:

Richard U

Active Member
Joined
Feb 14, 2006
Messages
396
Office Version
  1. 365
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
Two good approaches here for you.

One using the range option combined with

"For each... next" logic

One using "Do....loop" logic.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,083
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

@Richard U
Your code doesn't give an escape option, until it has looped through all the cells.
The OP has 2 input boxes, rather than just 1.
Also your code will loop through col A then Col H, which means that the user will have to enter all the SKUs & than enter the Qty
 

drdre77

New Member
Joined
Dec 27, 2017
Messages
13
Hi! Thank you so much! This is great... there's only 1 problem... it goes to a221 That's because I have cells a24-a53 that are open... everything above that and below that until a220 has info in it... is there a way it can just stick into that range only? or should I move everything below a53 to AA or something. Thanks so much though, this is awesome! @Fluff
 
Last edited:

drdre77

New Member
Joined
Dec 27, 2017
Messages
13

ADVERTISEMENT

I just tried the one from @Richard U and you are right.
 

Richard U

Active Member
Joined
Feb 14, 2006
Messages
396
Office Version
  1. 365
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
@Fluff

fixed it, thanks. Also, does yours have a provision for skipping cells already filled out?

Code:
Sub Button174_Click()
    Dim strReply As String
    Dim rngMyRange As Range
    Dim rngMycell As Range
    
    
    With ActiveSheet
    Set rngMyRange = .Range("A24:A53")
    
    For Each rngMycell In rngMyRange
    
        If rngMycell.Value = "" Then
            strReply = InputBox(Prompt:="SCAN OR ENTER SKU", _
            Title:="SKU", Default:="")
            If strReply <> vbNullString Then rngMycell.Value = strReply
        End If
        
        If rngMycell.Value = "" Then
            strReply = InputBox(Prompt:="SCAN OR ENTER SKU", _
            Title:="SKU", Default:="")
            If strReply <> vbNullString Then rngMycell.Value = strReply
        End If
        
        If MsgBox("SCAN THE NEXT ITEM?", vbYesNo) = vbNo Then Exit Sub
        
    Next rngMycell
    
    End With
End Sub
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,083
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
Sub Button174_Click()

   Dim Reply As String
   Dim NxtRw As Long
   
   Do
      On Error GoTo NoRoom
      NxtRw = Range("A24:A53").SpecialCells(xlBlanks)(1).Row
      On Error GoTo 0
      Reply = InputBox(Prompt:="SCAN OR ENTER SKU", _
         title:="SKU", Default:="")
      If Not Reply = vbNullString Then
         Range("A" & NxtRw).Value = Reply
   
         Reply = InputBox(Prompt:="QUANTITY", _
            title:="QTY", Default:="")
         If Not Reply = vbNullString Then
            Range("H" & NxtRw).Value = Reply
         End If
      End If
      If MsgBox("SCAN THE NEXT ITEM?", vbYesNo) = vbNo Then Exit Sub
   Loop
Exit Sub
NoRoom:
   MsgBox "No more blanks in col A"
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,865
Messages
5,598,530
Members
414,245
Latest member
Major Aly

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top