View Full Version : Spreadsheet vlookup help.
Emoncada
07-28-2008, 10:00 AM
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
07-28-2008, 10:21 AM
.
Emoncada
07-28-2008, 10:53 AM
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
07-28-2008, 11:21 AM
How about something like this
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
Hope this helps
Emoncada
07-29-2008, 06:21 AM
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
07-29-2008, 06:52 AM
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
07-29-2008, 07:16 AM
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
07-29-2008, 08:22 AM
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
07-29-2008, 09:01 AM
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.
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
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
07-29-2008, 09:20 AM
ok Georgi this is what i used.
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
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
07-29-2008, 09:34 AM
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.
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
Emoncada
07-29-2008, 09:37 AM
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
07-29-2008, 10:05 AM
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
07-29-2008, 01:09 PM
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
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
Emoncada
07-29-2008, 01:27 PM
that's giving me an error Missing a For on
Next sh
Bob Phillips
07-29-2008, 03:13 PM
Missing End If
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.