Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 2 of 2 FirstFirst 1 2
Results 11 to 19 of 19

Thread: On cell value change, lookup header & copy to 2nd range (GBP £30 for commented code)

  1. #11
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,593

    Re: On cell value change, lookup header & copy to 2nd range (GBP £30 for commented co

    Wait..

    There are couple of changes require. PFA this file.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

  2. #12
    Join Date
    12th August 2014
    Posts
    22

    Re: On cell value change, lookup header & copy to 2nd range (GBP £30 for commented co

    Thanks, that's picking up and deleting 'travel' entries now. Fantastic

    I'll be away from my computer for a while - but before I run, two quick questions/queries:

    i) When I enter a name in the site range, and that person isn't available (i.e. already has an entry for that date in the staff range), it's telling me that the site isn't available (e.g. 'Site 4 isn't available' - rather than the person not being available - nothing major but it would be great if it could be fixed.

    ii) Could you explain to to me (in really simple terms if possible), how the code is locating and picking up the headers (particularly the "SiteSCell" bit), as when I transfer the code to my 'real data' it's not recognising up the header line and I'll need to modify accordingly.

    VB:
    Set Headers = Me.Range("SiteSCell").Resize(, 1000).SpecialCells(xlCellTypeConstants) 
    
    
    Sorry about all the questions. Nearly sorted now, hopefully.

    Many thanks.

    Isame

  3. #13
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,593

    Re: On cell value change, lookup header & copy to 2nd range (GBP £30 for commented co

    Quote Originally Posted by isame View Post
    i) When I enter a name in the site range, and that person isn't available (i.e. already has an entry for that date in the staff range), it's telling me that the site isn't available (e.g. 'Site 4 isn't available' - rather than the person not being available - nothing major but it would be great if it could be fixed.
    replace

    VB:
    MsgBox "'" & Cells(Site.Row, Target.Column).Value & "'" & MsgSiteNotAvailable, vbExclamation, MsgBoxTitle 
    
    
    with

    VB:
    MsgBox "'" & Cells(Site.Row, Found.Column).Value & "'" & MsgSiteNotAvailable, vbExclamation, MsgBoxTitle 
    
    
    Quote Originally Posted by isame View Post
    ii) Could you explain to to me (in really simple terms if possible), how the code is locating and picking up the headers (particularly the "SiteSCell" bit), as when I transfer the code to my 'real data' it's not recognising up the header line and I'll need to modify accordingly.

    VB:
    Set Headers = Me.Range("SiteSCell").Resize(, 1000).SpecialCells(xlCellTypeConstants) 
    
    
    I should have told you earlier. SiteSCell is a named range (N5) - starting cell. You need to define this cell(starting header cell) in your original workbook. Also, as you said a blank column will be there to differentiate between staff and site range. I hope this answer your question.

  4. #14
    Join Date
    12th August 2014
    Posts
    22

    Re: On cell value change, lookup header & copy to 2nd range (GBP £30 for commented co

    Hi,

    Thanks, that's picking up the headers now, and I really like the flexibility of using a named cell as the 'starting point'.

    I have noticed a problem/unexpected behavior with the rota though, and I'm not quite sure why it's happening or how it might be fixed.

    The problem occurs with both the test data (with typed data input) and on my 'real data' (which uses dropdown lists), so I don't think it's the data input method per se, but:


    • if I allocate a person to a site and then change my mind and enter a different name (in the same cell), then (even if I've clicked away from the the active cell for a moment) unless I press the 'delete' key in between, it ends up with 2 (or potentially more) people allocated to the same site.



    So, I was wondering:
    • is there a way to stop a user from accidentally doing this, or notify them if they try to?
    • if not, is there a way to look at that row within the staff range, and flag that (for example) Site 2 occurs twice within that row?


    I did try a web search to see if anyone had a solution to this issue, but the closest I could find was:
    http://stackoverflow.com/questions/40538259/excel-vba-delete-data-from-a-worksheet-if-selection-from-dropdown-list-is-chan

    Any thoughts or help would really be appreciated.

    Best wishes,

    Isame

  5. #15
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,593

    Re: On cell value change, lookup header & copy to 2nd range (GBP £30 for commented co

    I don't think the first option would work. I can restrict the user to assign the same site twice.

    Replace these section

    VB:
     '//else put the site name
    Dim SiteDescription As String 
     
    SiteDescription = Trim(Replace(Target.Value, Found.Value, vbNullString, , , 1)) 
    SiteDescription = Trim(Cells(SiteNames.Row, Target.Column).Value & " " & SiteDescription) 
     
    Application.EnableEvents = False 
    Cells(Target.Row, Found.Column).Value = SiteDescription ' Trim(Cells(SiteNames.Row, Target.Column).Value)
    Application.EnableEvents = True 
    
    
    with


    VB:
     '//else put the site name
    Dim SiteDescription As String 
    Dim StaffRow        As Range 
     
    Set StaffRow = Intersect(Staff, Target.EntireRow) 
     
    SiteDescription = Trim(Replace(Target.Value, Found.Value, vbNullString, , , 1)) 
    SiteDescription = Trim(Cells(SiteNames.Row, Target.Column).Value & " " & SiteDescription) 
     
    Application.EnableEvents = False 
    If Application.WorksheetFunction.CountIf(StaffRow, SiteDescription) Then 
        MsgBox "'" & Cells(Site.Row, Target.Column).Value & "' is already assigned.", vbExclamation, MsgBoxTitle 
        Goto UndoAndExit 
    End If 
    Cells(Target.Row, Found.Column).Value = SiteDescription ' Trim(Cells(SiteNames.Row, Target.Column).Value)
    Application.EnableEvents = True 
    
    

  6. #16
    Join Date
    12th August 2014
    Posts
    22

    Re: On cell value change, lookup header & copy to 2nd range (GBP £30 for commented co

    Hi Kris,

    I've had a quick go with the new code, and it definitely helps. It's picking up attempted double bookings of Sites, but I don't think it's picking up anything with a Site name followed by other text e.g. 'Site 2 travel'

    Apologies if I'm wrong about this, as I said, I've only had a quick look at it, but as I think we're working across a time difference (?) and I'll be away from the computer again shortly, I thought it might be better to post now.

    Many thanks.

  7. #17
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,593

    Re: On cell value change, lookup header & copy to 2nd range (GBP £30 for commented co

    Can you please try this one ?
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

  8. #18
    Join Date
    12th August 2014
    Posts
    22

    Re: On cell value change, lookup header & copy to 2nd range (GBP £30 for commented co

    Thanks, I'll take a look.

  9. #19
    Join Date
    12th August 2014
    Posts
    22

    Re: On cell value change, lookup header & copy to 2nd range (GBP £30 for commented co

    Hi,

    I'm really sorry for the delay in replying. I got caught up in a pile of New Year deadlines.

    The new version that you attached works really well. Thank you so much for all your help, I'm very pleased with the final result.

    I did have a slight panic, as I didn't realise that the 'special cells' approach would fail if I locked the header and formulae cells. Lesson there: lock the sample file if the final version will be locked. However, I've added some 'unprotect' and 'reprotect' code (not sure whether it's in the optimal place) and it's working again.

    Thanks once again.

    Best wishes,

    Isame

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 3
    Last Post: December 20th, 2015, 23:04
  2. Replies: 1
    Last Post: July 9th, 2015, 02:11
  3. Sort columns then insert row and copy header at change in name
    By babulikrishna in forum Excel General
    Replies: 4
    Last Post: August 24th, 2011, 00:08
  4. Change Cells Comments Commented By Text
    By Madhart in forum Excel General
    Replies: 8
    Last Post: December 29th, 2008, 14:29
  5. Lookup Intersecting Cell Of Row and Column Header
    By Tachybana in forum Excel General
    Replies: 3
    Last Post: October 17th, 2007, 12:53

Bookmarks

Posting Permissions

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