Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 33

Thread: Solved: Saving to various sections on the same sheet

  1. #1
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location

    Solved: Saving to various sections on the same sheet

    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

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Surely you would just replicate the other code to different target areas.

  3. #3
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    Are the target areas declared as ranges?

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    I would have thought so.

  5. #5
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    So it simply copies to the last row plus one of each dynamic range?

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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.

  7. #7
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  8. #8
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    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.

  9. #9
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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.

  10. #10
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    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

  11. #11
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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]
    Last edited by Bob Phillips; 12-19-2006 at 06:41 AM.

  12. #12
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi Ted,
    Here's a modfification of your code which should accept new entries also.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  13. #13
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    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

  14. #14
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    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!!
    Last edited by Aussiebear; 12-21-2006 at 12:18 AM.

  15. #15
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    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

  16. #16
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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).

  17. #17
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by Aussiebear
    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.

  18. #18
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    Quote Originally Posted by xld
    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

  19. #19
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    Quote Originally Posted by xld
    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

  20. #20
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •