Selecting a cell

[olenavychief]

I used to be a pretty fair BASIC Programmer, but I haven't been able to
devote enough time to learn VBA for Office, I've never done anything that
needed it before. I'm sure this is a really easy one, but I'm really a newbie
to this, so any help would be appreciated.
Here is the situation:
I am trying to post inventory into an excel spreadsheet so that I can import
it into a custom label program to print price & description barcoded labels.
I export the inventory from my accounting program directly into an Excel
workbook, no problem there. Only 4 columns are exported:
"ItemNum" "Desc" "QuanOnHand" "Price"
I add a column heading "NumLabels" so that I know how many labels of any
particular item need printing. This is always the 5th column.
Then using my received inventory's packing slips I can search for the item
numbers, tab over to the "NumLabels" column and enter how many labels to
print.
Here's the problem - that's a LOT of typing! I CTRL-F to get the Find
Dialogue, type the item number I'm looking for, click search, close the Find
Dialogue, tab right 4 cells, type the number of labels - then CTRL-F etc. etc.
I'v tried recording a MACRO a zillion times, but it doesn't capture the
correct keypresses, so no go.
Ideally, I'd like to hit a key, have a find dialogue pop-up, type the item
number, find it, and when closed, automatically tab 4 spaces so I may enter
the number of labels, then open the find dialogue again, basicly a loop until
I type CTRL-X or something to stop it. Can this be done?

 

[Leith Ross]

Hello Olenavychief,

Since you already have the columns labeled, Excel has a built in dialog
to allow you to do what you want. After you have added the "NumLabels"
heading to the 5th column, go to the Excel menu and select DATA, and
then FORM. This will display a custom dialog based on your worksheet
data. It includes edit controls and Ctrl + F find features. This will
do what you need without having to program anything.

Sincerely,
Leith Ross

 

[olenavychief]

Guess I wasn't too clear - I'm searching a list of over 6000 items, but I may
be only printing labels for 100 or so random items, whatever stock came in
this week. When I select Form, the only "Find" feature I have is Find Prev /
Find Next buttons (I'm using Excel 2K3 SP2 if that makes a difference) If I
use the Criteria button to find the item I click Criteria, type in the item
I'm looking for, hit tab 4 times, type the number of labels, click criteria
etc. Unless I'm incredibly dense (which I may be, don't get me wrong...)
this is not much different than what I was doing before, except I'm not doing
CTRL-F, I'm clicking Criteria. (It does, however, save closing out the find
dialogue, which saves a step on each item, so I'll be using this tip until I
can figure out how to do what I really want, Thanks Leith!) I was hoping for
some automation, like type in the item number, cursor jumps immediately to
the numlabels field and then waits for input, then jumps immediately to the
item number entry, waits etc So I'd type in item# <enter> NumLabels <enter>
item# <enter> NumLabels <enter> until I click the close button. That way I
could use the number pad like a 10 key and really save a lot of time.

 

[Leith Ross]

Hello Oldnavychief,

I wasn't sure it you really needed a custom automated solution, but it
looks as though you do. To do so requires using VBA to create a User
Form with the appropriate controls and code. Being a novice, it would
be easier and faster for me to code a possible solution in a workbook
for you rather than walk you through the process by posting in the
newsgroup. If you lke I can do that and email the workbook to you.

Sincerely,
Leith Ross

 

[DataCollector]

Quick question...how do you receive your packing slips? Are they 'real
packing slips or are they in some sort of spreadsheet too?

Regard

 

[Dave Peterson]

Another option...

Select A:E (headers in Row 1 only).
Data|Form
You can click the criteria button
search for your Item number
tab to the correct field and type the new number you want.

==========

But if you want a cheap and dirty macro...

Option Explicit
Sub testme()
Dim FoundCell As Range
Dim FindWhat As String
Dim HowMany As Long

HowMany = 3

With Worksheets("sheet1")

Do
FindWhat = InputBox(Prompt:="Item Number?")
If Trim(FindWhat) = "" Then Exit Do

With .Range("a:a")
Set FoundCell = .Cells.Find(what:=FindWhat, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, searchorder:=xlByRows, _
searchdirection:=xlNext)
End With

If FoundCell Is Nothing Then
Beep
MsgBox "Not found!"
Else
HowMany = Application.InputBox(Prompt:="How Many for: " _
& FoundCell.Address(0, 0), _
Default:=HowMany, Type:=1)
If HowMany < 1 Then Exit Do
FoundCell.Offset(0, 4).Value = HowMany
End If
Loop
End With

End Sub

It quits when you hit cancel for either inputbox. And it remembers the previous
quantity--if you don't like that, change the default to what you use the most.