Spreadsheet vlookup help.

[Emoncada]

I have a large spreadsheet with about 10 worksheets in it.
I created one worksheet that I would like to input temporary data that will help me complete my task with the other 9 worksheets.

Example
NC8000 -Worksheet
NC8230 -Worksheet
NC8430 -Worksheet
D500 - Worksheet
Locate - Worksheet (Created for Temporary Data)

These are 4 of the worksheets that have a lot of data.
I have column A that I would like to use to show if data matches with the 10th worksheet("Locate")

So in the Locate Worksheet I have 9 columns all labeled with the name of the other 9 worksheets.

So I would like to put the Serial Number under it's appropriate column in Locate then go to the Worksheet and See under column A where it is located Looking in Column C for the matching Serial Number. Then I can easily find the row and input new data that I need to add.
Otherwise I need to do a ctrl+F and paste the serial numbers for each one and sometimes I get over 30 and that's too much.

Hope that explains what I need. If there is a better way please I am very open for suggestions.

[Bob Phillips]

.

[Emoncada]

It's not working for me. It gives me an error in the "rng =" That comes up Highlighted in blue and "Private Sub Worksheet_Change(ByVal Target As Range)" In Yellow.

can it highlight the entire row if match is found?

[georgiboy]

How about something like this

[VBA]Sub Highlighter()
Dim MyRange As Range, rCell As Range
Dim ShtStop As Integer


For Each pCell In ActiveWorkbook.Sheets
If pCell.Name = "Locate" Then GoTo jump01
ShtStop = WorksheetFunction.CountA(pCell.Range("A:A"))
Set MyRange = pCell.Range("A1:A" & ShtStop)

For Each rCell In MyRange.Cells

If WorksheetFunction.CountIf(Sheets("Locate").Range("A:A"), rCell) > 0 Then
rCell.EntireRow.Font.ColorIndex = 3
End If

Next rCell


jump01:
Next pCell


End Sub[/VBA]

Hope this helps

[Emoncada]

That seems to work Georgiboy, can you have it input something in Column A like "FOUND" so that I can then do a ctrl+F in column A to locate them faster. I have over 30,000 rows of data and that would make it easier. Thanks

[Bob Phillips]

It's not working for me. It gives me an error in the "rng =" That comes up Highlighted in blue and "Private Sub Worksheet_Change(ByVal Target As Range)" In Yellow.

orrected

[Emoncada]

XLD That works nicely too, but how can i modify it so If a paste like 40 numbers how can I have it go one by one? These seems like i have to manually type each one for this to work.

[Bob Phillips]

You will to start with, but mine was intended far more as a workflow process, as you change things, you jump to its entry and do whatever you have to do.

[georgiboy]

Try this you will need to insert a column so that column "A" is empty in the sheets you are searching. It now looks in column "B" for the criteria.

[VBA]Sub Highlighter()
Dim MyRange As Range, rCell As Range
Dim ShtStop As Integer


For Each pCell In ActiveWorkbook.Sheets
If pCell.Name = "Locate" Then GoTo jump01
ShtStop = WorksheetFunction.CountA(pCell.Range("B:B"))
Set MyRange = pCell.Range("B1:B" & ShtStop)

For Each rCell In MyRange.Cells

If WorksheetFunction.CountIf(Sheets("Locate").Range("A:A"), rCell) > 0 Then
rCell.EntireRow.Font.ColorIndex = 3
rCell.Offset(, -1).Value = "Found"
End If

Next rCell


jump01:
Next pCell


End Sub[/VBA]

You could also use a sort or filter to filter out the "Found" word. That way you will have all your data at the top of the page.

[Emoncada]

ok Georgi this is what i used.
[VBA]Sub Highlighter()
Dim MyRange As Range, rCell As Range
Dim ShtStop As Integer


For Each pCell In ActiveWorkbook.Sheets
If pCell.Name = "Locate" Then GoTo jump01
ShtStop = WorksheetFunction.CountA(pCell.Range("C:C"))
Set MyRange = pCell.Range("C1:C" & ShtStop)

