Posts by mjschukas

Re: .find method on a fitlered range

basically, i have a "master" list (update infrequently) in one sheet...

i have a promo list in another sheet (frequent updates-doesn't contain all master info.)...

so, i use the .find from the items in the promo list to search the "master" and retain info. to add to the promo...
(works well without the filtering)

make sense?

thank you.

the .find method is working well (code below), but when i add a fitler to the search range (below, column aa), i seem to get "funky" results (works 90% plus of the time and then i'll get a wrong return, or i have to search a second time to get the correct results...).

-i've wondered if sometimes the previous search doesn't get cleared?-i'm still testing...

???

'...
 For Each c In Worksheets("PT").Range("d8:D" & LastRowPC).Cells 'brand
   ' strSearch = c.Value & c.Offset(0, 1).Value            
 strSearch = usfP.cmbMU & usfP.cmdState & c.Value & c.Offset(0, 1).Value
   Set aCell = oshtsp.Range("aa1:aa" & LastRowsp).Find(What:=strSearch, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False, After:=oshtsp.Range("aa" & LastRowsp))
 '...

thank you.

Re: user form goes "blank" inside its borders

in further testing, i still loose the contents of the userform (seems the longer the code runs-based on a fitler, etc.

here's the code i added:
'...

Application.Calculation = xlCalculationManual    'manual calc
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.StatusBar = "Building..."
DoEvents
' Create a variable to hold number of Visual Basic forms loaded
' and visible.
Dim I, OpenForms
For I = 1 To 150000    ' Start loop.
    If I Mod 1000 = 0 Then     ' If loop has repeated 1000 times.
        OpenForms = DoEvents    ' Yield to operating system.
    End If
Next I    ' Increment loop counter.


dim strFirstAddress As String
Dim rngPicked As Range
'...

thank you

Re: user form goes "blank" inside its borders

thank you...

doevents is testing well...

thank you.

sometimes when i run code under a command button on a pop-up user form, the contents of the userform go "blank"
(i.e., border stays on, so you can see that the form is still there, but the contents are all "white"-you cannot read/see any of the content...

?what could be done to prevent this from happening (presently, i just have the mouse cursur displaying while code is running?

thank you.

Re: using the .find method on filter range

thank you...

happy to do so (and will work on ASAP)...

in the meantime, is there any inherit reason (in the object model) to the .find method that would suggest not using against a filtered range (or can we assume it's somewhere in my coding...)...?

i've been reading:

http://msdn.microsoft.com/en-us/library/office/ff839746.aspx
http://www.ozgrid.com/VBA/find-method.htm
etc.

thank you.

i'm using the .find method against a range on another worksheet (works well)...

but when i filter the range first (e.g., only give me the country of USA), my results are not 100% correct (sometimes i have to run the .find code a second time and then it returns correctly...???)...

?does this make sense?
--maybe i should not be using the .find method against a filtered range?
(i have tried running the find against the entire worksheet and then against a dynamic range name-adusting after the filter)

???

thank you.

Re: SaveAs Extension In 2007

ok...sounds straight forward...(i have just not done in 2007 yet...)

i will try asap...

thank you..!

i've been using the following lines of code successfully in 2003, we began porting to 2007 and i get an error on the .saveas replace method and function line...?

Set fs = CreateObject("Scripting.filesystemobject")
        If fs.fileexists(FileName) = False Then
            Atmt.SaveAsFile FileName
            Set objexcel = CreateObject("Excel.Application")
            Set objws = objexcel.Workbooks.Open(FileName)
                  'error next line...
            objws.SaveAs Replace(FileName, ".xls", ".txt"), xlTextWindows
            i = i + 1
            Item.Delete
        End If

(n.b., error on the line: objws.SaveAs replace...)

n.b., this code runs under a form in outlook 2007, but i'm thinking that i'm invoking Excel in the code...

???

thank you.

i've been asked if the Excel is ISO compliant...?

???

thank you...

Re: Suppress: Cannot Copy a Compressed (Zipped) Folder Onto Itself Message

oh...very interesting...

i'll post the code later...and can i surpress this...?

thank you.

Re: Suppress: Cannot Copy a Compressed (Zipped) Folder Onto Itself Message

thank you....

but i still get the error message (but the code work well-i would just like to surpress the error message and continue the code...)

thanks.

Re: Suppress: Cannot Copy a Compressed (Zipped) Folder Onto Itself Message

thank you...

i have tried this line of code (above and below)...

but i still get the error message...

???

thank you (and a merry xmas!)

i get the error messagebox:
title bar " compressed (zipped) folders error"
"cannot copy a compressed (zipped) folder onto itself"
with only the OK button

when i run

oapp.namespace(filenamezip).copyhere oapp.namespace(foldername).items

?i would like to surpress this message (that is continue...)...
???

thank you...

Re: Graph Tangent Function

thank you...

i think the problem is when excel evaluates the points tan(pi/2) and tan(3pi/2)...?
(it should be undefined and not graph...)

???

thank you.

graphing trigonometric functions...

sin and cos seem to work easily, but i'm having trouble getting the tangent function to graph (look) correctly...?

???

thank you

Re: Mod Function

works like a "charm"...!

thank you...

i'm looking to determine if a number is prime or not...

i thought of testing with the mod function in an adjacent cell...but i'm not sure how to do...?

???

thank you.

Re: Compress Worksheet File...

no...windows xp with excel 2003...

thanks.

Re: Compress Worksheet File...

here's my code:

‘error (compressed (zipped) folders error)
‘Error Warning “cannot copy a compressed file unto itself
‘oApp.Namespace(FileNameZip).CopyHere oApp.Namespace(FolderName).items


Private Sub CommandButton4_Click()
Dim k As Integer
Dim strng, path, filename As String
Dim wkb As Workbook
Dim ws As Worksheet
path = "c:\docume~1\nestors\salesm~1\spread~1\masterList\"
filenameI = path & "william_Order_Form.xls"
Workbooks.Open filename:=filenameI
Application.Run "PERSONAL.XLS!mac_Automate_SetUp"
On Error Resume Next
Dim FileNameZip, FolderName
Dim strDate As String, DefPath As String
Dim oApp As Object
    DefPath = "c:\temp\salesmanMDB\will\"
    If Right(DefPath, 1) <> "\" Then
        DefPath = DefPath & "\"
    End If
FolderName = "c:\temp\salesmanMDB\will\" '<< Change
FileNameZip = DefPath & "will_CustomerItemHistory" & ".zip"
    'Create empty Zip File
    NewZip (FileNameZip)
    Set oApp = CreateObject("Shell.Application")
    'Copy the files to the compressed folder
    On Error Resume Next
    'error below...
    oApp.Namespace(FileNameZip).CopyHere oApp.Namespace(FolderName).items
    On Error Resume Next
oApp.Namespace(FolderName).items.Count
    Set oApp = Nothing
End Sub
Sub NewZip(sPath)
On Error Resume Next
'Create empty Zip File
'Changed by keepITcool Dec-12-2005
    If Len(Dir(sPath)) > 0 Then Kill sPath
    Open sPath For Output As #1
    Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
    Close #1
End Sub

thanks...