Posts by flee01

    I would like to know if there is anyway to take text that is in a cell & make vba use it as code. Here is the line I am using it in.

    MsgBox (Cells(Range("help_available").Row, Target.Column).Text)

    I need it to take the text in the cell that comes up and process it as code. The text will look something like this.

    "Here is the list of names:" & chr(13) & chr(13) & range(firstName)

    The msgbox from above is used over and over again in different situations. Also I want the flexiblity to put the code right into a hidden cell on the spreadsheet.

    Re: Module level variable

    Something like this:

    This code doesn't do all that I need it to for my application but contains the tent poles of my problem. I simply want to store the last value of cell in a public variable that is accessible after the sub finishes running.

    I would like to save a module level variable but do not know how to do it. I have tried using the public key word. If I put it in the sub, I get an error when it tries to compile the sub. If I put it outside of a sub, it just doesn't work. Surely this is something easy.

    Thanks in advance.

    I am in the middle of designing a macro that has 5 or 10 times ran away with itself causing me to sit & wait for it to finish. I am hoping there is a graceful way out of this without going to office application recovery. Any suggestions?

    Re: Parse multiple files for matching records.

    Here is the working code:
    Sub Import()
    ChDrive "C"
    ChDir "c:\temp"
    Dim MASWb As Workbook
    Dim MASSh As Worksheet
    Set MASWb = ThisWorkbook
    Set MASSh = MASWb.Sheets("sheet1")

    filess = Dir("*.xls")
    While Not filess = ""
    Workbooks.Open Filename:=filess
    Set currWB = Workbooks(filess).Sheets("sheet1")
    For Each ce In currWB.Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    If ce.Offset(0, 4) <> "DONE" And ce.Value <> "" Then
    Set findit = MASSh.Range("A:A").Find(what:=ce.Value)
    If Not ce Is Nothing Then
    findit.Offset(0, 1).Resize(1, 3).Value = ce.Offset(0, 1).Resize(1, 3).Value
    ce.Offset(0, 4).Value = "DONE"
    ce.Offset(0, 4).Value = "COULD NOT BE FOUND"
    End If
    End If
    Next ce
    Workbooks(filess).Close savechanges:=True
    filess = Dir()
    End Sub

    Thanks to acw for building 99% of the code.

    Re: Parse multiple files for matching records.

    It seems as if this is the problem:

    Quote from acw

    Workbooks.Open Filename:=filess
    For Each ce In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)

    It would seem as if range is in the context of the statement but the range object's parent object is still MASSh.

    Re: Parse multiple files for matching records.

    Amid great confusion I sat down to figure out to port this to my spreadsheet. While doing this I think I have found a problem in the code. You can most likely see it by removing the PO #'s in the two example order spreadsheets & then running the macro. No matter what is in those files it lists all as done. In other words findit & ce are the exact same thing. The findit operation is referring to itself. As long as there is a file in the directory you are pointed at it will mark all orders as done. It does not matter what is in the files found. Help...

    I need to use a macro to import data from an unknown # of order files to my master spreadsheet. My master spreadsheet & my order files contain a unique po number that can be used to find matching records. When the macro is ran & a match is found it needs to import the all data that to the master spreadsheet & updated the "processed" column for the record found. Also, all the lines in the unprocessed order files should be matched up. If a record is not matched, a warning needs to be displayed. If the record has already been processed, it just needs to be skipped.

    Attached is an example master spreadsheet & an example unprocessed orders spreadsheet. If at all possible, please split the unprocessed orders into separate files when testing the final product. The part that I will struggle with the most is looping thru separate files.