Posts by DaveR (UK)

Re: Copy Rows Based On A Cell Value

Will definietly have something to do with

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 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

Option Explicit


Sub expandIt()
Dim pasteTo As Range, pasteTo2 As Long, cl As Range
    
     'Clears any existing data on the destination sheet (sheet2 in the example given)
    Sheet2.Cells(1, 1).CurrentRegion.ClearContents
     
     'Copy the header row from sheet1 to sheet2
    Sheet1.Range("A1:F1").Copy Destination:=Sheet2.Range("A1")
     
     'This selects the current used region on sheet1
     'assuming that you have a properly laid out table
     'ie no empty rows etc
    With Sheet1.Range("A1").CurrentRegion
        'Now loop down a resized range which is the
        'first column in the selected range
        For Each cl In .Offset(1).Resize(.Rows.Count - 1, 1)
            'Set the cell where the next set of date should
            'be pasted to
            Set pasteTo = Sheet2.Cells(65536, 1).End(xlUp).Offset(1)
            'Copy the single row across
            cl.Resize(, 5).Copy Destination:=pasteTo
            'If the Count is is greater than one,then
            'fill down the approrpite number of row
            'by using a resized range
            If cl.Offset(, 5) <> 1 Then
                pasteTo.Resize(cl.Offset(, 5).Value, 5).FillDown
            End If
        Next
    End With
          
    'populate the count column with '1'
    With Sheet2.Range("A1").CurrentRegion
        With .Offset(1, 5).Resize(.Rows.Count - 1, 1)
            .Value = 1
        End With
    End With
End Sub

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: Remove Cell Lines

No worries - not a dumb questions when you don't know the answer !! :p

Re: Remove Cell Lines

Look in TOOLS>>>OPTIONS and uncheck the Gridlines option...should do the trick

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: Update Sheet By Conditional Format

No probs - a pleasure to help

Re: Updating Summary Sheet Only By Format

Just add a formula on the 2008 sheet to point to the data - you then will have a value to which you can apply conditional formatting - something like

=IF('01'!C4="a",1,0)

should do the trick

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: Arrange Statistical Data In Numeric Order

Have a look at the rank formula - then record a macro that sorts the data by the rank value - this should point you in the right direction...

Re: Auto Run Macro On File -> New

Check this link to Chip Pearson's site - think this is what you are after

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...

Dim ColNo As Long


ColNo = WorksheetFunction.Match("Heading 4", _
        Sheet1.Range("A1", Sheet1.Range("IV1").End(xlToLeft)), 0)
MsgBox ColNo


ColNo = Sheet1.Range("A1", Sheet1.Range("IV1").End(xlToLeft)).Find("Heading 4").Column
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

For counter = 1 To NewSheet.HPageBreaks.Count
    'Escapes if outside print area
    If NewSheet.Range(NewSheet.HPageBreaks(counter).Location.Address).Row > _
            NewSheet.Range("A65536").End(xlUp).Row Then Exit For
    'Else move page break up one
    Set NewSheet.HPageBreaks(counter).Location = _
            NewSheet.Range(NewSheet.HPageBreaks(counter).Location.Address).Offset(-1)
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

UseSheet.PageSetup.PrintArea = _
        UseSheet.Range("A1:G" & UseSheet.Range("A65536").End(xlUp).Row + 1).Address


For Each pB In UseSheet.HPageBreaks
    Set pB.Location = UseSheet.Range(pB.Location.Address).Offset(-1, 0)
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: Multiple Criteria Vlookup

I would probably add a new column to your raw data that concatenated the three fields - and then look up based on that instead...

Re: Create If Statement To Look Up Training Info

No worries - always a pleasure to help out...

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...

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

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

For MyRows = 1 To Range("A65536").End(xlUp).Row Step - 1
If Range("J" & MyRows).Value = "" Or Range("C" & MyRows).Value = "C" Then
    Rows(MyRows).Delete Shift:=xlUp
    End If
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") & "'"

Re: Deleting Rows Based On Criteria

Can you post a sample work book ??

Can;t see why it wouldn;t work, is colkumn A always populate ie some form of identifier...