TrippyTom
09-10-2012, 02:06 PM
Hi everyone,
I've run into something I can't seem to wrap my brain around so I'm coming to you for your knowledge and experience.
My Problem:
file1 = "MASTER.xls". It has 362 rows with 2 columns. COL A = various text I want to look for, COL B = "Threat Level" (either LOW or HIGH)
file2 = an auto-generated report by our system.
What I want to do:
I would like to check the first column in that report for ANY occurrence of the words in my master list. I thought a wildcard in a VLOOKUP formula would work, but I don't quite have the syntax because it's only correctly reporting entire cells matching (not parts of the cell). Or maybe I'm going at this the wrong way.
Since this is an auto-generated report run weekly, I would prefer to put this in a macro. Here's what I have so far.
Sub test()
Application.ScreenUpdating = False
'On Error Resume Next
Dim lastRow As Long
Dim i As Integer
Dim lookFor As Range
Dim rng As Range
Dim col As Integer
Dim found As Variant
'Copy format of column headers to new column named "Threat Level"
Range("B5").Value = "Threat Level"
Range("A5").Copy
Range("B5").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
'find last row
lastRow = Range("A65536").End(xlUp).Row
'Set alignment in results columns to Top and Left
With Range("B:B")
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
End With
'--VLOOKUP PASS--
Set rng = Workbooks("MASTER.xls").Sheets(1).Range("keywords")
col = 2
For i = 6 To lastRow
Set lookFor = Range("A" & i)
'I used wildcards before and after the lookup value from the MASTER list here.
Range("B" & i).Formula = Application.VLookup("*" & lookFor.Value & "*", rng, col, 0)
Next i
Range("A1").Select
Application.ScreenUpdating = True
End Sub
Could anyone steer me toward the light? This is not homework. My boss "volunteered" me to figure this out for someone.
I've run into something I can't seem to wrap my brain around so I'm coming to you for your knowledge and experience.
My Problem:
file1 = "MASTER.xls". It has 362 rows with 2 columns. COL A = various text I want to look for, COL B = "Threat Level" (either LOW or HIGH)
file2 = an auto-generated report by our system.
What I want to do:
I would like to check the first column in that report for ANY occurrence of the words in my master list. I thought a wildcard in a VLOOKUP formula would work, but I don't quite have the syntax because it's only correctly reporting entire cells matching (not parts of the cell). Or maybe I'm going at this the wrong way.
Since this is an auto-generated report run weekly, I would prefer to put this in a macro. Here's what I have so far.
Sub test()
Application.ScreenUpdating = False
'On Error Resume Next
Dim lastRow As Long
Dim i As Integer
Dim lookFor As Range
Dim rng As Range
Dim col As Integer
Dim found As Variant
'Copy format of column headers to new column named "Threat Level"
Range("B5").Value = "Threat Level"
Range("A5").Copy
Range("B5").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
'find last row
lastRow = Range("A65536").End(xlUp).Row
'Set alignment in results columns to Top and Left
With Range("B:B")
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
End With
'--VLOOKUP PASS--
Set rng = Workbooks("MASTER.xls").Sheets(1).Range("keywords")
col = 2
For i = 6 To lastRow
Set lookFor = Range("A" & i)
'I used wildcards before and after the lookup value from the MASTER list here.
Range("B" & i).Formula = Application.VLookup("*" & lookFor.Value & "*", rng, col, 0)
Next i
Range("A1").Select
Application.ScreenUpdating = True
End Sub
Could anyone steer me toward the light? This is not homework. My boss "volunteered" me to figure this out for someone.