PDA

View Full Version : Solved: Saving to various sections on the same sheet



Aussiebear
12-18-2006, 04:34 AM
Having much earlier seen an example of copying and deleting a row to a different sheet, I was wondering how I can enter data via form which saves the information to different sections on the same sheet?

In the example below, I have expressed my recent sporting achievements as an example. (The more astute of you will note that I am a future olympic champion in the making.)

Ted

Bob Phillips
12-18-2006, 04:48 AM
Surely you would just replicate the other code to different target areas.

Aussiebear
12-18-2006, 05:08 AM
Are the target areas declared as ranges?

Bob Phillips
12-18-2006, 05:26 AM
I would have thought so.

Aussiebear
12-18-2006, 12:00 PM
So it simply copies to the last row plus one of each dynamic range?

Bob Phillips
12-18-2006, 01:05 PM
I am struggling to say yes here as I don't know what you are copying, where to, or know the code. All I know is about your athletic prowess.

mdmackillop
12-18-2006, 01:12 PM
Hi Ted,
From the look of your worksheet you need to determine the last cell in column B and then set your target range to be offset by 2 rows and -1 columns. You can use this with further offsets to put textbox contents in the appropriate locations.

Aussiebear
12-19-2006, 04:30 AM
MD, I have some code written but I need to firstly establish which event the new data applies too and then find the last row within that particular range.

What I need to know firstly, is... does the line " Set Tgt = Cells(ActiveSheet.Columns(10.Find(What:=Event, lookat:= xlWhole).Row,1) find the correct type of event?

and secondly can I then use the offset to find the last row within this section?

Ted

BTW my apologies to Bob for the wrong example posted.

Bob Phillips
12-19-2006, 04:40 AM
Yes that gets the event, and you can get the free line below that wit



Set Tgt = Tgt.Offset(0, 1).End(xlDown).Offset(1, 0)


then insert a row to add the new data



Tgt.EntireRow.Insert

But will you allow new events to be added via the form? And should the event box be a combobox pre-loaded, and you should allow incomplete dates to be entered and auto-complete it.

Aussiebear
12-19-2006, 05:02 AM
For now Bob, no further events will be added as this is just an example for me to learn with. Yes the events box should be a combobox pre-loaded, and yes autocomplete of dates is a great idea.

However, since I'm only just learning to crawl ( with vba) I'm working on one issue at a time. Please be patient with me.

I have added your lines as per your instruction and it indeed finds the correct event type and adds a new line to the bottom of that event range, but I've not yet written it correctly to send the data to the new line. I shall make that my goal for tomorrow night.

Ted

Bob Phillips
12-19-2006, 06:04 AM
Writing it is straightforward.

With the new target defind in column B,



Tgt.value = txtDate.Text
Tgt.Offset(0,1).value = txtResult.Text
Tgt.Offset(0,2).value = txtComment.Text

mdmackillop
12-19-2006, 03:03 PM
Hi Ted,
Here's a modfification of your code which should accept new entries also.

Aussiebear
12-20-2006, 06:09 AM
Thanks MD & Bob. Just one other question here if I may, if I wanted to write to a different sheet can I use the following to set the target? "Set Target = Cells (ActiveSheet "Sheet Name".Columns(1).Find(what:=Event, lookat:=xlWhole).Row,1) if the format was the same?

Ted

Aussiebear
12-20-2006, 06:09 AM
Thanks MD & Bob. Just one other question here if I may, if I wanted to write to a different sheet can I use the following to set the target? "Set Target = Cells (ActiveSheet "Sheet Name".Columns(1).Find(what:=Event, lookat:=xlWhole).Row,1) if the format was the same?

Ted

Edit: Hey Zack, stop nodding off and delete this 2nd post please.

PS Yes I will go stand in the corner for the next 3 hours to rethink my misdemeanor!!

Aussiebear
12-22-2006, 04:57 PM
Sorry guys but one more late question. What happens if the various sections had sub sections contained within them?

As in 100 mtrs - under 21's , under 35's, under 50's, etc?

We have been able to select the type of event from the code supplied before but now we need to do a second selection as well (or a selection based on two criteria)

Is this correct?

Set Tgt = ActiveSheet.Columns(1, 2).Find(What:= 1st criteria.text, 2nd criteria.text, Lookat:=xlwhole)

Ted

Bob Phillips
12-22-2006, 05:30 PM
What you would do here Ted is, first do a find for 100 mtrs. If found, then do a further find for the second criteria, under 21's saya, BUT, make sure that the row if found is not greater than the last row in the 100 mtrs section, in case you find under 21's in the next section (of course this data management code is superfluous if you know exactly waht the data will look like, and manage that within the code).

