Find values 
Author Message
 Find values

I am new with VBA scripts for Excel and I have a problem that I would
love to get some help with. I have a report with several tabs. On one
of the tabs I will have Workorders pasted in manually from a different
report. This is not in the beginning of the page but on lets say row
63. There are not always the same amount of workorders needed so
sometimes I will need to have it run to row 70 other times 73 etc so I
know that I need to do a loop that "sense" the last value. I need get
the values and check to see if they are anywhere else on the report
and if they are I need it to be bolded both the workorder I pasted in
as well as all the places on the report (can be more then one
occurance as well as different sheets). All help a newbie can get is
very appreciated! Thanks!


Sun, 04 Dec 2005 01:56:18 GMT  
 Find values
VBAN

Try this macro.  I've included explanations, but if you have more questions,
be sure to post back.

Sub FindWOs()

Dim sh As Worksheet
Dim ListSh As Worksheet
Dim ListRng As Range
Dim cell As Range
Dim FirstAddress As String
Dim FoundCell As Range

'Set sheet which contains list of workorders
Set ListSh = ThisWorkbook.Sheets("sheet1")

'Set range which contains work orders
Set ListRng = ListSh.Range("A63", ListSh.Range("a63").End(xlDown))

'Loop through cells in range
For Each cell In ListRng.Cells

    'Loop through worksheets
    For Each sh In ThisWorkbook.Worksheets

        'Find the first cell that matches
        Set FoundCell = sh.Cells.Find(cell.Value, , , xlWhole)

        'If a match is found
        If Not FoundCell Is Nothing Then

            'Store the address of the match
            FirstAddress = FoundCell.Address

            'If it finds a match in the list, don't do anything
            'otherwise bold the cell in the list and the match
            If FoundCell.Address <> cell.Address Then
                cell.Font.Bold = True
                FoundCell.Font.Bold = True
            End If

            'Start a loop
            Do

                'Find the next match
                Set FoundCell = sh.Cells.Find(cell.Value, FoundCell, ,
xlWhole)

                'Same as above
                If FoundCell.Address <> cell.Address Then
                    cell.Font.Bold = True
                    FoundCell.Font.Bold = True
                End If

            'Stop looping when it goes back to the first cell found
            Loop Until FoundCell.Address = FirstAddress
        End If
    Next sh
Next cell

End Sub

--
{*filter*} Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.


Quote:
> I am new with VBA scripts for Excel and I have a problem that I would
> love to get some help with. I have a report with several tabs. On one
> of the tabs I will have Workorders pasted in manually from a different
> report. This is not in the beginning of the page but on lets say row
> 63. There are not always the same amount of workorders needed so
> sometimes I will need to have it run to row 70 other times 73 etc so I
> know that I need to do a loop that "sense" the last value. I need get
> the values and check to see if they are anywhere else on the report
> and if they are I need it to be bolded both the workorder I pasted in
> as well as all the places on the report (can be more then one
> occurance as well as different sheets). All help a newbie can get is
> very appreciated! Thanks!



Sun, 04 Dec 2005 03:35:14 GMT  
 Find values
Dick,

I cannot thank you enough. It looks great. I have not been able to
test it on the real data yet but am looking forward to do that in the
morning. THANK YOU SO MUCH, you made my day!!

Quote:

> VBAN

> Try this macro.  I've included explanations, but if you have more questions,
> be sure to post back.

> Sub FindWOs()

> Dim sh As Worksheet
> Dim ListSh As Worksheet
> Dim ListRng As Range
> Dim cell As Range
> Dim FirstAddress As String
> Dim FoundCell As Range

> 'Set sheet which contains list of workorders
> Set ListSh = ThisWorkbook.Sheets("sheet1")

> 'Set range which contains work orders
> Set ListRng = ListSh.Range("A63", ListSh.Range("a63").End(xlDown))

> 'Loop through cells in range
> For Each cell In ListRng.Cells

>     'Loop through worksheets
>     For Each sh In ThisWorkbook.Worksheets

>         'Find the first cell that matches
>         Set FoundCell = sh.Cells.Find(cell.Value, , , xlWhole)

>         'If a match is found
>         If Not FoundCell Is Nothing Then

>             'Store the address of the match
>             FirstAddress = FoundCell.Address

>             'If it finds a match in the list, don't do anything
>             'otherwise bold the cell in the list and the match
>             If FoundCell.Address <> cell.Address Then
>                 cell.Font.Bold = True
>                 FoundCell.Font.Bold = True
>             End If

>             'Start a loop
>             Do

>                 'Find the next match
>                 Set FoundCell = sh.Cells.Find(cell.Value, FoundCell, ,
> xlWhole)

