VBA Code for formula that contains references to both a cell and column

Occasional Contributor


I have written a formula:

=IF(AND(COUNTIF([Account ID],[@[Account ID]])>1,(LARGE([Date],1)<>[@Date]))=TRUE,"Duplicate (Most recent is Genuine)",...

That counts the number of duplicate account numbers and marks the one with the most recent date as "Genuine" and others as "Duplicate" 

I now want to automate the creation of the table but don't know how to reference the [@[Account ID]] or [@Date]).

So far everything I have tried results in the cell being blank





                        Dim lastRow As Long
                         lastRow = Cells(Rows.Count, 2).End(xlUp).Row
                            Range("AF2:AF" & lastRow).FormulaR1C1 = "=iferror(VLOOKUP(RC[-18],Companies_Flag[[Name]:[Customer Exists]],5,0), """")"
                            Range("AF2:AF" & lastRow).Value = Range("AF2:AF" & lastRow).Formula
                                Application.CutCopyMode = False
                                ActiveCell.FormulaR1C1 = "Flag"
                        Dim lastRow2 As Long
                         lastRow2 = Cells(Rows.Count, 2).End(xlUp).Row
                            Range("AG2:AG" & lastRow2).FormulaR1C1 = "=IF(AND(COUNTIF(RC[-15],[@RC[-15]])]>1,(LARGE(RC[-31],1)<>[@RC[-31]]))=TRUE,'Duplicate', IF(OR([@RC[-12]]="", [@RC[-12]]>TODAY())=TRUE, IF(OR([@RC[-19]]='Rec', [@RC[-19]]='Rec',[@RC[-19]]='RRT - Rec', [@RC[-19]]='Rest')=TRUE, 'R&R',IF([@RC[-19]]='BAU', 'RCST', IF([@RC[-1]]=1, 'Tech', IF([@RC[-9]]<0.001, 'Tech',IF([@RC[-11]]<28, '< 28 Days', IF([@RC[-11]]>91, '> 90 Days', IF([@RC[-9]]<350, '< £350',IF(AND(XLOOKUP([@RC[-18]]], Logs[Name], Logs[Date], "", 0)<>"",XLOOKUP([@RC[-18]], Logs[Name], Logs[Created Date], "", 0)>[@RC[-13]]),'Checked','Genuine')))))))),'< today Maturity'))"
                            Range("AG2:AG" & lastRow2).Value = Range("AG2:AG" & lastRow2).Formula

                                Application.CutCopyMode = False
                                ActiveCell.FormulaR1C1 = "Notes"






0 Replies