I've got a little issue with calculating the minimum date from a data set, as it displays "00.01.1900".
The problem seems to be the missing data in some of the cells. In this case the code should of course take over the available minimum date from the cells where the data is available as long as it regards the same item no. in column A.
I've been trying to edit the code, unfortunately without success. Sadly the upload of files does not seem to work either in this forum, so I am pasting the code below..
Would you guys give a hand with that? Thanks a lot!
Best regards
HTML Code:
Option Explicit
Sub GrandStartDate()
Application.Calculation = xlCalculationAutomatic
Call MinValues(Range("F2"), "Apple", Range("A2"), Range("D2"), Range("B2"))
End Sub
Public Sub MinValues( _
rGrandStartDates As Range, _
sServiceDescription As String, _
rContracts As Range, _
rServiceDescriptions As Range, _
rStartDates As Range)
Dim lastrow As Long
Dim sFormula As String
Dim rContract As Range
With ActiveSheet
lastrow = .Cells(.Rows.Count, rStartDates.Column).End(xlUp).Row
Set rGrandStartDates = rGrandStartDates.Resize(lastrow - rGrandStartDates.Row + 1)
Set rContracts = rContracts.Resize(lastrow - rContracts.Row + 1)
Set rContract = rContracts.Cells(1, 1)
Set rServiceDescriptions = rServiceDescriptions.Resize(lastrow - rServiceDescriptions.Row + 1)
Set rStartDates = rStartDates.Resize(lastrow - rStartDates.Row + 1)
sFormula = "=MIN(IF(" & rContracts.Address(True, True) & _
"=" & rContract.Address(False, True) & _
",IF(" & rServiceDescriptions.Address(True, True) & _
"=""" & sServiceDescription & """," & _
rStartDates.Address(True, True) & _
")))"
rGrandStartDates.Cells(1, 1).FormulaArray = sFormula
rGrandStartDates.Cells(1, 1).AutoFill Destination:=rGrandStartDates, Type:=xlFillDefault
rGrandStartDates.Value = rGrandStartDates.Value
End With
Columns("F:G").Select
Selection.NumberFormat = "dd/mm/yyyy"
Call GrandEndDate
End Sub
Surround your VBA code with CODE tags e.g.;
[CODE]your VBA code here[/CODE]
The # button in the forum editor will apply CODE tags around your selected text.
Okay I wasn't sure, just knew your formula was not an Array formula. Can you explain the process. It is had for me to understand being all your data is the same. Maybe upload another example workbook with some different data and show your expected results.
Bookmarks