Macro Help
in [Programming]
|
From: fi.or.jp.de on 30 Apr 2010 09:09 Hi All, I have calling some macro like this Call Duplicate_Delete The Macro "Duplicate_Delete" deletes the col A's duplicate data using Countif function. Once this done again my main macro does some activity Again i need to run the Duplicate_Delete macro for the Col H. but previous i coded as Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("AY").Text). So, i need to run the same code for Col H. I know, i need to create new macro which hardly contains 3 lines. Is there any option i can assign it as follows Application.WorksheetFunction.CountIf(Range(ColA & "1:" & ColA & x), Range("AY").Text). Eg., Cola = "A" Call Duplicate_Delete '' do other work '' Cola = "H" Call Duplicate_Delete Thanks in advance
From: joel on 30 Apr 2010 09:36 Yes Dim DeleteCol as string Dim CompareStr as string DeleteCol = "H" CompareStr = Range("AY").Text Call Duplicate_Delete(DeleteCol,CompareStr) sub Duplicate_Delete(DeleteCol as string, CompareStr as String) Application.WorksheetFunction.CountIf( _ Range(DeleteCol & "1:" & DeleteCol & x),CompareStr) end sub -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=199734 http://www.thecodecage.com/forumz
From: Jacob Skaria on 30 Apr 2010 10:00 Try the below. Do you mean Range("AY").Text) ? Dim strCol As String strCol = "H" varCount = Application.WorksheetFunction. _ CountIf(Columns(strCol), Range("A1").Text) -- Jacob (MVP - Excel) "fi.or.jp.de" wrote: > Hi All, > > I have calling some macro like this > > Call Duplicate_Delete > > The Macro "Duplicate_Delete" deletes the col A's duplicate data > using Countif function. > > Once this done again my main macro does some activity > > Again i need to run the Duplicate_Delete macro for the Col H. > > but previous i coded as > Application.WorksheetFunction.CountIf(Range("A1:A" & x), > Range("AY").Text). > > So, i need to run the same code for Col H. > > I know, i need to create new macro which hardly contains 3 lines. > > Is there any option i can assign it as follows > > Application.WorksheetFunction.CountIf(Range(ColA & "1:" & ColA & x), > Range("AY").Text). > > Eg., > > Cola = "A" > Call Duplicate_Delete > > '' > do other work > > '' > > Cola = "H" > Call Duplicate_Delete > > Thanks in advance > . >
From: Jacob Skaria on 30 Apr 2010 11:00 Oops...I missed the point..Refer Joel's post -- Jacob (MVP - Excel) "Jacob Skaria" wrote: > Try the below. Do you mean Range("AY").Text) ? > > Dim strCol As String > strCol = "H" > > varCount = Application.WorksheetFunction. _ > CountIf(Columns(strCol), Range("A1").Text) > > -- > Jacob (MVP - Excel) > > > "fi.or.jp.de" wrote: > > > Hi All, > > > > I have calling some macro like this > > > > Call Duplicate_Delete > > > > The Macro "Duplicate_Delete" deletes the col A's duplicate data > > using Countif function. > > > > Once this done again my main macro does some activity > > > > Again i need to run the Duplicate_Delete macro for the Col H. > > > > but previous i coded as > > Application.WorksheetFunction.CountIf(Range("A1:A" & x), > > Range("AY").Text). > > > > So, i need to run the same code for Col H. > > > > I know, i need to create new macro which hardly contains 3 lines. > > > > Is there any option i can assign it as follows > > > > Application.WorksheetFunction.CountIf(Range(ColA & "1:" & ColA & x), > > Range("AY").Text). > > > > Eg., > > > > Cola = "A" > > Call Duplicate_Delete > > > > '' > > do other work > > > > '' > > > > Cola = "H" > > Call Duplicate_Delete > > > > Thanks in advance > > . > >
From: fi.or.jp.de on 30 Apr 2010 12:00
Joel, Thank u. It works great On Apr 30, 6:36 pm, joel <joel.4a8...(a)thecodecage.com> wrote: > Yes > > Dim DeleteCol as string > Dim CompareStr as string > DeleteCol = "H" > CompareStr = Range("AY").Text > Call Duplicate_Delete(DeleteCol,CompareStr) > > sub Duplicate_Delete(DeleteCol as string, CompareStr as String) > Application.WorksheetFunction.CountIf( _ > Range(DeleteCol & "1:" & DeleteCol & x),CompareStr) > end sub > > -- > joel > ------------------------------------------------------------------------ > joel's Profile:http://www.thecodecage.com/forumz/member.php?u=229 > View this thread:http://www.thecodecage.com/forumz/showthread.php?t=199734 > > http://www.thecodecage.com/forumz |