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 iforum.ozgrid.com/index.php?attachment/64272/ isn't doing that
(b) Can't see how to adapt the Code so it works on either of two "target" columns?

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)


Dim rngCell As Range


On Error GoTo ExitPoint


If Target.Cells.Count > 1 Then Exit Sub


If Intersect(Target, Columns(1)) Is Nothing Then Exit Sub


Application.EnableEvents = False


For Each rngCell In Intersect(Target, Columns(1)).Cells
    If IsNumeric(rngCell.Value) Then
        With rngCell
        .NumberFormat = "[h]:mm"
        End With
    End If
Next rngCell
ExitPoint:
Application.EnableEvents = True


End Sub

All suggestions welcomed gratefully

Ochimus

Re: named range as variable

Steve,

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.

Ochimus

Re: Conditional Formula

Hopefully the attached file forum.ozgrid.com/index.php?attachment/64231/ does what you want?

Formula is:

"=IF(B10="ES",(D13-D12)*E12*(-1)*50,IF(B10="NQ",(D16-D15)*E16*(-1)*20,IF(B10="YM",(D19-D18)*E19*(-1)*5)))"

Ochimus

Re: named range as variable

Hopefully the attached file forum.ozgrid.com/index.php?attachment/64229/ 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.

Option Explicit


Private Sub CmdCopy_Click()
    
    Dim x As Range
    
    Set x = Range("i4")
        
    Range("a9") = x
    
End Sub

Ochimus

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.

Option Explicit


Sub DAYS()


Dim j As Long, l As Long, n As Long
Dim TDate As Range, PDate As Range, LPrs As Range, Now As Range, NPrc As Range
Dim ws As Worksheet
Dim Target
    
    Sheet2.Activate
    
'Sets last row in sheet
    j = Range("j65536").End(xlUp).Row
    
'If there is nothing below the heading it moves to next step.
'Otherwise it deletes everything from row 2  - Last row
    If j >= 2 Then
    Range("j2:P" & j).Select
    Selection.Delete Shift:=xlUp
    Range("j2").Select
    End If
    
'Sets criteria to find (Single date set for this sample)
    Sheet1.Activate
    
    For n = 310 To 310
    
    Target = Range("A" & n)
    
'Sets last row to search in Database
    l = Range("J65536").End(xlUp).Row
    Set TDate = ws.Rows(l).Find("Trading Date") 'Throwing "RunTimeError 91 -Object Variable or With Variable Not set"
    Set PDate = ws.Rows(l).Find("Previous Day")
    Set LPrs = ws.Rows(l).Find("Last Closing Price")
    Set Now = ws.Rows(l).Find("Current Date")
    Set NPrc = ws.Rows(l).Find("This Closing Price")
    
        If (Not TDate Is Nothing) Then
            With ws.Cells(l).CurrentRegion.Resize(, Application.Max(TDate.Column, PDate.Column, LPrs.Column, Now.Column, NPrc.Column))
            .AutoFilter 1, Target
            Union(.Columns(l), .Columns(TDate.Column), .Columns(PDate.Column), .Columns(LPrs.Column), .Columns(Now.Column), .Columns(NPrc.Column)).Copy
                With Sheet2
                .Range("J2").PasteSpecial
                End With
            End With
        End If
    Next
         
'Remove filters from DATABASE
    Sheets("DATABASE").Activate
    Application.CutCopyMode = False
    Selection.AutoFilter


End Sub

Hope someone can see the flaw.

All responses accepted gratefully as ever

Ochimus

Re: Filter shows row and the PRECEDING row

Jindon,

Apologies for not replying before now, and thank you for the Code above.

It is an interesting approach, and has adapted easily to produce what was needed.

Ochimus

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 forum.ozgrid.com/index.php?attachment/64076/ 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.

[Code]
Option Explicit

Sub Copier()

Dim r As Long

r = Range("I65536").End(xlUp).Row
Columns("I:I").Select
Selection.AutoFilter
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
Sheets("STATUS").Range("I1").Paste

End Sub

Ochimus

Attached file forum.ozgrid.com/index.php?attachment/64044/ 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.

Option Explicit
   
Sub Rangeset()
    
Dim CALSTART As Range, CALEND As Range, CALRANGE As Range
    
    Set CALSTART = Worksheets("DATABASE").Range("K1")
    Set CALEND = CALSTART.End(xlDown)
    ActiveWorkbook.Names.Add Name:="CALRANGE", RefersToR1C1:=Range(CALSTART, CALEND)
       
    End Sub
    
Sub eachval()


Dim MaxVal As Integer, b As Integer, i As Integer, q As Integer, x As Integer, z As Integer


Sheets("calendar").Select
' Find the highest number of Calendar days in Col K of Database
    MaxVal = Application.Max(Range("CALRANGE"))
     
