Craig Johnson
asked on
Excel Search
Hey...quick question guys!
I have created a macro to seach through a long list of data looking for the key word "*Delisted*". This macro works great, but now I want it to also look for "*Delisted*" or "*=*". Thus my goal is to have it find eithier of these two words. Attached is the code I have currently...
Thanks! Sorry if this is an easy question...I'm still learning VBA
CWJ
-------------------------- ---------- -
Sub Copy_Delisted_Tickers()
Dim wb As Workbook, ws As Worksheet, ws_d As Worksheet, rng As Range, cel As Range
Set wb = ActiveWorkbook
Set ws = Worksheets("Reuters Data Check Tool")
Set rng = Intersect(ws.Columns("G"), ws.UsedRange) ' change to 'Description' column
Set ws_d = Worksheets("Delisted Report")
ws.Columns("B").Copy
ws.Columns("G").PasteSpeci al Paste:=xlPasteValues, Operation:=xlNone
Sheets("Delisted Report").Select
Range("A2:A1000").Select
Selection.ClearContents
'ws_d.UsedRange.Offset(1). ClearConte nts
Set ws = ActiveSheet
For Each cel In rng
If Not IsEmpty(cel) And Not IsError(cel) Then
If cel.Value Like "*=*" Then
cel.Offset(0, -6).Copy
ws_d.Range("A65536").End(x lUp).Offse t(1).Paste Special xlAll
End If
End If
Next cel
ws_d.Move Before:=Sheets(4)
wb.Worksheets("ADR Report").Move Before:=Sheets(4)
ws_d.Tab.ColorIndex = 5
Sheets("Reuters Data Check Tool").Select
Range("D2").Select
Selection.Copy
Sheets("Info").Select
Range("H13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("H14").Select
Application.DisplayAlerts = True
Call Recalc_FactSet_Data
End Sub
-------------------------- -
I have created a macro to seach through a long list of data looking for the key word "*Delisted*". This macro works great, but now I want it to also look for "*Delisted*" or "*=*". Thus my goal is to have it find eithier of these two words. Attached is the code I have currently...
Thanks! Sorry if this is an easy question...I'm still learning VBA
CWJ
--------------------------
Sub Copy_Delisted_Tickers()
Dim wb As Workbook, ws As Worksheet, ws_d As Worksheet, rng As Range, cel As Range
Set wb = ActiveWorkbook
Set ws = Worksheets("Reuters Data Check Tool")
Set rng = Intersect(ws.Columns("G"),
Set ws_d = Worksheets("Delisted Report")
ws.Columns("B").Copy
ws.Columns("G").PasteSpeci
Sheets("Delisted Report").Select
Range("A2:A1000").Select
Selection.ClearContents
'ws_d.UsedRange.Offset(1).
Set ws = ActiveSheet
For Each cel In rng
If Not IsEmpty(cel) And Not IsError(cel) Then
If cel.Value Like "*=*" Then
cel.Offset(0, -6).Copy
ws_d.Range("A65536").End(x
End If
End If
Next cel
ws_d.Move Before:=Sheets(4)
wb.Worksheets("ADR Report").Move Before:=Sheets(4)
ws_d.Tab.ColorIndex = 5
Sheets("Reuters Data Check Tool").Select
Range("D2").Select
Selection.Copy
Sheets("Info").Select
Range("H13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("H14").Select
Application.DisplayAlerts = True
Call Recalc_FactSet_Data
End Sub
--------------------------
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.