Printable View
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
Surely you would just replicate the other code to different target areas.
Are the target areas declared as ranges?
I would have thought so.
So it simply copies to the last row plus one of each dynamic range?
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.
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.
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.
Yes that gets the event, and you can get the free line below that wit
[vba]
Set Tgt = Tgt.Offset(0, 1).End(xlDown).Offset(1, 0)
[/vba]
then insert a row to add the new data
[vba]
Tgt.EntireRow.Insert[/vba]
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.
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
Writing it is straightforward.
With the new target defind in column B,
[vba]
Tgt.value = txtDate.Text
Tgt.Offset(0,1).value = txtResult.Text
Tgt.Offset(0,2).value = txtComment.Text
[/vba]
Hi Ted,
Here's a modfification of your code which should accept new entries also.
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
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!!
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
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).
Just noticed this post Ted.Quote:
Originally Posted by Aussiebear
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.Quote:
Originally Posted by xld
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
As in..Quote:
Originally Posted by xld
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
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).
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
I haven't tested it, but that looks about right.
Bob it just inserts the data on the bottom row of the event, not to the bottom row of the sub event.
Ted
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?
Yes blank lines between the subsections
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
Hey, AussieBear, shall we assume you are seeking a medal in track? (Or perhaps synchronous water dancing??) You can do it!! Go AB, GO!
Hey Cyberdude, thanks for the motivation. Merry Christmas to you
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
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
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
Hmmm... Hey Bob.... you living in a cave? I just had an echo effect.
.... cup your ear just so...Quote:
Originally Posted by xld
Quote:
Originally Posted by mddr
Well I've never been to Kuwait ...