PDA

View Full Version : VLookUP to Update List?



JP2R
12-03-2008, 07:34 AM
Greetings --

This may be a very basic question - but I'm stumped.

I have a main page that has a list of Events in column "A"

Then I have a tab for each month - Pivot Tables - that show the list of Events that occurred for that particular month...

What I am trying to do is --

Create code that takes column "A" from monthly tab and looks to the main tab column "A" and if it exists - does nothing - if it does not exist inserts it...

One thing I'm having trouble with is that it needs to be sorted A-Z....

I have this code that someone here had helped me with (I'm sorry I can't remember the name - I thought I had put it in the notes/comments)

Sub InsertOffender()
'
' InsertOffender Macro
' Macro recorded 4/18/2008
'
' Keyboard Shortcut: Ctrl+k
'
Dim intNumRows_Bedford_Matrix As Integer
Dim strNewCTI As String
Dim strCTI As String
Dim myRange As Range

'find out how many rows in Bedford_Matrix sheet
intNumRows_Bedford_Matrix = NumRows("Bedford_Matrix")
For a = 2 To NumRows("Oct2008") - 1
strNewCTI = Worksheets("Oct2008").Range("AD" & a).Value
'strCTI = Worksheets("Oct2008").Range("b" & a).Value
intNumRows_Bedford_Matrix = NumRows("Bedford_Matrix")
Set myRange = Worksheets("Bedford_Matrix").Range("A1:L" & intNumRows_Bedford_Matrix).Find(strNewCTI, , xlValues)
If Not myRange Is Nothing Then
Rows(myRange.Row + 1).Select
Selection.Insert xlShiftDown
strrow = myRange.Row + 1
Range("A" & strrow).Select
ActiveCell.Value = strNewCTI
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("D" & strrow).Select
ActiveCell.Value = strCTI
Else
Range("A" & intNumRows_Bedford_Matrix).Select
ActiveCell.Value = strNewCTI
Range("D" & intNumRows_Bedford_Matrix).Select
ActiveCell.Value = strCTI
End If
Next a
End Sub
Private Function NumRows(strSheet As String) As Integer
Dim intNumRows As Integer
intNumRows = 1
Do Until Worksheets(strSheet).Range("A" & intNumRows) = ""
intNumRows = intNumRows + 1
Loop
NumRows = intNumRows
End Function
http://www.screencast.com/users/Monk/folders/Jing/media/aa2fbe75-1f86-4fbc-b4af-12dedefce0a1

I really appreciate any help you could provide...
-- Monk