complex lookup vba solution

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hi there,,
    Here is what I'd like to do.. I have a report that is full of garble we'll call it report.xls . It's like 30,000 rows long..


    I'd like a vba solution in a new workbook w/ 3 rows
    Dept - Salary - Benefits


    In the dept column I'd like to look in report.xls and everytime it finds the word "Department Totals" to move right 3 columns and up 6 rows and pull that number. First placing the result in A2, then moving down to A3 and placing the next result, the A4, etc.. etc.. There are a ton of different department #'s, but they're always in the same position... I just want it to search for all instances until the end of the worksheet. Once that's figured out I'm sure I can apply the same vba to the other 2 columns (salary and benefits). Thanks for any help offered!
    If anyone would like a sample of the excel file, please let me know. Thanks!

    Hi jjst34,


    The Find example from the VBA help file is a great starting point.


    Code
    With Worksheets(1).Range("a1:a500")
    Set c = .Find(2, lookin:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Interior.Pattern = xlPatternGray50
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With


    If this still leaves you confused then post an example workbook.

    [h4]Cheers
    Andy    [/h4]

    Following up on Andy's suggestion, here's some code to try. Change UsedRange to the appropriate column or range to search.
    [vba]
    Sub GetStuff()
    Dim firstfound As String, f As Range, t As Worksheet, k As Long, r As Range
    Const s As String = "Department Totals"
    Set f = Workbooks("Report.xls").Sheets(1).UsedRange
    Set t = ThisWorkbook.Sheets(1)
    k = 1
    On Error Resume Next
    Set r = f.Find(what:=s, LookIn:=xlValues, lookat:=xlWhole)
    On Error GoTo 0
    If r Is Nothing Then
    MsgBox s & " not found.", vbOKOnly
    Exit Sub
    End If
    firstfound = r.Address
    Do
    k = k + 1
    t.Cells(k, 1).Value = r.Offset(-6, 3).Value
    Set r = f.FindNext(r)
    Loop Until r.Address = firstfound
    End Sub
    [/vba]

    Derk,, your's worked like a charm.. Thanks so much for the help!


    Andy,, I attempted yours for a second, and I got a "variable not defined" error? I would bug you to correct, but I can always use Derk's info.. Unless you want to fix it up, I think I'm all set.. I do appreciate your reply also!

    Derk,,,
    I do appreciate the help..
    Any chance you could "translate" some of this thing for me on a line by line basis, so i can use it in the future and know what each line means?


    I understand some of it, but not quite how it all works.

    There is not much to explain once you know the meaning of each of the terms. The code does exactly what you requested in the oprder you requested it. To look up the meaning of a term you don't understand, put your cursor on the term and then push the F1 function key. VBA help will then explain it's meaning and often provide an example of it's use.

    Derk,,
    wondering if you can help me w/ another thing on this... The code u gave me worked excellent for what I needed.. Now I need something from the same report, but a little more complex, and I adapted your code for part of it.. but i'm stuck on something..
    I need to fill in the following columns on an excel spreadsheet..
    Percentage - Name - Fund - Org - Salary - Benefits - Total



    Now I can get everything done w/ your code from earlier, except for the percentage part because sometimes 100% of the salary is expensed to a particular fund, but on some there are several % splits, but the name is only listed once.. Anyways,, I know that explanation is garbage so I attached a spreadsheet showing a sampling of the report I'm extracting the info from.. And my desired outcome on sheet 2... If you could take a look at it and see what the possibilities are, I'd really appreciate it.

    Try the following code. It seemed to work on your sample. I didn't bother with formatting and I prepositioned the headers on sheet3.
    [vba]
    Sub ProcessData()
    Dim i As Long, j As Long, k As Long, first As String, f As Worksheet, t As Worksheet, _
    r As Range, x As Range, s As Integer
    Set f = Sheets("sample of report")
    f.Activate
    Set t = Sheets("Sheet3")
    t.UsedRange.Offset(1, 0).Clear
    Set r = f.Columns(1)
    f.Cells(1, 1).Select
    Set x = r.Find(what:="Percent", LookIn:=xlValues, lookat:=xlWhole)
    If x Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    first = x.Address
    k = 1
    Do
    k = k + 1
    i = x.End(xlUp).Row
    t.Cells(k, 2) = Cells(i, 1)
    i = x.Row + 1
    s = Cells(i, 1)
    t.Cells(k, 1) = s
    t.Cells(k, 3) = Cells(i, 3)
    t.Cells(k, 4) = Cells(i, 5)
    t.Cells(k, 7) = Cells(i - 2, 8)
    While s < 100
    i = i + 1
    k = k + 1
    t.Cells(k, 1) = Cells(i, 1)
    s = s + Cells(i, 1)
    t.Cells(k, 2) = t.Cells(k - 1, 2)
    t.Cells(k, 3) = Cells(i, 3)
    t.Cells(k, 4) = Cells(i, 5)
    t.Cells(k, 7) = t.Cells(k - 1, 7)
    Wend
    Set x = r.FindNext(x)
    Loop Until x.Address = first
    t.Activate
    Application.ScreenUpdating = True
    End Sub
    [/vba]

    Derk... YOU ARE THE MAN!


    where on earth can I learn this stuff to the extent you have? Any specific books? Or is this classroom training? Self training? etc... this is wonderful! I appreciate it so much!

    You ask how to learn VBA. The main way is by doing it day after day. However, there are some good aids for speeding up the learning process. The macro recorder and VBA help are invaluable. Record something, then look up each term in VBA help (put the cursor on a term and hit the F1 function key). If you haven't gotten it already, I recommend John Walkenbach's Power Programming with VBA as an excellent beginning tutorial. Also read all of the archived copies of the OzGrid newsletters. Happy learning!

    I have signed up for the newsletter and will also take a look at the book you've recommended..


    I do have one more question regarding this project... I am getting an error on your code when I run it w/ the full report. It's on the "s = Cells(i, 1)" part. It's due to my formatting of the report that I do. Before I put in all of your code, I have a piece of code to format the report. It goes like this..


    Workbooks.OpenText FileName:= _
    "C:\Documents and Settings\jwetmore\Desktop\report.txt", Origin:=xlWindows, _
    StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(7, 1), Array(19 _
    , 1), Array(24, 1), Array(25, 1), Array(29, 1), Array(80, 1), Array(114, 1), Array(129, 1))
    Application.ScreenUpdating = False
    On Error GoTo done
    Do While 1 < 1000
    Cells(1, 8).EntireColumn.Find("Page", LookIn:=xlWhole, lookat:=xlWhole, matchcontrolcharacters:=False).Select
    Range(ActiveCell(), ActiveCell.Offset(7, 0)).Select
    Selection.EntireRow.Delete
    'a = 1 'this is just a bogus variable
    Loop
    done:




    ActiveWorkbook.SaveAs FileName:= _
    "C:\Documents and Settings\jwetmore\Desktop\report.xls", FileFormat:= _
    xlExcel9795, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
    False, CreateBackup:=False
    Range("A1").Select
    Application.ScreenUpdating = True
    End Sub



    now when that's all done, depending on the page breaks occasionally I end up w/ 2 rows like this:


    Percent Project
    Percent Project


    How would I run something that that does a find for "Percent" and if the row below it also says percent to delete the TOP one? I think this should solve the error i'm getting. Thanks!
    ps i'm working on it,, but i keep getting stuck in a loop! LOL I think it needs to be done from the bottom up also right? again thanks for the help!

    Try the following modification to delete the unwanted rows if they exist.
    [vba]Sub ProcessData()
    Dim i As Long, j As Long, k As Long, first As String, f As Worksheet, t As Worksheet, _
    r As Range, x As Range, s As Integer
    Set f = Sheets("sample of report")
    f.Activate
    Set t = Sheets("Sheet3")
    t.UsedRange.Offset(1, 0).Clear
    Set r = f.Columns(1)
    f.Cells(1, 1).Select
    Set x = r.Find(what:="Percent", LookIn:=xlValues, lookat:=xlWhole)
    If x Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    first = x.Address
    k = 1
    Do
    If x.Offset(1, 0) = "Percent" Then
    If x.Address = first Then first = x.Offset(1, 0).Address
    Set x = x.Offset(1, 0)
    x.Offset(-1, 0).EntireRow.Delete
    End If
    k = k + 1
    i = x.End(xlUp).Row
    t.Cells(k, 2) = Cells(i, 1)
    i = x.Row + 1
    s = Cells(i, 1)
    t.Cells(k, 1) = s
    t.Cells(k, 3) = Cells(i, 3)
    t.Cells(k, 4) = Cells(i, 5)
    t.Cells(k, 7) = Cells(i - 2, 8)
    While s < 100
    i = i + 1
    k = k + 1
    t.Cells(k, 1) = Cells(i, 1)
    s = s + Cells(i, 1)
    t.Cells(k, 2) = t.Cells(k - 1, 2)
    t.Cells(k, 3) = Cells(i, 3)
    t.Cells(k, 4) = Cells(i, 5)
    t.Cells(k, 7) = t.Cells(k - 1, 7)
    Wend
    Set x = r.FindNext(x)
    Loop Until x.Address = first
    t.Activate
    Application.ScreenUpdating = True
    End Sub
    [/vba]

    Thanks again... It's now running all the way through... now time to confirm some #'s.. I really appreciate your help w/ this one! :)

    Re: complex lookup vba solution


    Hi Derk,,
    several weeks ago you helped me out w/ this code.. It is working awesome! I confired all the #'s.. and they were off only about $200 on a $90 million report.
    I figured out the small problem and its kind of weird.. when the code pulls the figures over, and reads the percentages, for some reason if the percentage is 67.5 it rounds up to 68 and there were some that were 33.34 and it rounded down to 33. There are others like the 66.66 that don't round and work right.. why is it that only some would round and others dont? clearly I don't want any rounding.. any ideas? Thanks!

    Re: complex lookup vba solution


    The code assumed percents were going to be integers. Since that is a bad assumption, just change the type of s in the dimension statement at the top of the module.


    Instead of
    s As Integer


    make it
    s as Double

    Re: complex lookup vba solution


    well that was a nice easy fix.. appreciate it... that project is complete and working like a charm.. thanks for all your help!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login