How to "push" a value into a cell

  • Thread starter Thread starter Mark Flaxman
  • Start date Start date
M

Mark Flaxman

Hi, how do I "push" a value into a cell?

What do I mean?

I know that if I have cell A1, I can make the contents of
that cell equal to the value of any other cell in a
workbook. The easiest way is to enter

the formula in cell A1, "=B1" (where B1 holds the
contents that I want A1 to hold). I can even make a
conditional formula, eg if A1 is empty, fill it

with the contents of B1, else leave it alone.

But, what if I have a range of cells, eg A1 to A9 that I
want to fill, but only from top down, and only if the
cell is empty and the previous cell in the

range is not? eg;

A1 = Fred
A2 = John
A3 = Mary
A4 = James
A5 = ""
A6 = ""
A7 = ""
A8 = ""
A9 = ""

I want A5, the next empty cell in the range, to hold the
name Anne, but not the remainder of the empty cells?

I can set up a flag, say in the range C1 to C9, so that
if any cells in the A1 to A9 range are empty the flag
cell shows "0", or if any are not, the

flag cell shows "1", or if the cell above is empty, the
flag cell shows "2", eg

0 = empty, 1 = not empty, 2 = empty but the cell above
is also empty

C1 = 1 (A1 is not empty)
C2 = 1 (A2 is not empty)
C3 = 1 (A3 is not empty)
C4 = 1 (A4 is not empty)
C5 = 0 (A5 is empty)
C6 = 2 (A6 is empty but so is the previous cell)
C7 = 2 (and so on)
C8 = 2
C9 = 2


This way, I can use the MATCH function to find out that
the next empty cell is in row 5.

But then, if a user inputs into cell D1 the name Anne,
how do I "push" that value from D1 into the next
available empty cell, A5, and not all the

other empty cells, A6 to A9?

I feel I should be using the INDIRECT function somehow,
but I cannot get my head around the problem.

Any guidance would be appreciated.

Er.... I would rather not use VBA if I can avoid it. My
brain is too small to start to learn yet another
programming style, :)

Many thanks,

Mark
 
Mark,

Label your column of names with a title (say Names)
The names you filled are now in A2:A5

Select the range of names (and maybe a few addional rows.
Data / Form ; Click OKbutton.
On the inputform now click New record and type Anne and push the Enter key

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Hi m8,

Thanks for your advice.

I had tried Data / Form / New but I don't think this is
the answer I need.

I have now "named" the range A2 to A10 with a name, and
tried Data / Form again, but it's not working for me. It
is probably me, but I am not sure I understand your
solution to my problem.

I need to capture what has been input by someone else
elsewhere on the form. Data / Form doesn't seem to be
able to capture from a different cell reference.

Bu thanks anyway for your time and advice

Mark Flaxman
 
Mark,

I don't think that you can do what you want to do ( append to a range the
value of a *cell* elsewhere on the sheet) other than by VBA.

The solution I gave (and that the reason why i gave it) does this, although
the input then is not from a cell, but via a form. I'l keep an eye on it to
watch if someone else does come with a solution.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Yes I think you're right, it is not possible without
using VBA.

I got your Data / Forms method to work. It "was" me who
was wrong. I didn't understand what it was doing, but
Debra's web-site in the replies above these had a link
which pointed me to the right way.

So, I can now enter data in the next empty cell, (or
using a 2D table range, I can input data in the next
empty row), But it isn't quite what I require. I'm almost
there.

I don't trust users to be able to find Data / Forms,
either from the menu or using shortcut keys, and then to
press "New", without making an error and corrupting the
worksheet. I am not belittling the users, it is just that
they are not comfortable with spreadsheets,and I wanted
to keep the process as simple as possible.

I need to create a macro button that, when they are ready
to enter a new record, they press the button, and the
Data / Form dialogue appears, with "New" already selected.

Macro's don't work, because I can't "stop" recording a
macro with the Form dialogue box open.

How else can I do this?

I appreciate all the previous advice. It is getting me
there, :)

I am willing to tackle a VBA solution if there is one, :(

Mark Flaxman
 
Debra,

Many thanks for this. That web page has a link to another
which helped me a lot.

Thanks again

Mark Flaxman
 
The following code will open the data form with a new record selected.
Change the sheet name and cell reference to match your worksheet.

'==============================
Sub OpenDataForm()
Dim ws As Worksheet
Set ws = Worksheets("Data")

SendKeys "%w"
ws.Range("A1").Worksheet.ShowDataForm
End Sub
'======================
 
Debra,

I'm really sorry but I cannot get this to work.

My Input worksheet is named Input. From there the user
will click a button to transport her/him to Sheet1, (a
temporary name for the sheet where the data is to be
stored).

So the macro must be assigned to a button on the first
sheet, (Input), which will open up the second sheet,
(Sheet1), and then open up the data form.

The data range on Sheet1 is A4:H33, (where A4:H4 are
column headers).

My knowledge of VBA is very weak, and the runtime error I
get means nothing to me. I get a runtime error '13' type
mismatch, and debugging points to "Set ws = Worksheets
(Sheet1).

What have I done wrong?

By the way, what is the SendKeys "%w" expression for?

Sorry,

Mark Flaxman
 
Enclose the sheet name in quotation marks --

Set ws = Worksheets("Sheet1")

In the Data Form, the accelerator for the New key is the w. You could
activate it from the keyboard by pressing Alt+w.
Used with the SendKeys statement, % represents the Alt key, so this line
of code is like pressing Alt+w.
 
Still not working, I still get the run time error code 13
type mismatch.

And ALT+w just brings up the "Window" menu.

Many thanks all the same. I will have to re-think my
strategy
 
Try naming the data range on sheet1 "Database", using a dynamic formula.
There are instructions here:

http://www.contextures.com/xlNames01.html#Dynamic

Your formula should be:

=OFFSET(Sheet1!$A$4,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$4:$4))

