I am in a situation where we're using an Excel sheet to massively inpu
a lot of data into a database. The Excel sheet with its data is bein
imported into the database. Anyway, the problem focuses on th
efficiency of the Excel sheet...

To illustrate a clear example:
COLUMN_A : Artists
COLUMN_B : Albums
COLUMN_C : Songs

For certain reasons it's necessary to have the input like that: for a
artist albums are enlisted and for each album the songs. It means tha
with the entry of each new song for an already specified Artist an
Album I have to repeat the artist and the album.

This can be done by means of dragging the value with the mouse an
filling all fields in the column that are the same. But it has to b
more efficient.... automatically. That's a big demand right now fro
some datatypist who want to focus on entering as much as possible i
little time. For us, we don't work with Artists, Albums and Songs..
but it gives you an idea of what it's about :)

What I'd like is to specify an Artist with an Album and start with th
first song. I press [enter] and the focus is on the Song field in th
next row. I'd like to create a Macro and event catcher so that wit
each [enter] (entering the next row) the values of the fields fo
COLUMN_A and COLUMN_B are copied from the previous row. If it's a ne
album or artist at some point... a user can change it and from tha
point on that album or/and artist will be used as previous value.

Problem. I'm not at all experienced in Excel programming. I guess tha
this may be quite a simple trick or macro to get it to work, but for m
it's a big problem. Tried some things, but ended up with nothing tha
worked even remotely.

I couldn't find some good tutorial on getting this issue solved. It'
not my wish to learn Excel extensively. It just happens that out of th
blue we end up with one very wished for feature of the Excel shee
(template) which is used a lot.

Anybody got some tips on the Macro itself... and secondly how to get i
to execute automatically on entering a new COLUMN_C field in the nex
row (trigger)?

Thanks BIG time if someone can help :

--
Lav
-----------------------------------------------------------------------
Lava's Profile: http://www.excelforum.com/member.php...fo&userid=2779
View this thread: http://www.excelforum.com/showthread.php?threadid=47311

Lava,
Maybe...
Enter only the song title and add the artist and album each time
they change. Then use John Walkenbach's tip here...
http://www.j-walk.com/ss/excel/usertips/tip040.htm
to fill in the blank cells after the data entry is complete.

Jim Cone
San Francisco, USA


"Lava"

wrote in message
...

I am in a situation where we're using an Excel sheet to massively input
a lot of data into a database. The Excel sheet with its data is being
imported into the database. Anyway, the problem focuses on the
efficiency of the Excel sheet...
To illustrate a clear example:
COLUMN_A : Artists
COLUMN_B : Albums
COLUMN_C : Songs
For certain reasons it's necessary to have the input like that: for an
artist albums are enlisted and for each album the songs. It means that
with the entry of each new song for an already specified Artist and
Album I have to repeat the artist and the album.

This can be done by means of dragging the value with the mouse and
filling all fields in the column that are the same. But it has to be
more efficient.... automatically. That's a big demand right now from
some datatypist who want to focus on entering as much as possible in
little time. For us, we don't work with Artists, Albums and Songs...
but it gives you an idea of what it's about :)

What I'd like is to specify an Artist with an Album and start with the
first song. I press [enter] and the focus is on the Song field in the
next row. I'd like to create a Macro and event catcher so that with
each [enter] (entering the next row) the values of the fields for
COLUMN_A and COLUMN_B are copied from the previous row. If it's a new
album or artist at some point... a user can change it and from that
point on that album or/and artist will be used as previous value.

Problem. I'm not at all experienced in Excel programming. I guess that
this may be quite a simple trick or macro to get it to work, but for me
it's a big problem. Tried some things, but ended up with nothing that
worked even remotely.

