Posts by p45cal

    Re: Autofilter Copy Blank Row

    Maybe I too should have gone down the complete rewrite road, however I came across an interesting nugget or two about Autofilter while researching the answer. Specifically that Excel creates a hidden named range in the workbook when AutoFilter is on. It's called 'Sheet1!_FilterDatabase', or in this case 'Current!_FilterDatabase'. I didn't use it in the end.

    See attached file. It seems to work for whether there are 0, 1 (your code had some problems with 1) or any number of 'New Entry' rows.


    Re: "next Command" Not Going Past Row 19

    Yes, put this in the Immediate pane of the VBE and see if it comes back with what you expect..

    Results might be a bit more predicatable with
    mLastRow = Sheets("TheSheetsName AsOnTheTab").Cells(Rows.Count, "I").End(xlUp).Row

    mLastRow = Sheet3.Cells(Rows.Count, "I").End(xlUp).Row

    As for a better way to do it? Well here's a different way to do it, no vba:

    This is for row 20, you can copy up/down:
    In any column in row 20 place this formula:


    Note that it refers to I20 and J20. This will give the same result of the multiplication as yours. Copy the formula up/down as required (and yes, you're right, this doesn't automatically find how far down to go).

    Next format all those cells with a green background.

    Finally in whichever cell you've chosen to put the formula you can add some conditional formatting. See the attached cf1 graphic. Alternatively, to be able to apply the conditional formatting to other cells, for example to colour the cells containing the words themselves see graphic cf2 attached which was looking at the value in cell, but could be applied to any cell, most usefully, in the same row.

    Finally, without using any extra cells containing formulae, but requiring 2 small vlookup tables (2 columns each) anywhere in the workbook, even on another sheet, I created these and made them Named ranges, this one I called 'Grade':
    High 10
    Medium 5
    Low 1

    and this one 'Critical'
    Critical 10
    Serious 8
    Moderate 5
    Minor 1

    then I changed the 'Formula is' formulae to:
    Condtion 1
    Condition 2
    Condition 3

    not forgetting to have the default cell background set to green.
    The conditional format still refers to the cells $I20 and $J20, which allows the conditional format to be copied and pasted (eg. with the format painter) to any row or column.


    Re: Will This Link Be Garbled?

    re:"Motivational to preview posts, huh?"

    Previewing doesn't show up the problem, you'll either have to know what's going to be autolinked, or post anyway and edit your post afterwards if necessary. I'm a bit surprised that it does autolink stuff within the url code tags.

    Anyway, now I understand what to do, so thanks to all, regards,

    Re: Will This Link Be Garbled?

    We are in the test area and I was testing whether a url which previewed OK would still be OK on posting. The letters vb in the test message do constitute a hyperlink, but not to the page I pasted. In the end I edited my post on the live forum giving a tinyurl link instead of the original. So no hidden question! (although I sent a message to Andy Pope about it)

    Re: Remove Cell Borders With Macro

    You might be suffering from an Excel Gotcha; see

    I had no problems activating or selecting my cell by just uncommenting either of those lines.

    However, the line
    .BorderAround LineStyle:=xlContinuous, Weight:=xlThin, ColorIndex:=xlColorIndexAutomatic
    (which has a doubtful line continuation character btw) wouldn't undo your borders, only change it from dashed to continuous. Default is no borders.


    Re: Automatically Comment Cell When Row With Consecutive Column Has Same Entry

    This in the worksheet code module for the sheet in question (where the flashing cursor is when you right click on the worksheet's tab and choose 'View Code...'

    bit is the specified range mentioned below. No checks have been put in to check that only one row of cells has been changed; it's just for ideas.

    As far as making a user defined function is concerned, the following basic UDF will give a result in a cell (not its comment):

    Put this preferably in a standard code module.
    It won't work for anything but a single contiguous row of cells with at least 2 cells, but you're looking for more than 3 consecutives anyway.


    Quote from ceruin

    Dear p45cal,

    What do you mean by: "it could be made to run on a worksheet change event if the specified range is changed."?

    Thanks for your help, now I have a better understanding of VBA on excel. You are my champion! :smile:

    Re: Automatically Comment Cell When Row With Consecutive Column Has Same Entry

    I think a formula might be very complicated, and I think impossible as far as writing a comment is concerned. So I've gone down the vba route. Explore this (albeit amateur) offering:

    The above code has to be manually run but it could be made to run on a worksheet change event if the specified range is changed.

    In column H I just put the formula
    =sum(A1:G1) in the first row and copied down.

    At the very least it should give you some suggestions.


    Re: Worksheets: Run-time Error 9

    Most likely because when execution gets to that line there isn't a sheet named "HiWin04"

    Are you renaming sheets here?
    ThisWorkbook.Worksheets(i).Name = sheet_name(i)
    in the loop

    Re: Issue with format of dates

    How does the data get into man.xls?
    It is already wrong there. If the data is imported into man.xls, xl can be stopped from trying to interpret dates. At the moment, when it sees a valid English date it seems to flip month and day!
    If the file it is imported from is text, then Data|Import External Data|Import Data allows you to specify DMY or MDY

    I notice that the file is called 'man', is that because the data comes from the Man stocks and shares data feed supplier? If so, it looks like you've got future prices there - now that would be interesting!

    Re: Linking Spreadsheets

    You have a workbook called pricelist.xls and one called stock.xls.
    In stock.xls you have column A headed 'Part No.', column B headed 'Quantity', and you add a third column (C) headed 'Price'; this is where the price will appear.

    In the pricelist.xls workbook you have column A headed 'Part No.' and column B headed Price, a;ready populated with prices.

    In workbook stock.xls place something similar to the following formula in cell C2 and copy it down:

    you should first replace the 8 in the formula above to the row number of the last part no. in pricelist.xls. I don't think that pricelist.xls needs to be open.