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.

-- 
hopalong

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?

"jimbo" wrote:

> 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.
> 
> -- 
> hopalong

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.


jimbo wrote:
> 
> 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.
> 
> --
> hopalong

-- 

Dave Peterson

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.
-- 
hopalong


"Arceedee" wrote:

> 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?
> 
> "jimbo" wrote:
> 
> > 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.
> > 
> > -- 
> > hopalong

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. 
-- 
hopalong


"Dave Peterson" wrote:

> 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.
> 
> 
> jimbo wrote:
> > 
> > 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.
> > 
> > --
> > hopalong
> 
> -- 
> 
> Dave Peterson
> .
>