>                 'Same as above
>                 If FoundCell.Address <> cell.Address Then
>                     cell.Font.Bold = True
>                     FoundCell.Font.Bold = True
>                 End If

>             'Stop looping when it goes back to the first cell found
>             Loop Until FoundCell.Address = FirstAddress
>         End If
>     Next sh
> Next cell

> End Sub

> --
>{*filter*} Kusleika
> MVP - Excel
> www.dicks-clicks.com
> Post all replies to the newsgroup.



> > I am new with VBA scripts for Excel and I have a problem that I would
> > love to get some help with. I have a report with several tabs. On one
> > of the tabs I will have Workorders pasted in manually from a different
> > report. This is not in the beginning of the page but on lets say row
> > 63. There are not always the same amount of workorders needed so
> > sometimes I will need to have it run to row 70 other times 73 etc so I
> > know that I need to do a loop that "sense" the last value. I need get
> > the values and check to see if they are anywhere else on the report
> > and if they are I need it to be bolded both the workorder I pasted in
> > as well as all the places on the report (can be more then one
> > occurance as well as different sheets). All help a newbie can get is
> > very appreciated! Thanks!



Sun, 04 Dec 2005 11:55:00 GMT  
 Find values
Dick,

Thank you so much for helping me out yesterday writing the FindWOs
script. I learned a lot from the code you provided. When I compile the
code in Excel it have an eternal loop (hourglass never goes out) and I
have to kill the process manually. I am not sure why this happens but
it might have something to do with the WOs are imbedded with other
text in the same cell and the cell is only looking for cells with
exact value could this be it? Also the results is not in the same
column as the inputed data, is this a factor? Do you need me to send
the script you wrote yesterday or sample data?

Thanks a million!

Quote:

> VBAN

> Try this macro.  I've included explanations, but if you have more questions,
> be sure to post back.

> Sub FindWOs()

> Dim sh As Worksheet
> Dim ListSh As Worksheet
> Dim ListRng As Range
> Dim cell As Range
> Dim FirstAddress As String
> Dim FoundCell As Range

> 'Set sheet which contains list of workorders
> Set ListSh = ThisWorkbook.Sheets("sheet1")

> 'Set range which contains work orders
> Set ListRng = ListSh.Range("A63", ListSh.Range("a63").End(xlDown))

> 'Loop through cells in range
> For Each cell In ListRng.Cells

>     'Loop through worksheets
>     For Each sh In ThisWorkbook.Worksheets

>         'Find the first cell that matches
>         Set FoundCell = sh.Cells.Find(cell.Value, , , xlWhole)

>         'If a match is found
>         If Not FoundCell Is Nothing Then

>             'Store the address of the match
>             FirstAddress = FoundCell.Address

>             'If it finds a match in the list, don't do anything
>             'otherwise bold the cell in the list and the match
>             If FoundCell.Address <> cell.Address Then
>                 cell.Font.Bold = True
>                 FoundCell.Font.Bold = True
>             End If

>             'Start a loop
>             Do

>                 'Find the next match
>                 Set FoundCell = sh.Cells.Find(cell.Value, FoundCell, ,
> xlWhole)

>                 'Same as above
>                 If FoundCell.Address <> cell.Address Then
>                     cell.Font.Bold = True
>                     FoundCell.Font.Bold = True
>                 End If

>             'Stop looping when it goes back to the first cell found
>             Loop Until FoundCell.Address = FirstAddress
>         End If
>     Next sh
> Next cell

> End Sub

> --
>{*filter*} Kusleika
> MVP - Excel
> www.dicks-clicks.com
> Post all replies to the newsgroup.



> > I am new with VBA scripts for Excel and I have a problem that I would
> > love to get some help with. I have a report with several tabs. On one
> > of the tabs I will have Workorders pasted in manually from a different
> > report. This is not in the beginning of the page but on lets say row
> > 63. There are not always the same amount of workorders needed so
> > sometimes I will need to have it run to row 70 other times 73 etc so I
> > know that I need to do a loop that "sense" the last value. I need get
> > the values and check to see if they are anywhere else on the report
> > and if they are I need it to be bolded both the workorder I pasted in
> > as well as all the places on the report (can be more then one
> > occurance as well as different sheets). All help a newbie can get is
> > very appreciated! Thanks!



Mon, 05 Dec 2005 01:21:23 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Finding values in a Table with Public Function?

2. Finding Value of senders email address

3. Finding Value in Excel Range

4. Text files finding value

5. Find value in worksheet

6. Find value i Excel range

7. find value using screen co-ordinates

8. Finding values of WinAPI constants

9. how to find value of <option> selected?

10. Finding the number value of a hex value

11. Finding the value of the field used as prmary key

12. Finding the value of a global variable in another dbase

 

 
Powered by phpBB® Forum Software