Pasting formuales from VB on to an actice cell inb excel and pulling it within a rang

  • Thread starter Thread starter andrew_iitm
  • Start date Start date
A

andrew_iitm

Pasting formuales from VB on to an actice cell in to excel and pulling
it within a range where the last row is a variable calculated from
"rowCount = ActiveSheet.UsedRange.Rows.Count"

The formuale I wanted to fill in Row 2 is
{=IF(MAX(COUNTIF((RC[-1]:R[410]C[-1]),(RC[-1]:R[410]C[-1])))>1,"Duplicates","No
Duplicates")}. Then I want to drag it to the last row, where the last
row is a function calculated from "rowCount =
ActiveSheet.UsedRange.Rows.Count"

Would you please help me.
 
Your explanation is a bit confusing. What is the intended result of
what you are doing here? I'm guessing you want to know whether there
are any duplicates within a certain range? - Pikus
 
You didn't say what column to put it in, so I put it in Column 5. Change
the 5 to reflect the proper column:

Sub Tester9()
Dim rowCount As Long
Dim rng As Range
Dim cell As Range
rowCount = ActiveSheet.UsedRange.Rows.Count
Set rng = Range(Cells(2, 5), Cells(rowCount, 5))
For Each cell In rng
cell.FormulaArray = "=IF(MAX(COUNTIF((RC[-1]" & _
":R[410]C[-1])," & _
"(RC[-1]:R[410]C[-1])))>1" & _
",""Duplicates"",""No Duplicates"")"
Next
End Sub
 
Back
Top