About Us

edluver

I am trying to write a macro for my job that will take account numbers from a
computer generated report and compair them with account numbers known to have
problems. IF working properly, the macro would take the first account number
on the "ActiveWorksheet" and compair it with the entire range of account
numbers on the "Hotlist" and "OCList" and format the entire corresponding
line on the "ActiveWorkSheet" if there is a "hit", then, move on to the next
account number on the unsorted "ActiveWorkSheet" and repeat the process until
complete. I am new at writing Macro's and thought this would be a fairly
straight forward, simple macro, but i have had a terrible time and very
little success. This is a copy of the code:

Code:

Sub Asi19_Formatting_Macro()
'
'Asi19_Formatting_Macro Macro
'Macro written 2/17/07 by Edward S. Lane
'
       
'Set Pointers
    Dim OrigRow As Integer
    Dim HotListAmount As Integer
    OrigRow = 2     'Where to start compairing numbers
    HotListAmount = 1   'Will be used to determine the amount of items in 
the hotlist and subsequent range (I'm assuming there will be at least one).
'Finds the amount of itmes in the Hotlist to be used as the range
    Do While IsNumeric(Cells(OrigRow, 2))
        Sheets("HotList").Select
        If IsNumeric(Cells(OrigRow, 2)) Then
            HotListAmount = OrigRow
        End If
        OrigRow = OrigRow + 1
        Sheets("HotList").Select
    Loop
    OrigRow = 2
'Compare OCList account numbers
    Sheets("ASI-19 ActiveSheet").Select
    Do While IsNumeric(Cells(OrigRow, 7))
        Sheets("ASI-19 ActiveSheet").Select
        If IsEmpty(Cells(OrigRow, 7)) = False Then
            If WorksheetFunction.Match(ActiveSheet.Range(OrigRow, 7), 
Worksheets("OCList").Range("D2:D3198").Select) Then
            Rows("OrigRow:OrigRow").Select
                With Selection.Interior
                     .ColorIndex = 6
                     .Pattern = xlSolid
                End With
            End If
        End If
        OrigRow = OrigRow + 1
        Sheets("ASI-19 ActiveSheet").Select
    Loop
End Sub

this is only for the "OCList" with a known range. The first set of code is
used to find the range of the "Hotlist" since it is dynamic and changes
daily. What am i doing wrong? I have gotten a wide range of errors while
working on this, but the most common of those has been a mysterious "400"
error with a big red X, and i have no idea what that means. I am sure i have
many issues, redundent code and so forth, but to say i am frustrated is to do
a disservice to the word. Any feedback and help with this would be GREATLY
appreciated. I have posted this once before, but it was deleted, so if you
replied to the previous one, i appologize and would like to thank you for
your audience and help.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
IF function that searches a range of cells for data and provide tr	offdah3z	Excel Worksheet Functions	2	August 13th 09 07:46 PM
Getting valid web searches and avoiding sites that contaminate web searches	David McRitchie	New Users to Excel	1	June 13th 06 03:00 AM
Need help -- a Macro that searches for string in a cell range?	graphicGuy	Excel Programming	1	April 18th 06 05:53 PM
which formula or function searches for a value in a range of cell.	Roccobarocco	Excel Worksheet Functions	5	December 3rd 04 12:06 PM
which formula or function searches for a value in a range of cell.	Roccobarocco	Excel Worksheet Functions	7	December 3rd 04 09:06 AM

Menu

About Us