Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select range using row and column number addresses | Excel Discussion (Misc queries) | |||
Select Range based on column name and not A1 notations | Excel Discussion (Misc queries) | |||
Select a Range of column depending on the date | Excel Worksheet Functions | |||
select date range in column | Excel Worksheet Functions | |||
hOW DO i CHANGE COLUMN WIDTH FOR ONLY A SELECT RANGE OF CELLS? | Excel Discussion (Misc queries) |