CODEWITHSUNDEEP
vbamaxformula
rasmusb
rasmusb

Reputation: 11

VBA - insert maxIF Formula

I'm having several sheets with data where I need to find the highest and lowest date based on criteria in other columns. I have defined ranged for these other columns and would like to insert formula with VBA but I keep getting a 438 error "Object doesnt support this property or method.

Any clues on what is wrong, below is the extraction of my code - when I debug it is at the formula the problem is. 

Dim emp As Range
Set emp = Range("R5", ActiveCell.End(xlDown))
Dim epidno As Range
Set epidno = Range("B5").End(xlDown)
Dim jobtype As Range
Set jobtype = Range("D5", Selection.End(xlDown))
Dim jobdate As Range
Set jobdate = Range("J5", Selection.End(xlDown))

Dim datecell As Range
For Each datecell In Range("R5", ActiveCell.End(xlDown).End(xlDown).End(xlUp))
datecell.Offset(0, 1).Formula.Cell.FormulaR1C1 = "=MIN(IF(" + jobtype.adress + " =""TECHNICIAN"",IF(" + epidno.adress + "=RC[-1]," + jobdate.adress + ",""""),""""))"

Upvotes: 1

Views: 217

Answers (1)

cronos2546
cronos2546

Reputation: 1106

"=MIN(IF(" + jobtype.address + " =""TECHNICIAN"",IF(" + epidno.address + "=RC[-1]," + jobdate.address + ",""""),""""))"

There is no .adress property of a range, there is a .Address property (2 D's, 2 S's).

Upvotes: 1

Related Questions