Posts by flee01

Re: Use text in cell as code.

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

Re: Use text in cell as code.

That definetly display's the text. I need it to process the text that I put in the cells as code & not just text.

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

Mainly, I just want to learn how to use them to so that I don't have to put that sort of thing on the spreadsheet or in my form.

Re: Module level variable

Well, that is what I thought. I will keep plugging away at it.

Thanks.

Re: Module level variable

Something like this:

public myVar


sub mySub(myText)


for each cell in myRange
if cell.value like "*" & myText & "*" then
    pull_data_to_form
    exit for
end if


myVar = cell.row
next cell


end sub

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.

Re: VBA to let Internet Explorer quit

Doesn't look like there is an easy solution for this. Here is an example of the fix.

http://www.developersdex.com/vb/message.asp?p=2677&r=4461452

Re: Module level variable

bump

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.

Re: Breaking out of run away code

Thanks 4DNFO!

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:
[vba]
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"
Else
ce.Offset(0, 4).Value = "COULD NOT BE FOUND"
End If
End If
Next ce
Workbooks(filess).Close savechanges:=True
filess = Dir()
Wend
End Sub
[/vba]

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 workbooks.open 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...

Re: &lt;&gt; operator

Thanks.

What does this operator do?

Re: Parse multiple files for matching records.

I haven't actually used it yet, but that totally rocks. Thank you so much for your help. After I implement it I will repost with how it worked. That should be sometime today or monday.

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.

Re: Select text inside text box on VBA form

Thank you. That is exactly what I need.

Re: Select text inside text box on VBA form

Bump