Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Numbers are text and won't match using a formula
Hello
Cells(2, "b") = "=SAPTasks!A2" 'SAP Personnel....This formula moves the personnel number fro the SAPTasks worksheet into another worksheet containing the formula. Cells(2, "b").AutoFill Destination:=Range(Cells(2, "b"), Cells(br, "b"))...this formula than fills that formula down a number of rows that is contained in the field br Cells(2, "k") = "=IF($b$2:$b$8000="""","""",(INDEX(Personnel!$B$1: $B$1000,MATCH($b$2:$b$8000,Personnel!$A$1:$A$1000, 0))))"...this formula gets the name of the person from the worksheet personnel by matching. The issue is the personnel number from the SAPTasks worksheet is TEXT so it isn't finding the match. I just want to be able to find the name. Excel suggested multiplying by 1 in the field containing the number(Text). I recorded a macro that works and gives teh following but it goes 65536 or something. How can I do the following code better so it converts the number to a number and allows the matching on a designated number of rows in br?????? Columns("A:A").Select Selection.Copy Columns("Q:Q").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Range("R1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "1" Range("R1").Select Application.CommandBars("Task Pane").Visible = False Range("R1").Select Selection.Copy Columns("Q:Q").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _ SkipBlanks:=False, Transpose:=False Columns("Q:Q").Select Selection.Copy Columns("A:A").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Numbers are text and won't match using a formula
Hi Bud,
Try the following. See comments for what it is doing. Not the way the purists would do it but i think that it might be easier for you to understand. Sub Macro1() 'Ensure that column A is formated to numeric Columns("A:A").Select Selection.NumberFormat = "0" 'Select and enter 1 in an empty cell Range("R1").Select ActiveCell.FormulaR1C1 = "1" 'Copy the cell Selection.Copy 'Select from first cell to last used cell in column A 'Identifying last used cell is like selecting the 'last cell in column A (65536 or something.) 'Holding the Ctrl key and pressing up arrow to 'find last used cell in the column. Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp)).Select 'Paste Special multiply Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _ SkipBlanks:=False, Transpose:=False End Sub -- Regards, OssieMac "Bud" wrote: Hello Cells(2, "b") = "=SAPTasks!A2" 'SAP Personnel....This formula moves the personnel number fro the SAPTasks worksheet into another worksheet containing the formula. Cells(2, "b").AutoFill Destination:=Range(Cells(2, "b"), Cells(br, "b"))...this formula than fills that formula down a number of rows that is contained in the field br Cells(2, "k") = "=IF($b$2:$b$8000="""","""",(INDEX(Personnel!$B$1: $B$1000,MATCH($b$2:$b$8000,Personnel!$A$1:$A$1000, 0))))"...this formula gets the name of the person from the worksheet personnel by matching. The issue is the personnel number from the SAPTasks worksheet is TEXT so it isn't finding the match. I just want to be able to find the name. Excel suggested multiplying by 1 in the field containing the number(Text). I recorded a macro that works and gives teh following but it goes 65536 or something. How can I do the following code better so it converts the number to a number and allows the matching on a designated number of rows in br?????? Columns("A:A").Select Selection.Copy Columns("Q:Q").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Range("R1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "1" Range("R1").Select Application.CommandBars("Task Pane").Visible = False Range("R1").Select Selection.Copy Columns("Q:Q").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _ SkipBlanks:=False, Transpose:=False Columns("Q:Q").Select Selection.Copy Columns("A:A").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MATCH search with text and numbers | Excel Worksheet Functions | |||
text match formula not working | Excel Worksheet Functions | |||
match function with text and numbers | Excel Programming | |||
match cell text with text in formula | Excel Worksheet Functions | |||
how to build a formula to match numbers in 2 columns with the equ. | Excel Worksheet Functions |