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

Occasional Contributor

Hi,

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
                                                        
                                Range("Af1").Select
                                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
                                                        


                                Range("AG1").Select
                                Application.CutCopyMode = False
                                ActiveCell.FormulaR1C1 = "Notes"

 

 

 

 

 

0 Replies