IF Loop...I think?

B

Benzoli7

Board Regular
Joined
Jan 11, 2010
Messages
136
Thanks for viewing this. Can anyone help me with the following code. I need to make some kind of IF loop that either uses or skips the following code if an autofilter in column AA finds the word "NEW" or not. I can't quite get it figured out. I know the code below isn't the cleanest so please forgive me for that. Thanks so much for your help.

HTML: 
 Sheets("Rate Comparison").Activate
    Range("A6").Select
    Range("A6").AutoFilter
    Range("A6").AutoFilter Field:=27, _
        Criteria1:="=New"
    Range("A7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).copy
    Sheets("Proposed Ranking").Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("C" & FinalRow + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Rate Comparison").Select
    Range("C7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).copy
    Sheets("Proposed Ranking").Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("E" & FinalRow + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Rate Comparison").Select
    Range("D7:J7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).copy
    Sheets("Proposed Ranking").Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("F" & FinalRow + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Rate Comparison").Select
    Range("Q7:R7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).copy
    Sheets("Proposed Ranking").Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("Q" & FinalRow + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Rate Comparison").Select
    Range("B7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).copy
    Sheets("Proposed Ranking").Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("O" & FinalRow + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Rate Comparison").Select
    Range("K7:L7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).copy
    Sheets("Proposed Ranking").Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A" & FinalRow + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   Sheets("Rate Comparison").Select
   Range("A6").AutoFilter
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
Benzoli7 said:
Thanks for viewing this. Can anyone help me with the following code. I need to make some kind of IF loop that either uses or skips the following code if an autofilter in column AA finds the word "NEW" or not. I can't quite get it figured out. I know the code below isn't the cleanest so please forgive me for that. Thanks so much for your help.

HTML: 
 Sheets("Rate Comparison").Activate
    Range("A6").Select
    Range("A6").AutoFilter
    Range("A6").AutoFilter Field:=27, _
        Criteria1:="=New"
    Range("A7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).copy
    Sheets("Proposed Ranking").Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("C" & FinalRow + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Rate Comparison").Select
    Range("C7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).copy
    Sheets("Proposed Ranking").Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("E" & FinalRow + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Rate Comparison").Select
    Range("D7:J7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).copy
    Sheets("Proposed Ranking").Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("F" & FinalRow + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Rate Comparison").Select
    Range("Q7:R7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).copy
    Sheets("Proposed Ranking").Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("Q" & FinalRow + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Rate Comparison").Select
    Range("B7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).copy
    Sheets("Proposed Ranking").Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("O" & FinalRow + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Rate Comparison").Select
    Range("K7:L7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).copy
    Sheets("Proposed Ranking").Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A" & FinalRow + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   Sheets("Rate Comparison").Select
   Range("A6").AutoFilter
Click to expand...


dim asd as variant
dim search as string

search = "NEW"

asd = Application.VLookup(search, Worksheets("Sheet1").Range("AA2:AA999"), 1, False)

If asd = "NEW" then
exit sub
else
'run code
end if
 
Upvote 0
Hello bensonsearch,


Noticed the compile error by missing .worksheetfunction.



Application.WorksheetFunction.VLookup("new", Worksheets("Sheet1").Range("AA2:AA999"), 1, False)



-Jeff
 
Upvote 0
repairman615 said:
Hello bensonsearch,


Noticed the compile error by missing .worksheetfunction.



Application.WorksheetFunction.VLookup("new", Worksheets("Sheet1").Range("AA2:AA999"), 1, False)



-Jeff
Click to expand...


Hey Jeff.

Really? I have used before with just application.vlookup. thanx for letting me know
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Dynamic Address by VBA
Replies
8
Views
205
MikeVol
MikeVol
C
  • Question Question
Please help clean up my convoluted VBA code. It seems very redundant and repetitive.
Replies
1
Views
439
myall_blues
myall_blues
willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
390
willow1985
willow1985
E
  • Solved
macro for copying values to the next blank cell
Replies
5
Views
453
Micron
M
F
  • Solved
How to make loop stop when the cell is empty? (VBA)
Replies
3
Views
295
kvsrinivasamurthy
K

Forum statistics

Threads
1,224,586
Messages
6,179,729
Members
452,939
Latest member
WCrawford

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top