Posts by Ochimus

    Twin problem here. End User wants to enter a number in either of two columns and have them convert automatically to "unlimited hours and minutes" (e.g. entering "2700" results in "27:00" in cell).

    (a) Can't see why the following Worksheet_Change macro in attached file isn't doing that
    (b) Can't see how to adapt the Code so it works on either of two "target" columns?

    All suggestions welcomed gratefully


    Re: named range as variable


    You have a list of sub assemblies to which you have given a defined "name".

    They correspond to the list of months I provided in Col N and then named as a range called "Months".

    You want to be able to select from your list.

    If you select cell I4, an arrow appears on the right of the cell. Click that arrow, and the list of entries in the "Months" range appears in a "Dropdown" list.

    Select whichever one you want. The list will disappear, leaving just your selection visible in the cell.

    Click the button, and whatever you selected in I4 appears in A9.


    Re: named range as variable

    Hopefully the attached file does what you want?

    It sets the Range Name as "Cell I4", which I have set up as a Combobox populated with the months in Col. N which I have given the Range Name "months", and set by Data Validation.

    Clicking the "Copy" button copies your selection to cell A9.

    Change the selection and click and A9 changes automatically.


    Apologies for not attaching the file, but for some reason the system won't upload it (It was created in Excel 2013 but in "compatability mode" as the end user has Excel 2007.

    Objective is to find and copy all the data on the "Database" where the date in Col J matches the "Target" date in Col A of the "Date" sheet.

    I have "Dim'd" the five variables involved, but I get a "RunTimeError 91 -Object Variable or With Variable Not set" message when the Code tries to set the ranges.

    Hope someone can see the flaw.

    All responses accepted gratefully as ever


    Re: Index Match links correctly to Named ranges

    Many thanks for the suggestions, and apologies to both for not getting back sooner, but still trying to get this to work seamlessly!

    Danerida, I would be delighted to remove the "selects" and references, but your code produces a RunTime Error 438 on the line copying the data across sheets. Attached file shows it in action.

    The other key concern is it assumes the Code refers to the Database sheet at the beginning. But if a user saves the file whilst another sheet was "selected", the next session could end up trying to execute on the wrong sheet.

    Option Explicit

    Sub Copier()

    Dim r As Long

    r = Range("I65536").End(xlUp).Row
    ActiveSheet.Range("I1:I" & r).AutoFilter Field:=1, Criteria1:="1"
    Range("I1:J" & r).Copy Sheets("STATUS").Range("F1").Paste 'Produces Run Time error 438 - Doesn't support this property or method
    Sheets("DATABASE").Range("K1:K" & r).Copy

    End Sub


    Attached file accepts and uses range name CALSTART for one procedure, but not on another.

    Macro "RANGESET" names Cell K1 on the Database sheet as "CALSTART".

    The "EACHVAL" macro recognises and uses that range name to find the highest number of Calendar days (MAXVAL) in Col K of the Database worksheet, list the days in Col A of the Calendar sheet, then count how many times each Calendar Day appears on the Database, putting the answers in Cols A and B of the Calendar sheet.

    But when I try to use it to filter Col K on the Database, the Code generates a "Run Time Error 1004" saying Method 'Range' of object '_Global' failed.

    As ever, all suggestions and solutions accepted gratefully


    Real file has to work with different databases of unknown sizes, so I need to be able to link the Code to range names.

    But, as shown on the attached file, Code is not "Index Matching" correctly.

    It filters data from one sheet and posts it to another correctly.

    But the "Index Link" step to find matching data from the original sheet generates error messages

    Hope someone can see the wood for the trees, because in the "real" file I can't see another way to "set" the last row of the Database.


    Re: Macro links date in first row to correct sequential blocs of forty days


    Many thanks for prompt response and explanation of the formula issue. I thought it would just "bypass" the blank month and count forty days from the start of the year.

    Unfortunately the "concatenate" approach won't work "automatically". That first date can be anything from 1 Jan to 31 December, and I need something that identifies the relevant dates and bloc number for every eventuality, and then "sorts" the blocs into the right sequence without involving the End User.


    Need a solution that works for Excel 2007.

    As shown on the attached file, Cell "A2" has a date.

    Calendar days are split into nine "Forty Day" blocks starting each year on 1 Jan (Block 9 actually carries to end of year, so is more than forty days).

    Need the date in "A2" to start the "bloc number" sequence in Cols B - D, which then determines the sequence in the rows below. (The sequence on the file only covers the nine rows, but in the real file there will be nine rows for every year in the Database).

    So if A2 is in the date range for "Block 1", B2 must be "1", and C2 - D2 will show the start and end dates for that bloc. B3 will be Bloc 2, and Bloc 1 will restart on row 11.

    But if A2 is in "Block 6", B2 will be "6" and C2 - D2 will show THAT start and end dates. B3 will be Bloc 7, and Bloc 1 will restart on row 6.

    Row 21 shows a formula approach using "=DATE(YEAR(E3),,40)". It should have shown 10/02/76 as the "Day Forty" date, but it actually showed 09/01/76, and can't see why.

    Hope someone can see the wood for the trees?


    Re: Filter shows row and the PRECEDING row


    Thank you for the update.

    Apologies that I did not explain clearly enough what I need.

    Your file shows each number in a different Bloc, but I need them to start in the SAME cells each time. I hope the attached file will make this clear.

    So the Code start with "1" in F3. After I add some Code to "Do Things" with the data, the Code then needs to find "2" and start that also in F3, then after "doing things" it shows "3" in F3, until it has listed the highest number in the Database.

    One problem is your Code "reversed" the data for "1", as shown on the worksheet, although all the other numbers worked perfectly when I ran it!


    Re: Filter shows row and the PRECEDING row

    Good evening jindon.

    Thank you for touching base,

    Attached file shows your Code selects and copies all the rows with number "1" and the rows preceding them.

    But I need to "cycle" through all the numbers in Col C. So after the Code copies every row with "1" into Col H,and the preceding row into Col G, I can add Code to "do things" with the data, then have the Code find the rows with "2" and their preceding rows, I do things, the Code then finds "3" and preceding and so on.

    I have the Code that can find the "maximum number" in the database, so it would say "For x = 1 to (MaxValue). . . ."

    Hope that explains.


    Re: Filter shows row and the PRECEDING row


    Thanks for seeing the issue so quickly.

    If I had remembered to add "Option Explicit" at the beginning, the Code would presumably have highlighted the unmatched "end with" before it even began to execute? (Hangs head in shame. . . )

    But still can't see where to change the "1" to "x" so I can cycle through the numbers?


    Re: Filter shows row and the PRECEDING row


    Thanks for the "patch" - but it generates a "Compile Error" message that "x = .Rows(1).Value" is an Invalid or Unqualified reference. I am sure I pasted it correctly:


    Re: Filter shows row and the PRECEDING row

    Two slight challenges.

    When I extended the Database, the Test macro on the attached crashes when n=9, but I can't see why?

    Secondly I can't work out how to change the "filter" number from 1 to any others,

    If I can change the number then I can have a preceding line (e.g. "For j=1 to MaxValue") so the Code can "cycle" through the numbers sequentially.

    Idea is that it pulls up "1", more code "does things", clears the results and calls up "2", etc


    Re: Filter shows row and the PRECEDING row

    Thanks to both for prompt response,

    Mawrin,'s approach appeals (less work on my part), but when I ran the Code it left row 6 blank, but filled G7 - K9 with "#N/A"s? Can't see why.


    Not sure whether this is possible either by formula or VBA?

    Col A of attached file has list of numbers.
    Col B has sequential numbers in "blocs" starting at 1, but with different "last numbers".

    How can I "filter" Col A so it extracts every "1" AND every row preceding that "1", as shown in Cols D and E?

    And is there a way I can "jump" straight from the list in Cols A - B to the Table output in Cols G - K?

    Challenge is that "real" database has over ten thousand records, and the "Col B" blocs go up to 261 rows each, so really need something "speedy"

    All solutions accepted gratefully.


    Attached spreadsheet has sequential dates in Col A.

    Need the Code that identifies the last entry for each month in the list, works back to find the 10th oldest date for that month, and copy it into the relevant column.

    Can't use "Calendar days" approach, because that date may not be in the Database.

    So in the example, "Jan 76" is A2:a22, and the 10th oldest date is A13 - 19 Jan. "Feb 76" is A23:A42, so the 10th oldest is A33 - 16 Feb, etc.

    Need it as VBA because the "real" file has forty years' records in this particular Database, and End User wants to be able to apply it to other Databases as well.

    All solutions and ideas received gratefully.