Input Box for data-entry

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paul - 29 Nov 2007 14:11 GMT

Hi there,
Help please, this is driving me mad!
This is a simplified version:

A B C D E F G
R1 Code Open In Out Book Physical Diff
R3 1001 100 50 75 125 130 5
R4 1011 50 0 0 50 50 0
R5 1017 0 100 50 50 50 0
.....
R374 Total
All cols are formulas apart from "physical" and what I'm trying to get are
two bits of code that:
a) Zeros down the the Physical col from row 3 to the row above the Total
(the sheet grows/shrinks as items are added or deleted) and then pops up an
input box that asks "enter stock for Code ...." for every code (in order)

and
b) (This is for after the differences have been resolved) an Input box that
asks for the code and then the new Physical quantity.

Many Thanks

Paul

Reply to this Message

DomThePom - 29 Nov 2007 18:09 GMT

Here is code for the first part - you can work out the rest yourself!

Sub EnterData()
Dim rng As Range
Dim cell As Range
Set rng = ActiveSheet.UsedRange.Find("Physical", LookIn:=xlValues)
Set rng = rng.Resize(rng.CurrentRegion.Rows.Count - 2).Offset(1, 0)
rng.Value = 0
For Each cell In rng.Cells
rng.Select
cell.Value = InputBox("Enter quantity for code " & cell.Offset(0,
-5), _
"Enter Physical Quantities", 0)
Next cell

End Sub

> Hi there,
> Help please, this is driving me mad!
[quoted text clipped - 20 lines]
>
> Paul

Reply to this Message