Bob Phillips
12-22-2006, 05:32 PM
Thanks MD & Bob. Just one other question here if I may, if I wanted to write to a different sheet can I use the following to set the target? "Set Target = Cells (ActiveSheet "Sheet Name".Columns(1).Find(what:=Event, lookat:=xlWhole).Row,1) if the format was the same?

Ted

Edit: Hey Zack, stop nodding off and delete this 2nd post please.

PS Yes I will go stand in the corner for the next 3 hours to rethink my misdemeanor!!

Just noticed this post Ted.

the code you show does the Find in another sheet, not the writing.

Aussiebear
12-22-2006, 06:02 PM
Just noticed this post Ted.

the code you show does the Find in another sheet, not the writing.

Thanks Bob. I sort of knew what I meant, even if it confused everyone else.

At the time of posting I was thinking about the concept of having different events on different sheets but have since moved on to seeing if i could write to subsections on the same sheet.

ted

Aussiebear
12-22-2006, 06:07 PM
What you would do here Ted is, first do a find for 100 mtrs. If found, then do a further find for the second criteria, under 21's saya, BUT, make sure that the row if found is not greater than the last row in the 100 mtrs section, in case you find under 21's in the next section (of course this data management code is superfluous if you know exactly waht the data will look like, and manage that within the code).

As in..
Set Tgt = ActiveSheet.Column(1).Find(What:=Me.Combobox1.Text, Lookat:= xlWhole)
Set Tgt = ActiveSheet.Column(2).Find(What:=Me.Combobox2.Text,Lookat:=xlWhole)

providing the Combobox 2 contained the different age groups?

Ted

Bob Phillips
12-22-2006, 06:20 PM
You should add the After argument to the second Find, so that it starts where the first find left off, After:=Tgt.Offset(0,1).

And don't forget to test you haven't gone too far (if necessary).

Aussiebear
12-22-2006, 07:55 PM
Rofl.... its like getting my teeth pulled.

For the second find it then should read

Set Tgt = ActiveSheet.Column(2).Find(After:=Tgt.Offset(0,1), What:=Me.Combobox1.Text,Lookat:= xlWhole)

Ted

Bob Phillips
12-23-2006, 02:56 AM
I haven't tested it, but that looks about right.

Aussiebear
12-23-2006, 04:11 AM
Bob it just inserts the data on the bottom row of the event, not to the bottom row of the sub event.

Ted

Bob Phillips
12-23-2006, 06:39 AM
Ted,

This gets a bit tricky.

Does your sub sections have blank lines between it and the next sub-section within that evet, or are the only blank lines between events?

Aussiebear
12-23-2006, 02:09 PM
Yes blank lines between the subsections

Aussiebear
12-23-2006, 04:02 PM
I might just drop the concept of writing to various subsections on the one sheet and replace the idea with setting up different sheets for different events.

Ted

Cyberdude
12-24-2006, 04:58 PM
Hey, AussieBear, shall we assume you are seeking a medal in track? (Or perhaps synchronous water dancing??) You can do it!! Go AB, GO!

Aussiebear
12-25-2006, 02:32 AM
Hey Cyberdude, thanks for the motivation. Merry Christmas to you

Aussiebear
12-27-2006, 12:59 AM
Curosity got the better of me, and I went back over the post of MD's, and after correcting some error's in the code (by me not by MD), .....

....it works!!!! Woo Hoo somebody give that boy a hug.

I haven't been this excited since I came second in french in year 10. There were only two of us in the class but what the hell, I came second.

Ted

mddr
12-27-2006, 01:12 AM
You should add the After argument to the second Find, so that it starts where the first find left off, After:=Tgt.Offset(0,1).

And don't forget to test you haven't gone too far (if necessary

Aussiebear
12-27-2006, 03:32 AM
G'day mddr, I wont actually be needing it since I'm off to marry off the principles as outlined by MD with a sheet selection for the job at hand.

No don't panic, my athletic prowess doesn't extend to more than one sheet.

Whilst the code as MD has suggested posts to the right subsection on a single sheet. I have a bigger project in mind. At work we have recently been involved in recieving more than 6000 tons of stubble hay which has been deployed into a number of areas with up to 6 blocks in any one area.

Since I can now write to multi sections on one page this takes care of the areas with a different numbe rof blocks allocated. I will be creating different sheets each named after a different area.

So the problem is kind of solved if you follow my drift. I appreciate your assistance.

Ted

Aussiebear
12-27-2006, 03:38 AM
Hmmm... Hey Bob.... you living in a cave? I just had an echo effect.


You should add the After argument to the second Find, so that it starts where the first find left off, After:=Tgt.Offset(0,1).

And don't forget to test you haven't gone too far (if necessary).

.... cup your ear just so...


You should add the After argument to the second Find, so that it starts where the first find left off, After:=Tgt.Offset(0,1).

And don't forget to test you haven't gone too far (if necessary

Bob Phillips
12-27-2006, 03:59 AM
Well I've never been to Kuwait ...