Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Frantic Excel-er
 
Posts: n/a
Default 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   Report Post  
Fred Smith
 
Posts: n/a
Default

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   Report Post  
Frantic Excel-er
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
2 part question - macro / command button John Excel Discussion (Misc queries) 3 April 16th 05 09:00 PM
Macro Question? Greg B Excel Discussion (Misc queries) 7 March 9th 05 02:08 PM
Macro Question? Greg B Excel Worksheet Functions 7 March 9th 05 02:08 PM
Excel Macro Question about Conditional Formatting David Britton via OfficeKB.com New Users to Excel 3 February 10th 05 03:23 PM
An easy macro question and one I believe to be a little more diffi TroutKing Excel Worksheet Functions 3 January 18th 05 10:17 PM


All times are GMT +1. The time now is 12:15 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"