Home |
Search |
Today's Posts |
#1
|
|||
|
|||
MACRO Question - Help Please!!!!!!!!!!!!!!!!!
Hi All,
I am having a hard time getting my macro to run its Vlookup portion with only the selection that I want....I am going to insert the WHOLE Macro code and see if anyone can help me out..... What I am trying to do is to get the selection after I find "x" on the modified page to do a vlookup....(I believe I have to sort my selection in ascending order first, but I don't know how to do that either ") ) I have tried to correct the problem myself, but I am unable to figure out how to tell VB to look at only the selection that I have in the "Modified" worksheet.... If anyone can help, I would appreciate it.......thanks in advance Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(13, 1), Array(26, 1), Array(38, 1), _ Array(60, 1), Array(70, 1), Array(78, 1), Array(83, 1), Array(99, 1), Array(111, 1), Array( _ 117, 1), Array(123, 1), Array(131, 1), Array(142, 1), Array(146, 1), Array(175, 1), Array( _ 187, 1), Array(202, 1)) Cells.Select ActiveWindow.Zoom = 80 Cells.EntireColumn.AutoFit Sheets().Copy After:=Sheets(1) Sheets(2).Select Sheets(2).Name = "Modified" Cells.Select Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Columns("C:C").Find(What:="x").EntireRow.Insert Columns("C:C").Find(What:="x").EntireRow.Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Sheets.Add Sheets("Sheet1").Select Sheets("Sheet1").Move After:=Sheets(3) Sheets("Sheet1").Select Sheets("Sheet1").Name = "USB" Columns("A:A").Select Selection.NumberFormat = "@" ActiveCell.FormulaR1C1 = "04165" Range("A2").Select ActiveCell.FormulaR1C1 = "14709" Range("A3").Select ActiveCell.FormulaR1C1 = "14716" Range("A4").Select ActiveCell.FormulaR1C1 = "24704" Range("A5").Select ActiveCell.FormulaR1C1 = "44705" Range("A6").Select ActiveCell.FormulaR1C1 = "44710" Range("A7").Select ActiveCell.FormulaR1C1 = "54780" Range("A8").Select ActiveCell.FormulaR1C1 = "64651" Range("A9").Select ActiveCell.FormulaR1C1 = "64660" Range("A10").Select ActiveCell.FormulaR1C1 = "64666" Range("A11").Select ActiveCell.FormulaR1C1 = "64667" Range("A12").Select ActiveCell.FormulaR1C1 = "74720" Range("A13").Select ActiveCell.FormulaR1C1 = "74725" Range("A14").Select ActiveCell.FormulaR1C1 = "74731" Range("A15").Select ActiveCell.FormulaR1C1 = "74735" Range("A16").Select ActiveCell.FormulaR1C1 = "74736" Range("A17").Select ActiveCell.FormulaR1C1 = "74737" Range("A18").Select ActiveCell.FormulaR1C1 = "74739" Range("A19").Select ActiveCell.FormulaR1C1 = "74743" Range("A20").Select ActiveCell.FormulaR1C1 = "74751" Range("A21").Select ActiveCell.FormulaR1C1 = "74752" Range("A22").Select ActiveCell.FormulaR1C1 = "74753" Range("A23").Select ActiveCell.FormulaR1C1 = "74765" Range("A24").Select ActiveCell.FormulaR1C1 = "74770" Range("A25").Select ActiveCell.FormulaR1C1 = "74781" Range("A26").Select ActiveCell.FormulaR1C1 = "74782" Range("A27").Select ActiveCell.FormulaR1C1 = "74783" Range("A28").Select ActiveCell.FormulaR1C1 = "74784" Range("A29").Select ActiveCell.FormulaR1C1 = "74785" Range("A30").Select ActiveCell.FormulaR1C1 = "84703" Range("A31").Select ActiveCell.FormulaR1C1 = "84789" Range("A32").Select Sheets("Modified").Select Dim i As Long Dim iPos As Long For i = 2 To Cells(Rows.Count, "D").End(xlUp).Row iPos = 0 On Error Resume Next iPos = Application.Match(Val(Left(Cells(i, "D").Value, 5)), _ Worksheets("USB").Range("$D:$D"), 0) On Error GoTo 0 If iPos = 0 Then Cells(i, "D").Interior.ColorIndex = 3 Else Cells(i, "D").Interior.ColorIndex = x.ColorIndexNone End If Next i End Sub |
#2
|
|||
|
|||
There's no Vlookup in your code. Is it the Match you are having trouble with?
-- Regards, Fred Please reply to newsgroup, not e-mail "Frantic Excel-er" wrote in message ... Hi All, I am having a hard time getting my macro to run its Vlookup portion with only the selection that I want....I am going to insert the WHOLE Macro code and see if anyone can help me out..... What I am trying to do is to get the selection after I find "x" on the modified page to do a vlookup....(I believe I have to sort my selection in ascending order first, but I don't know how to do that either ") ) I have tried to correct the problem myself, but I am unable to figure out how to tell VB to look at only the selection that I have in the "Modified" worksheet.... If anyone can help, I would appreciate it.......thanks in advance Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(13, 1), Array(26, 1), Array(38, 1), _ Array(60, 1), Array(70, 1), Array(78, 1), Array(83, 1), Array(99, 1), Array(111, 1), Array( _ 117, 1), Array(123, 1), Array(131, 1), Array(142, 1), Array(146, 1), Array(175, 1), Array( _ 187, 1), Array(202, 1)) Cells.Select ActiveWindow.Zoom = 80 Cells.EntireColumn.AutoFit Sheets().Copy After:=Sheets(1) Sheets(2).Select Sheets(2).Name = "Modified" Cells.Select Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Columns("C:C").Find(What:="x").EntireRow.Insert Columns("C:C").Find(What:="x").EntireRow.Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Sheets.Add Sheets("Sheet1").Select Sheets("Sheet1").Move After:=Sheets(3) Sheets("Sheet1").Select Sheets("Sheet1").Name = "USB" Columns("A:A").Select Selection.NumberFormat = "@" ActiveCell.FormulaR1C1 = "04165" Range("A2").Select ActiveCell.FormulaR1C1 = "14709" Range("A3").Select ActiveCell.FormulaR1C1 = "14716" Range("A4").Select ActiveCell.FormulaR1C1 = "24704" Range("A5").Select ActiveCell.FormulaR1C1 = "44705" Range("A6").Select ActiveCell.FormulaR1C1 = "44710" Range("A7").Select ActiveCell.FormulaR1C1 = "54780" Range("A8").Select ActiveCell.FormulaR1C1 = "64651" Range("A9").Select ActiveCell.FormulaR1C1 = "64660" Range("A10").Select ActiveCell.FormulaR1C1 = "64666" Range("A11").Select ActiveCell.FormulaR1C1 = "64667" Range("A12").Select ActiveCell.FormulaR1C1 = "74720" Range("A13").Select ActiveCell.FormulaR1C1 = "74725" Range("A14").Select ActiveCell.FormulaR1C1 = "74731" Range("A15").Select ActiveCell.FormulaR1C1 = "74735" Range("A16").Select ActiveCell.FormulaR1C1 = "74736" Range("A17").Select ActiveCell.FormulaR1C1 = "74737" Range("A18").Select ActiveCell.FormulaR1C1 = "74739" Range("A19").Select ActiveCell.FormulaR1C1 = "74743" Range("A20").Select ActiveCell.FormulaR1C1 = "74751" Range("A21").Select ActiveCell.FormulaR1C1 = "74752" Range("A22").Select ActiveCell.FormulaR1C1 = "74753" Range("A23").Select ActiveCell.FormulaR1C1 = "74765" Range("A24").Select ActiveCell.FormulaR1C1 = "74770" Range("A25").Select ActiveCell.FormulaR1C1 = "74781" Range("A26").Select ActiveCell.FormulaR1C1 = "74782" Range("A27").Select ActiveCell.FormulaR1C1 = "74783" Range("A28").Select ActiveCell.FormulaR1C1 = "74784" Range("A29").Select ActiveCell.FormulaR1C1 = "74785" Range("A30").Select ActiveCell.FormulaR1C1 = "84703" Range("A31").Select ActiveCell.FormulaR1C1 = "84789" Range("A32").Select Sheets("Modified").Select Dim i As Long Dim iPos As Long For i = 2 To Cells(Rows.Count, "D").End(xlUp).Row iPos = 0 On Error Resume Next iPos = Application.Match(Val(Left(Cells(i, "D").Value, 5)), _ Worksheets("USB").Range("$D:$D"), 0) On Error GoTo 0 If iPos = 0 Then Cells(i, "D").Interior.ColorIndex = 3 Else Cells(i, "D").Interior.ColorIndex = x.ColorIndexNone End If Next i End Sub |
#3
|
|||
|
|||
Yes, I am having difficulty with the match....(sorry, I changed from vlookup
to match per response from an earlier question I posted on the board)... "Fred Smith" wrote: There's no Vlookup in your code. Is it the Match you are having trouble with? -- Regards, Fred Please reply to newsgroup, not e-mail "Frantic Excel-er" wrote in message ... Hi All, I am having a hard time getting my macro to run its Vlookup portion with only the selection that I want....I am going to insert the WHOLE Macro code and see if anyone can help me out..... What I am trying to do is to get the selection after I find "x" on the modified page to do a vlookup....(I believe I have to sort my selection in ascending order first, but I don't know how to do that either ") ) I have tried to correct the problem myself, but I am unable to figure out how to tell VB to look at only the selection that I have in the "Modified" worksheet.... If anyone can help, I would appreciate it.......thanks in advance Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(13, 1), Array(26, 1), Array(38, 1), _ Array(60, 1), Array(70, 1), Array(78, 1), Array(83, 1), Array(99, 1), Array(111, 1), Array( _ 117, 1), Array(123, 1), Array(131, 1), Array(142, 1), Array(146, 1), Array(175, 1), Array( _ 187, 1), Array(202, 1)) Cells.Select ActiveWindow.Zoom = 80 Cells.EntireColumn.AutoFit Sheets().Copy After:=Sheets(1) Sheets(2).Select Sheets(2).Name = "Modified" Cells.Select Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Columns("C:C").Find(What:="x").EntireRow.Insert Columns("C:C").Find(What:="x").EntireRow.Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Sheets.Add Sheets("Sheet1").Select Sheets("Sheet1").Move After:=Sheets(3) Sheets("Sheet1").Select Sheets("Sheet1").Name = "USB" Columns("A:A").Select Selection.NumberFormat = "@" ActiveCell.FormulaR1C1 = "04165" Range("A2").Select ActiveCell.FormulaR1C1 = "14709" Range("A3").Select ActiveCell.FormulaR1C1 = "14716" Range("A4").Select ActiveCell.FormulaR1C1 = "24704" Range("A5").Select ActiveCell.FormulaR1C1 = "44705" Range("A6").Select ActiveCell.FormulaR1C1 = "44710" Range("A7").Select ActiveCell.FormulaR1C1 = "54780" Range("A8").Select ActiveCell.FormulaR1C1 = "64651" Range("A9").Select ActiveCell.FormulaR1C1 = "64660" Range("A10").Select ActiveCell.FormulaR1C1 = "64666" Range("A11").Select ActiveCell.FormulaR1C1 = "64667" Range("A12").Select ActiveCell.FormulaR1C1 = "74720" Range("A13").Select ActiveCell.FormulaR1C1 = "74725" Range("A14").Select ActiveCell.FormulaR1C1 = "74731" Range("A15").Select ActiveCell.FormulaR1C1 = "74735" Range("A16").Select ActiveCell.FormulaR1C1 = "74736" Range("A17").Select ActiveCell.FormulaR1C1 = "74737" Range("A18").Select ActiveCell.FormulaR1C1 = "74739" Range("A19").Select ActiveCell.FormulaR1C1 = "74743" Range("A20").Select ActiveCell.FormulaR1C1 = "74751" Range("A21").Select ActiveCell.FormulaR1C1 = "74752" Range("A22").Select ActiveCell.FormulaR1C1 = "74753" Range("A23").Select ActiveCell.FormulaR1C1 = "74765" Range("A24").Select ActiveCell.FormulaR1C1 = "74770" Range("A25").Select ActiveCell.FormulaR1C1 = "74781" Range("A26").Select ActiveCell.FormulaR1C1 = "74782" Range("A27").Select ActiveCell.FormulaR1C1 = "74783" Range("A28").Select ActiveCell.FormulaR1C1 = "74784" Range("A29").Select ActiveCell.FormulaR1C1 = "74785" Range("A30").Select ActiveCell.FormulaR1C1 = "84703" Range("A31").Select ActiveCell.FormulaR1C1 = "84789" Range("A32").Select Sheets("Modified").Select Dim i As Long Dim iPos As Long For i = 2 To Cells(Rows.Count, "D").End(xlUp).Row iPos = 0 On Error Resume Next iPos = Application.Match(Val(Left(Cells(i, "D").Value, 5)), _ Worksheets("USB").Range("$D:$D"), 0) On Error GoTo 0 If iPos = 0 Then Cells(i, "D").Interior.ColorIndex = 3 Else Cells(i, "D").Interior.ColorIndex = x.ColorIndexNone End If Next i End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 part question - macro / command button | Excel Discussion (Misc queries) | |||
Macro Question? | Excel Discussion (Misc queries) | |||
Macro Question? | Excel Worksheet Functions | |||
Excel Macro Question about Conditional Formatting | New Users to Excel | |||
An easy macro question and one I believe to be a little more diffi | Excel Worksheet Functions |