'List the Calendar Day numbers in Col A
    Range("a2").Select
    
    For i = 1 To MaxVal
    ActiveCell = I
    ActiveCell.Offset(1, 0).Select
    Next
        
'Count number of each Calendar Day and put in Col B of Calendar sheet
    z = 2
    For x = 1 To MaxVal
    Range("b" & z) = Application.WorksheetFunction.CountIf(Range("CALRANGE"), x)
    z = z + 1
    Next
'Cycle through Calendar Days by filtering each value in turn


    For q = 1 To MaxVal
'****THIS IS WHERE THE PROBLEM OCCUE    
    Range("CALSTART").Select
    Selection.AutoFilter Field:=1, Criteria1:=q
    ActiveSheet.AutoFilter.Range.Copy
    Workbooks.Add.Sheets(1).Paste
    Next
    
End Sub

As ever, all suggestions and solutions accepted gratefully

Ochimus

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 forum.ozgrid.com/index.php?attachment/64028/, 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

Option Explicit


Sub Merger()


Dim a As Integer, c As Variant, r As Integer
Dim WHEN As Range, PRICES As Range


Sheets("Database").Activate
    a = Range("A65536").End(xlUp).Row
    r = Range("J65536").End(xlUp).Row
    Set WHEN = Range("A2:A" & a)
    Set PRICES = Range("E2:E" & a)


Columns("I:I").Select
    Selection.AutoFilter
    ActiveSheet.Range("I1:I" & r).AutoFilter Field:=1, Criteria1:="1"
    Range("I1:J" & r).Select
    Selection.Copy
    Sheets("STATUS").Select
    Range("F1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("I1").Select
    Sheets("DATABASE").Select
    Range("K1:K" & r).Select
    Selection.Copy
    Sheets("STATUS").Select
    ActiveSheet.Paste
    Range("H2").Select


'THIS IS WHERE THE PROBLEM OCCURS:
'Add prices for dates
For Each c In Range("J2:J" & r)
        c.Value = Application.Index(PRICES, Application.Match(c.Offset(0, -1).Value, WHEN, 0))
        Next


End Sub

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.

Ochimus

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

philby,

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.

Ochimus

Need a solution that works for Excel 2007.

As shown on the attached file forum.ozgrid.com/index.php?attachment/63968/, 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?

Ochimus

Re: Filter shows row and the PRECEDING row

Jindon,

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 forum.ozgrid.com/index.php?attachment/63917/ 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!

Ochimus

Re: Filter shows row and the PRECEDING row

Good evening jindon.

Thank you for touching base,

Attached file forum.ozgrid.com/index.php?attachment/63911/ 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.

Ochimus

Re: Filter shows row and the PRECEDING row

Mawhrin,

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?

Ochimus

Re: Filter shows row and the PRECEDING row

jindon,

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:

Sub test()
    
    Dim x, a, i As Long, n As Long
    
    With Cells(1).CurrentRegion.Resize(, 3)
        With .Offset(2)
        x = Filter(Evaluate("transpose(if(((" & .Columns(3).Address & "=1)*(" & _
    .Columns(3).Offset(-1).Address & "<>1))+(" & .Columns(3).Offset(1) _
    .Address & "=1),row(1:" & .Rows.Count & "),char(2)))"), Chr(2), 0)
    x = Application.Index(.Value, Application.Transpose(x), [{1,3}])
        End With
    End With
        ReDim a(1 To UBound(x) / 2, 1 To 5)
        For i = 1 To UBound(x) Step 2
            n = n + 1
            a(n, 1) = x(i, 1): a(n, 2) = x(i, 2)
            a(n, 3) = x(i + 1, 1): a(n, 4) = x(i + 1, 2)
            a(n, 5) = a(n, 3) - a(n, 1)
        Next
        x = .Rows(1).Value
        With .Offset(, .Columns.Count + 2).Resize(n + 1, 5)
            .Rows(1).Value = x: .Cells(1, 5).Value = "Difference"
            .Cells(1).Resize(, 2).AutoFill .Cells(1).Resize(, 4)
            .Rows(2).Resize(n).Value = a
        End With
    End With
End Sub

Ochimus

Re: Filter shows row and the PRECEDING row

Two slight challenges.

When I extended the Database, the Test macro on the attached forum.ozgrid.com/index.php?attachment/63899/ 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

Ochimus

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.

Ochimus

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

Col A of attached file forum.ozgrid.com/index.php?attachment/63880/ 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.

Ochimus

Attached spreadsheet forum.ozgrid.com/index.php?attachment/63855/ 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.

Ochimus

Re: Formula identifies nearest Start and End of Month dates correctly from dates in r

Batman,

Thanks for prompt response and solution.

I thought (wrongly) that the "same month" was axiomatic. Mr. Gates' lads obviously had a different approach.

Ochimus