Posts by DaveR (UK)

    Re: Copy Rows Based On A Cell Value


    Will definietly have something to do with

    Code
    1. Set pasteTo = Sheet2. Cells(65536, 1).End(xlUp).Offset(1)


    This goes to cell A65536 and finds the next cell above it that has information in it - if the the data fills the range up to here, then it will find A1...reason being I use excel 2003 where the maximum number of rows is 65536 so I am guessing you will need to amend this to reflect the maximum number of rows in 2007 (there may be a better way, but not having used excel for a while or 2007 ever I couldn't be sure -think 2007 maximum rows is somewhere around 1 million (2^20)...)


    Bring on the bank holiday !!!


    :drink: :sailboat: :drink:

    Re: Copy Rows Based On A Cell Value


    Hi Marc,


    couple of points - when I suggested that you post the question along with the sample data in the forum, I meant for you to pose your question and not just attach the spreadsheet - this was so that you could broaden the number of people who could potentially look at your problem and come up with a solution...


    Also - I don't believe that this is

    Quote

    wow! This must be the most user-unfriendly site I have ever seen)

    In fact I believe the community that use this forum to be one of the most friendly and helpful I have ever come across and the site is great - I have learnt most of what I know from this forum :-D thanks to all you oz-griders !!


    So, back to your problem - I reckon your requirements can be met with the following code...hopefully the comments fully explain what is going on...if you have any questions, please respond to this post and I'll endeavour to help



    Hopefully my comments help (although I must profess I am not the best at explaining what I do sometimes !!!)


    Anyway - was lovely to post here again - haven't visited for a while as my role has moved me much more towards SQL and the like...


    :drink:
    :drink:
    :drink:
    :drink:
    :drink:


    All this in preparation for the Uk Bank holiday weekend - enjoy...


    D ;O)

    Re: Mail Merge Template


    hmmm - not sure you have the right files here !! My limited experience in mail merges from excel to word is that you need to have a row for each record to be created as the word document...ie Doe,John should have the attendence all records on the one row - not laid out in a table as in your example.


    Maybe you need to be linking to the original report ???

    Re: Show Messagebox If Worksheet Already Open


    Not sure you can save only section sof the work book - nor why this would be needs - it is easy to copy a single sheet to a new work book and save that though...


    As for the access to the workbook....provided you haven't set the work book up as a shared workbook - only one person should be able to open it with read/write access at any time - and a message along the lines of


    This workbook is already opened by Bill Bloggs Open Read only or Notify when they have finished...


    should appear when opened

    Re: Selecting A Cell - In A Column By Its Heading


    Think the only way to do this would be to use the
    WORKSHEETFORMULA.MATCH
    Function to location the position of the heading you want then add an appopriate number where the headings list does not start in cell A1[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Or - you could use the find function to find the cell with the heading required, then the row property of said cell...I'll knock up an example[hr]*[/hr] Auto Merged Post;[dl]*[/dl]so - here you go, the below assumes that the heading start in Sheet1.Range("A1") - adjust the ranges to suit


    It is essential that the column headings are unique for this to work properly - think the second method is probably better...


    Code
    1. Dim ColNo As Long
    2. ColNo = WorksheetFunction.Match("Heading 4", _
    3. Sheet1.Range("A1", Sheet1.Range("IV1").End(xlToLeft)), 0)
    4. MsgBox ColNo
    5. ColNo = Sheet1.Range("A1", Sheet1.Range("IV1").End(xlToLeft)).Find("Heading 4").Column
    6. MsgBox ColNo

    Re: Move Hpagebreak Error


    Thanks for your help guys....I forgot to mention that this is performing on a sheet where subtotal have been added...starting with preset page breaks...anyway, while the site was struggling yesterday I found a work around...


    it turned out it was only on the last page break (the grand total row) which was in fact outside of the print area (is there a unshown page break here maybe ??) !?!?! All had me rather cuffudled to be honest, but I put in a test to see is the address of the page break in questions was after the last row and stopped if it was and it all works now...


    If any one can offer an explanation - I would love to hear...


    Below the code I ended up with

    Code
    1. For counter = 1 To NewSheet.HPageBreaks.Count
    2. 'Escapes if outside print area
    3. If NewSheet.Range(NewSheet.HPageBreaks(counter).Location.Address).Row > _
    4. NewSheet.Range("A65536").End(xlUp).Row Then Exit For
    5. 'Else move page break up one
    6. Set NewSheet.HPageBreaks(counter).Location = _
    7. NewSheet.Range(NewSheet.HPageBreaks(counter).Location.Address).Offset(-1)
    8. Next

    Afternoon all,


    been a while since I've had a question to post here, but am playing around with adjusting the print area and page breaks of a report we are trying to create...


    The code seems to work fine - basically it loops through the HPagreBreaks collection in the print area and shifts each page break up one cell (don't ask - thats the way it is wanted !!)


    Here is the code

    Code
    1. UseSheet.PageSetup.PrintArea = _
    2. UseSheet.Range("A1:G" & UseSheet.Range("A65536").End(xlUp).Row + 1).Address
    3. For Each pB In UseSheet.HPageBreaks
    4. Set pB.Location = UseSheet.Range(pB.Location.Address).Offset(-1, 0)
    5. Next


    All is fine until it tries to move the last page break (or the end of the print area) when I get an error....


    Any help greatly appreciated...

    Re: Create If Statement To Look Up Training Info


    Hi Ediese,


    Both statement are reuiqred - the first part caters for values that are looked up and not in the list....if this wasn't there would would get a #N/A! error instead of "", the second part looks up the value and when it is equal to "TCH-Preceptor" returns YES otherwise returns NO.


    The 3 tells the lookup up to return the third column from the table that is being interrogated and the FALSE allows only exact macthes to be return. If this was omitted then if no exact match was found it would return the first match that was lower (before in the alphabet) to the search criteria...


    Hope this makes it clearer....

    Re: Msgbox Getting Cut Off


    I would have a look at creating a form to mimic the input box - that way you can code it the way you want and use any control you desire - combobox, tickbox, optionbox etc etc...


    Also using UCASE or LCASE on the returned value will convert it to Upper or Lower case

    Re: Deleting Rows Based On Criteria


    The code in the attached work book is still stepping down the rows from top to bottom...

    Code
    1. For MyRows = 1 To Range("A65536").End(xlUp).Row - 1
    2. If Range("J" & MyRows).Value = "" Or Range("C" & MyRows).Value = "C" Then
    3. Rows(MyRows).Delete Shift:=xlUp
    4. End If
    5. Next MyRows


    did you catch that you needed to add STEP -1 to the line which defines the loop as below

    Code
    1. For MyRows = 1 To Range("A65536").End(xlUp).Row Step - 1
    2. If Range("J" & MyRows).Value = "" Or Range("C" & MyRows).Value = "C" Then
    3. Rows(MyRows).Delete Shift:=xlUp
    4. End If
    5. Next MyRows

    Re: Date Wildcard In Vb Sql


    Think the select statement should look more like...


    sSQLQRY = "SELECT CustID, Type, DatePaid FORM tblMoorages WHERE DatePaid = '" & Format(Range("A1"), "yyyymmdd") & "'"