If you can't get that to work, and would like to send me a copy, remove
the capital letters from my email address.
 
Mark, Alt+w will only bring up the Window menu if you use it outside of the Data
Form. Debra's suggestion was to use it with sendkeys as part of the routine
*after* the routine has activated the form. If you activate the form manually
using data / Form and then hit Alt+w then you should see it change to new entry.
What is the exact code you are using - Copy and paste it from your module, don't
try and rewrite it in the note ( And just for the record, If Debra tells you it
works, it works, period ;-> ).

There is no limit to the number of posts you can make here until you get this
doing exactly what you want it to do, and so far there is no reason why this
should not be a fairly simple exercise, so hang on in there.
 
rotflmao
If you can't get that to work, and would like to send me a copy, remove
the capital letters from my email address.
Click to expand...

Debra - Was literally sat here thinking that I would have put money on that
being the next step as it popped in not 2 mins ago. :-)
 
Hi Ken,

Thanks for your words m8. I must admit I was anxious that
I was wasting peoples time here, especially Debra's, with
my own lack of ability in understanding a simple VBA
procedure and how to debug it.

I really need to learn VBA before I start asking for VBA
solutions, but I keep putting it off, because of the time
and effort it's going to require.

I have just read Debra's latest reply below this one. I
will have a go at this first and let you both know how I
get on.

Mark
 
The whole point of the groups is to get you sorted, so until you are sorted we
are not done. :-)

I would take advantage of Debra's offer to look at the file if I were you. If
you were paying for consultancy, it would cost you an arm and a leg for Debra to
do this, but you have the offer for free - Personally I'd use it :-)

Even when Debra has it sorted for you, if you need help in understanding any/all
of the various steps just feel free to post and ask - Folks here tend to get
real helpful indeed when people show a desire to actually understand some of the
solutions posted, and you won't be short of volunteers for explanations. Good
luck.
 
Debra & Ken,

Thanks for persisting with me with this.

I have re-named my data range as Database and used the
formula:

=OFFSET(Record!$A$4,0,0,COUNTA(Record!$A:$A),COUNTA
(Record!$4:$4)) in the defining name dialogue.

I have had to change the worksheet name from Sheet1 to
Record. This is because I managed to debug the error I
was originally getting. In the VBAProject list under
Microsoft Excel Objects I saw a list of sheets from
Sheet1 (Start) to Sheet6 (Codes), and the sheet I was
using as Sheet1 was actually Sheet4 (Sheet1). I figured I
was confusing the VBA program with which sheet to look at.

My new VBA code looks like this:


Sub OpenDataForm()

Sheets("Record").Select ' comment 1
Dim ws As Worksheet
Set ws = Worksheets("Record")

SendKeys "%w"
ws.Range("Database").Record.ShowDataForm
End Sub

Comment 1 - I have to first get the macro to move the
user to the Record sheet before opening the Data/Form

When I run this macro I now get the VB error "Run-time
error '438': - Object doesn't support this property or
method"

When I debug,
the line "ws.Range("Database").Record.ShowDataForm"
is highlighted.

If I change the line to;
"ws.Range (Database).Record.ShowDataForm",
(ie, remove the quotation marks from Database), then run
the macro, I get the error "Run-time error '1004': -
Method 'Range' of object '_Worksheet' failed".

So, what have I done wrong? Any ideas?

Mark

PS, I am willing to send a copy to either of you, but
would rather not, There are a lot of entries on the
spreadsheet I am using as test data, and it's all a bit
confidential, :)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
The line before End Sub should be:

ws.Range("Database").Worksheet.ShowDataForm

And I've tried the code in Excel 97, Excel 2000 and Excel 2002, and
didn't need to select the Record sheet in order for the Data Form to
work. The revised code is:

'=================
Sub OpenDataForm()

Dim ws As Worksheet
Set ws = Worksheets("Record")
SendKeys "%w"
ws.Range("Database").Worksheet.ShowDataForm
End Sub
'=====================
 
Debra, just a thought, but wasn't there an issue in earlier versions where if
the database range did not start on any of the first 2 rows, then it didn't like
it. I seem to remember I had a similar issue with a post recently and am sure
it was one of your posts that put me straight. A possibility perhaps?
 
Back
Top