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
 
Sort by date Sort by votes

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Hi the QTY doesn't show up now... just Enter SKU and asks for any other items...

the way I'm fixing it is that it's gotta start with a24 and go as far as a53 but nobody will be able to enter items in the middle of that range for example.. so I don't think I need it to skip anything..

way back I thought I was not bad at this excel, but WOW, you are geniuses :) Thanks for the help
 
Upvote 0
@Fluff it is amazing! Love that last code you did... works perfectly!!!! Thank you. And thank you @ Richard U also! I learnt a lot today from both of you! cheers!
 
Last edited:
Upvote 0
Glad we could help & thanks for the feedback.

PS in future when posting code could you please use code tags (the # icon in the reply window).
Cheers
 
Upvote 0
@Fluff can I throw something else at ya? ;) Now that item SKU number... gets put in cell A24, and H24 has the QTY, the others.. C24 for example, Vlooks up A24 in my product page, and find the description of that product, the F column for example looks up the pricing, and so on... Now, I also did a countif that range in the product page matches the item number entered in a24. If it does, I have 024 hidden with a YES (a match) or NO, but if it's a NO, the vlookup shows as N/A as usual.... but how can your above code, see that the item code scanned or punched in, is not accurate or in my system? and stop the tool or even have the person correct it or opt out... whatever makes sense...
cause I do have this type of function on other buttons I have done... but I'm not sure where to put it in your code here... Hope it's o.k. that I ask.
Thanks
Andre
 
Last edited:
Upvote 0
How about
Code: 
Sub Button174_Click()

   Dim Reply As String
   Dim NxtRw As Long
   Dim Fnd As Range
   
   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
         Set Fnd = Sheets("[COLOR=#ff0000]Product[/COLOR]").Columns([COLOR=#ff0000]1[/COLOR]).Find(Reply, , , xlWhole, , , False, , False)
         If Fnd Is Nothing Then
            MsgBox "SKU not found"
            Exit Sub
         End If
         Range("A" & NxtRw).Value = Reply
         Range("C" & NxtRw).Value = Fnd.Offset(, [COLOR=#ff0000]1[/COLOR]).Value
         
         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
If the SKU is not found the sub will exit, if it is found it will put the value in the next column into col C.
Change values in red to suit
 
Upvote 0
@Drdre Glad we could help. :D

Just a word to the wise. If you ever find yourself coding something more than twice, there's usually a loop that can help.

We also took two different approaches. Fluff cycled through the rows, I cycled through the range, then used an offset. Either way works. As you can see there are lots of different approaches to the same problem. Whatever style works best.

And you're right, you're not bad, you're just learning, as are all of us.

I've been doing this for 20 years and am still learning.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Help to exit VBA upon cancel on input box
Replies
3
Views
490
Domenic
D
G
  • Solved
run VBA when clicked
Replies
4
Views
94
Gylle
G
M
  • Question Question
Prevent worksheet change from triggering when similar macro is executed?
Replies
1
Views
97
AlphaFrog
AlphaFrog
P
  • Question Question
Worksheet and cell value matched to range
Replies
1
Views
501
Farscape2020
F
S
  • Question Question
How to change scope (Workbook to Sheet) of Formula Name after duplicated with Sheet?
Replies
8
Views
1K
SunnySchindler
S

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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