VLookup Problemo!

[mona4]

Hiya,

I am trying to structure my spreadsheet properly with VLookup. I will try to explain the layout as clear as i can.

I have two worksheets,

1) Worksheet 1 has two columns, Column A has a list of company codes and Column B contains the company Name. This worksheet is updated as some companies may be removed and new added.

2) Worksheet 2 is the working area. Column A is the key for the VLookup, Column B contains the company codes and Column C the Company Name.

Column A has this formula:
=IF(ISNA(VLOOKUP(B3,'S&P'!B:C,1,FALSE)),"No Match",VLOOKUP(B3,'S&P'!B:C,1,FALSE))

It looks up the company code and company name and if it does not match with that in the first worksheet, it displays "No Match".

Right heres the problem, I am not able to identify the new companies and the codes that have been added to worksheet 1 that replaced the existing ones. I hope i have explained it properly!

any suggestions?

 

[fairwinds]

Hi,

Are you saying that when you enter a new company in sheet1 you want it to appear in sheet2 without you entering the company code there?

 

[mona4]

well i dont enter a new company and its code in sheet 1 myself, i refresh the list from a website. But yes i would like it to appear in sheet 2 maybe at the bottom of the existing list or in the middle, as long as it is identifiable by highlighting automatically.

 

[fairwinds]

You could try something like this.

When you run this it should take the entries form col A in sheet1 that is not present in col A in sheet2 and put them after the other data in red cells.

Sub xxx()
Dim ce As Range
For Each ce In Range(Sheet1.Range("A1"), Sheet1.Range("A65536").End(xlUp))

With Range(Sheet2.Range("A1"), Sheet2.Range("A65536").End(xlUp))
    Set c = .Find(ce, LookIn:=xlValues)
    If c Is Nothing Then
      With Sheet2.Range("A65536").End(xlUp).Offset(1)
          .Value = ce
         .Interior.ColorIndex = 3
      End With
    End If
End With

Next ce

End Sub

 

[mona4]

This line is giving a run time error


For Each ce In Range(Sheet2.Range("A5"), Sheet2.Range("A65536").End(xlUp))

 

[fairwinds]

I guess you might have renamed the sheets.

Try this, but update the code if the sheets have different names.

Sub xxx()
Dim ce As Range
For Each ce In Range(Sheets("Sheet1").Range("A1"), Sheets("Sheet1").Range("A65536").End(xlUp))

With Range(Sheets("Sheet2").Range("A5"), Sheets("Sheet2").Range("A65536").End(xlUp))
    Set c = .Find(ce, LookIn:=xlValues)
    If c Is Nothing Then
      With Sheets("Sheet2").Range("A65536").End(xlUp).Offset(1)
          .Value = ce
         .Interior.ColorIndex = 3
      End With
    End If
End With

Next ce

End Sub

 

[mona4]

I have replaced the code with the new one but the line starting with For Each is still giving the error message once run.

 

[fairwinds]

Do you have a sheet called Sheet1 and a sheet called Sheet2 ?

 

[mona4]

no they are called something else: sheet 1 is - S&P and sheet 2- S&P portfolio changes. they are actually sheet 2 and 3 but i changed your code to say sheet 2 and sheet 3 where needed and it still gave error.

 

[fairwinds]

So:

Sub xxx()
Dim ce As Range
For Each ce In Range(Sheets("S&P").Range("A1"), Sheets("S&P").Range("A65536").End(xlUp))

With Range(Sheets("S&P portfolio changes").Range("A5"), Sheets("S&P portfolio changes").Range("A65536").End(xlUp))
    Set c = .Find(ce, LookIn:=xlValues)
    If c Is Nothing Then
      With Sheets("S&P portfolio changes").Range("A65536").End(xlUp).Offset(1)
          .Value = ce
         .Interior.ColorIndex = 3
      End With
    End If
End With

Next ce

End Sub