select a range in a column
 

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

select a range in a column
 

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

select a range in a column
 

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

select a range in a column
 

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

select a range in a column
 

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
.