MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Trying to write a simple macro to handle input!

  Asked By: Darla    Date: Mar 09    Category: MS Office    Views: 1127

I am tring without much success to write a short macro which will
let me input data and keep asking for input until I answer No to the
question in the message box.

I have three column labels B2 Number, C2 Name, D3 Address

When I click on the button that I attach the macro to I want the
cursor to move automatically into the next empty cell in column B,
write the number which is 1 more than the number above it (so if B2
has 1, B3 has 2 etc), then move automatically into the C column and
wait until I input the Name using an InputBox, then move across for
the input in the D column.

If I answer Yes to the question "enter more data" from the Message
Box the cursor then goes to the next empty B cell and so on.

Not concerned about validation.

When I leave the macro and worksheet I want to return to it and
start inputting on the next available B cell.

Loop is working Ok, Input boxes re Ok BUT cannot get the movement to
the next row and automatic increment to happen. Also cannot go back
and get the cursor to jump into the next empty B cell to start the
numbering from where it was left off the last time.

Any help please?



7 Answers Found

Answer #1    Answered By: Ludkhannah Fischer     Answered On: Mar 09

I think that what you need is the Offset method for the range and cells
You could use the Offset to specify how many rows and cells from a reference
you want get/set values.

cellls(1, 1).Offset(0, 1).value = 4 - One to right from A1 (Sets B2 = 4)
cellls(1, 1).Offset(1, 1).value = 4 - One to right and one down from A1
(Sets B2 = 4)

Using minus (-) on offset values, you specify positions to left and up.

Answer #2    Answered By: Sairish Kauser     Answered On: Mar 09

Seems like a non-macro approach would consume less key-strokes.

1.Put a conditional formula in the 2nd data row, like


2. Copy the formula and paste it down column B
3. Enter the numbet 1 in B1 (assuming that is the first data row)
4. Change Excel movement options on Enter to down via

[Tools][Options][Edit] Move selection after Enter


5. Find the last row filled via placing your cursor in column C and
pressing [Ctrl][down arrow].

Whenever you enter a name in column C, press [Tab]. The number will
appear in column B. Enter the address in column D and press [Enter].
The cursor should go to column C of the next line.

Answer #3    Answered By: Javairea Akram     Answered On: Mar 09

Please post your code if you want help with it.

Answer #4    Answered By: Laura Rodriguez     Answered On: Mar 09

You still working on this?

Is it actually necessary to move the cursor from cell to cell?
(Excel doesn't need it, the macro  can say: Cells(3,5) = "myname")

Is it possible for you to create a form that has (2) boxes
for name and Address, then the "OK" button would insert it.

I can help with the form, if you want.
let me know.

Answer #5    Answered By: Spiru Kelly     Answered On: Mar 09

since I don't know what you have for code so far, let me take a stab at
solving the problems you stated you still have... ok first thing to do
when macro  starts is to get the last row...

function lastrow() as integer
dim lastrow as integer

lastrow = activesheet.range(A65565)end(xlUp).row

now that you have the last row... you can increment the number in b2

.range("B" & lastrow + 1).value = .range("B" & lastrow).value +1

you can also store these values in variables to use when you answer yes to
adding more information...

for example

lastid = lastid + 1

lastrow = lastrow +1

hope this helps... if I knew what code your using now I could be more

Answer #6    Answered By: Jenny Lopez     Answered On: Mar 09

Actually the last row is simpler than that. (also you had a couple of typos)

You just need

Dim lastrow as long
lastrow = ActiveSheet.Range("A65536").End(xlUp).Row

there is no need to move the cursor at all.

This assumes the last row will have data in col A.

Answer #7    Answered By: Aiko Suzuki     Answered On: Mar 09

. later on I tried it myself to make sure I was giving
correct advice... and found the typos... I just hadn't gotten back online to
correct them... thanks for catching me though.. I learn from those

Didn't find what you were looking for? Find more on Trying to write a simple macro to handle input! Or get search suggestion and latest updates.