For Each rCell In MyRange.Cells

If WorksheetFunction.CountIf(Sheets("Locate").Range("A:K"), rCell) > 0 Then
rCell.EntireRow.Font.ColorIndex = 3
rCell.Offset(, -1).Value = "Found"
End If

Next rCell


jump01:
Next pCell


End Sub
[/VBA]
This works great only thing is it takes a really long time.
Is there a way to make it faster. Possibly instead of using
("Locate").Range("A:K") can it auto see on that spreadsheet for data from ("A3:K"). I think it takes long because it's going threw all those cells and mostly all are blank. Let me know what you think. Thanks

[georgiboy]

Why have you changed it from "A:A" to "A:K" this part is only ever looking for the criteria number in the locate sheet, can this not always be kept in column "A" in the locate sheet? That way it will only count one column rather than "A:K" in the locate sheet.

[VBA]

Sub Highlighter()
Dim MyRange As Range, rCell As Range
Dim ShtStop As Integer


For Each pCell In ActiveWorkbook.Sheets
If pCell.Name = "Locate" Then Goto jump01
ShtStop = WorksheetFunction.CountA(pCell.Range("C:C"))
Set MyRange = pCell.Range("C1:C" & ShtStop)

For Each rCell In MyRange.Cells

If WorksheetFunction.CountIf(Sheets("Locate").Range("A:A"), rCell) > 0 Then
rCell.EntireRow.Font.ColorIndex = 3
rCell.Offset(, -1).Value = "Found"
End If

Next rCell


jump01:
Next pCell


End Sub

[/VBA]

[Emoncada]

It can but i would like to be able to seperate it by model.
Example
Column
.....A...............B...............C.................D................E.. ......
NC8000........NC8230........NC8430.........DC7100.......DC7600...

That way I know how many I should get in each worksheet.

[georgiboy]

oh i see. i am afraid i am all out of ideas now as i am only self taught in vba and excel. Hopefully someone else can come up with an alternative piece of code or streamline my piece. Best of luck.

[Bob Phillips]

This works great only thing is it takes a really long time.
Is there a way to make it faster. Possibly instead of using
("Locate").Range("A:K") can it auto see on that spreadsheet for data from ("A3:K"). I think it takes long because it's going threw all those cells and mostly all are blank. Let me know what you think. Thanks

That won't make any difference, that is highly optimised code that drives COUNTIF.

See if this is better

[vba]

Sub Highlighter()
Dim MyRange As Range, rCell As Range
Dim ShtStop As Long, sh As Worksheet

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

For Each sh In ActiveWorkbook.Sheets

If sh.Name <> "Locate" Then

ShtStop = WorksheetFunction.CountA(sh.Range("C:C"))
Set MyRange = pCell.Range("C1:C" & ShtStop)

For Each rCell In MyRange

If WorksheetFunction.CountIf(Sheets("Locate").Range("A:K"), rCell) > 0 Then

rCell.EntireRow.Font.ColorIndex = 3
rCell.Offset(, -1).Value = "Found"
End If
Next rCell
Next sh

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub
[/vba]

[Emoncada]

that's giving me an error Missing a For on

Next sh

[Bob Phillips]

Missing End If

[vba]

Sub Highlighter()
Dim MyRange As Range, rCell As Range
Dim ShtStop As Long, sh As Worksheet

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

For Each sh In ActiveWorkbook.Sheets

If sh.Name <> "Locate" Then

ShtStop = WorksheetFunction.CountA(sh.Range("C:C"))
Set MyRange = sh.Range("C1:C" & ShtStop)

For Each rCell In MyRange

If WorksheetFunction.CountIf(Sheets("Locate").Range("A:K"), rCell) > 0 Then

rCell.EntireRow.Font.ColorIndex = 3
rCell.Offset(, -1).Value = "Found"
End If
Next rCell
End If
Next sh

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub
[/vba]