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

D

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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
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
 
Upvote 0
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:
Upvote 0
Two good approaches here for you.

One using the range option combined with

"For each... next" logic

One using "Do....loop" logic.
 
Upvote 0
@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
 
Upvote 0
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:
Upvote 0
@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:
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Worksheet and cell value matched to range
Replies
1
Views
354
Farscape2020
F
S
  • Question
How to change scope (Workbook to Sheet) of Formula Name after duplicated with Sheet?
Replies
8
Views
661
SunnySchindler
S
M
  • Solved
VBA comparing cell values dosen't work consistantly
Replies
6
Views
102
michaelehoudek
M
Agnarr
  • Question
VBA problem with creating new sheets
Replies
0
Views
336
Agnarr
Agnarr
S
  • Question
Need code that adds multiple new rows to table to include a check box in column "L" for each new row
Replies
0
Views
312
sspatriots
S

Forum statistics

Threads
1,214,575
Messages
6,120,342
Members
448,956
Latest member
Adamsxl

Share this page

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
Back
Top