Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sumproduct issues?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sumproduct issues?
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 "vbastarter" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sumproduct issues?
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 -- Regards, Tom Ogilvy "vbastarter" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT ISSUES | Excel Worksheet Functions | |||
sumproduct issues | Excel Worksheet Functions | |||
Sumproduct issues | Excel Worksheet Functions | |||
Sumproduct issues. | Excel Worksheet Functions | |||
sumproduct issues | Excel Worksheet Functions |