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