I couldn't find some good tutorial on getting this issue solved. It's
not my wish to learn Excel extensively. It just happens that out of the
blue we end up with one very wished for feature of the Excel sheet
(template) which is used a lot.
Anybody got some tips on the Macro itself... and secondly how to get it
to execute automatically on entering a new COLUMN_C field in the next
row (trigger)?
Thanks BIG time if someone can help :)--
Lava

Hmmz, I see... so instead of an immediate fill, the filling of the empty
fields is done in one go at the end of the data entry. Still it involves
quite a few clicks and selections and such. Would it be possible to
achieve something like this by means of a Macro and perhaps a button
(or shortcut) otherwise?

I haven't given up hope yet on the immediate fill, but a Macro and
button (shortcut) involving _nothing_ more, but a simple click at the
end of the entry would suffice as well.


--
Lava
------------------------------------------------------------------------
Lava's Profile: http://www.excelforum.com/member.php...o&userid=27793
View this thread: http://www.excelforum.com/showthread...hreadid=473113

Lava,

You can experiment with the following code, which should be
placed in the sheet module of the data entry sheet.
It assumes the data entry is in Column C.
Jim Cone
San Francisco, USA

"---------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo BadFill
Application.EnableEvents = False
Dim rngToFill As Excel.Range
If Not Application.Intersect(Target(1), Columns("C")) Is Nothing Then
Set rngToFill = Range(Target(1, -1).Address, Target(1, 0).Address)
If Application.CountA(rngToFill) = 0 Then _
rngToFill.Value = rngToFill.Offset(-1, 0).Value
Target(2, 1).Activate
Set rngToFill = Nothing
End If
BadFill:
Application.EnableEvents = True
End Sub
'----------------------------


"Lava"

wrote in message
...

Hmmz, I see... so instead of an immediate fill, the filling of the empty
fields is done in one go at the end of the data entry. Still it involves
quite a few clicks and selections and such. Would it be possible to
achieve something like this by means of a Macro and perhaps a button
(or shortcut) otherwise?

I haven't given up hope yet on the immediate fill, but a Macro and
button (shortcut) involving _nothing_ more, but a simple click at the
end of the entry would suffice as well.--
Lava

Okay, small update...

I've decided to use a piece of code by Dave Peterson. It can be found
in the attached zipfile, in an example Excel file. The subroutine is
named FillColumnBlanks(). It's a routine attached to a button and
executed at the end of all input.

I chose for this alternative for the time being because of the one-time
execution. If a macro is executed each time a line is finished it might
delay things and cause problems when the person working with it is
doing some massive and quick input typing blind from a papersheet.
Triggering per entry is something I do keep in mind for testing.

Problem with it is defining the range. When you open the Excel file
you'll find a column named "Gebouw" on the left. I've basically defined
8 lines, but the value in "Gebouw" and "Ruimte" can be repeated until a
new value is being defined. By means of the button "Vul lege velden"
(fill empty fields) it should fill things till line 8. HOWEVER... it
fills it till line 42 or something.

It would appear that Excel keeps a wrong Last Row in mind. Maybe I once
had a value in line 42 and deleted it? How can I let Excel find the REAL
last row which is line 8 in this case?

P.S. the buttons are on top (above "Gebouw" and "Ruimte")


+-------------------------------------------------------------------+
|Filename: Import Shouwing.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3907 |
+-------------------------------------------------------------------+

--
Lava
------------------------------------------------------------------------
Lava's Profile: http://www.excelforum.com/member.php...o&userid=27793
View this thread: http://www.excelforum.com/showthread...hreadid=473113

Sorry, I was a bit too hasty with my question.

I think I've found the solution at:
http://www.beyondtechnology.com/geeks012.shtml

Using this piece of code to define the LastRow seems to help:

Code:
--------------------
LastRow& = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
--------------------


Posting it as it may be of use to others as well.


--
Lava
------------------------------------------------------------------------
Lava's Profile: http://www.excelforum.com/member.php...o&userid=27793
View this thread: http://www.excelforum.com/showthread...hreadid=473113