sumproduct issues?

[Guest]

Hi I have code using sumproduct to copy paste duplicate names into another
sheet.
I have used sumproduct so that the process is quick. But I can't do things
like TRim. Ucase or even compare duplicates just with first letter in
firstname col. Anyone knows if this can be achieved while using sumproduct ?
Or if theres an alternative method that is as effecient time as sumproduct
Thanks in Advance

 

[Guest]

Sorry here is my current code

Rng(1).Offset(0, Rng.Columns.Count).EntireColumn.Insert
Set Col = Rng.Offset(, Rng.Columns.Count).Resize(, 1).Cells
Set colF = Intersect(Rng, Rng.Columns(strFNameCol & ":" & strFNameCol)).Cells
Set colL = Intersect(Rng, Rng.Columns(strLNameCol & ":" & strLNameCol)).Cells

Col.Formula = "=if(Sumproduct(--(" & colF.Address & "=" & colF(1).Address(0,
1) _
& "),--(" & colL.Address & "=" & colL(1).Address(0, 1) & "))>1,na(),"""")"
On Error Resume Next
Col.SpecialCells(xlFormulas, xlErrors).EntireRow.Copy
Set sh = ActiveWorkbook.Worksheets.Add
Set k = sh.Range("A:AV")
k.Rows.PasteSpecial

Col.SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
On Error GoTo 0
Col.EntireColumn.Delete

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

 

[Tom Ogilvy]

Since you code shows you are writing a formula to a cell, then you shouldn't
be limited beyond the limitations you would find in entering the formula by
hand.

Sumproduct will work with worksheet functions Trim, Left, Upper, but is has
to be in the resulting formula, not in VBA.

If you want to get the best advice on writing sumproduct formulas, ask in
Microsoft.public.excel.worksheet.functions