select a range in a column

  • Thread starter Thread starter jimbo
  • Start date Start date
J

jimbo

I have two columns in a sheet say column A and B. In Column A I have invoice
numbers and in column B a formula which identifes duplicate invoice numbers.

As the data varies in length in column A when opening the sheet I want to
ensure that a formula in column B returns a value for all the entries in
Column A.

As part of a macro instruction how can I write in VBA a sequence which will
cover all the entires in Column A, which will then allow me to copy the
formula in Column B thus ensuring the variable number number of entires in
Column A are covered.
 
What do you want to do with the duplicate numbers? You may want to approach
the problem from another angle. How about conditionally formatting for dups
(2007) or filtering/removing dups?
 
I'm not sure what formula you use, but maybe this'll get you closer:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim LastRow As Long

Set wks = Worksheets("Sheet1")

With wks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("B2:b" & LastRow).Formula _
= "=if(countif(a:a,a2)>2,""Duplicate"",""ok"")"
End With
End Sub

I assumed row 1 had headers, so the formula starts in B2 (using A2 in the
=countif() statement.
 
Thank you for your thoughts. I did consider this option but ran in to some
difficulites building a Macro, as I am not great a wrting macro's.
 
Dave thank you for replying.
I was usuing the If Count Formula and your macro intstruction has really set
me on my way, works a treat!!!

Once again thanks a million.
 
Back
Top