Posts by lasw10

    Re: find next loop VBA

    ok shame...

    Not particularly clean or watertight but should give you the general idea...

    Re: populating one worksheet with another

    as for your other code - can you explain what you're trying to do with your concatenate macro - we can see why it's not working - but can't fix without knowing what you're trying to do exactly... specify ranges etc as much as possible (including specific cells)

    Re: populating one worksheet with another

    ok well for starters...

    to mimic one sheet to another (without the need for VBA) just activate the "master" sheet and then hold down CTRL and click on the "slave" sheet - both will now be highlighted - they are now "Grouped" so whatever you enter on one will be entered on the other.

    unfortunately the grouping does not stay constant...

    so if you want a VBA solution - use a workbook sheet change event along the lines of

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim s1 As String    'MASTER WORKSHEET NAME
    Dim s2 As String    'SLAVE WORKSHEET NAME
    s1 = "MASTER"
    s2 = "SLAVE"
    If UCase(Sh.Name) = s1 Then
        Sheets(s2).Cells(Target.Row, Target.Column) = Target.Value
    End If
    End Sub

    if the name of the sheet that is changed matches your criteria as being the master sheet it will just copy the cell that's changed to the identical cell location on your designated other worksheet.

    Re: find next loop VBA

    not an answer I am afraid - more a question...

    where are you gettign this data from? is it a download from a database etc? If it is, you will find it easier to tweak the code returning the data direct from the db rather than in XL - you should never aim to have 30,000 rows of data in a spreadsheet.... it's not an efficient db tool for this kind of thing unfortunately.

    let us know if it is db related - and if so - what db and if you have the code used to retrieve it post that too...

    in the meantime I am sure someone will give you an XL VBA answer sooner rather than later...

    Re: Adding and subtracting in cells - without iteration

    I am not entirely sure I follow what you're trying to do but how about this:

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name <> "Sheet1" Then Exit Sub
    If Target.Column <> 1 Then Exit Sub
    If IsNumeric(Target.Value) Then
        Target.Offset(0, 5) = Target.Offset(0, 5) + Target.Value
    End If
    End Sub

    This would be a sheet change event (ie every time a cell is changed)

    Will exit if not cell changed not say in Column A of Sheet1... if it is it changes corresponding F value to equal original F value + value of cell in A that's changed (if it's numeric).

    This code would sit in "ThisWorkbook" module in VBA

    Re: Daily Email job

    as a general rule I'd suggest avoiding using object libraries as much as possible (unless you really need to) purely because of binding issues ... there are ways to bind libraries automatically (often using the GUID) which can get around the problem of handling multiple operating systems but for what you're trying to achieve I'd say this is overkill

    An ex. problem would be if you manually bind the Outlook library to your file say on XP and then distributed it to say someone running an archaic OS (like NT4 or Me etc...) - the library you have issued would be vn 10.0 whereas that version would not be available to the other user and your code would debug as soon as the file was opened... (note the reverse is not an issue - ie distributing version 9 to a user where only 10 is available would not cause a prob as XL would simply switch to 10... backwards compatability doesn't work in this instance).

    As i said - using the GUID with major/minor version references gets around this but for a simple email routine it's just not worthwhile... I tend to only use it when distributing files heavily reliant on ADO etc...

    An ex. of emailing without requiring the OL library itself be referenced can be found at the below URL - although in this instance we're using HTML format - the basic premise of creating the OL object and generating the mail (see all e. variables) is what you want.

    If anyone thinks the above assumptions are incorrect please feel free to correct me - but in my experience - this is the best approach.

    Re: remove all non alpha characters in sql


    I can't think of any *quick* way of doing this given the chars to be removed are variable... you know that any char < 65 or > 122 is non-alpha but I can't think of a quick way to get this into a SQL statement I'm afraid... i guess I'd do the stripping elsewhere (ie wherever you're returning the recordset?)

    Re: Formula Help Required

    or if you prefer - a little vba function

    Function txtstr(win As String, lin As String, aix As String)
    txtstr = ""
    If win = "x" Then txtstr = txtstr & "Windows; "
    If lin = "x" Then txtstr = txtstr & "Linux; "
    If aix = "x" Then txtstr = txtstr & "AIX"
    End Function

    so d1 then =txtstr(a1,b1,c1)

    would return appropriate string given values of a, b & c1

    Re: remove all non alpha characters in sql

    do you mean remove chars from an actual statement or do you mean use a statement to remove chars from the resulting string of a statement??

    i don't know of any function that removes all non-alpha chars - the only way I know would be to use a replace function and in truth that's not going to be very efficient.

    Which db are you using and where are you returning the resulting string to? ie if in Access you may find it easier to build a function to do this (iterating the resulting string char by char) - ie an adaption of XL's CLEAN function but for all chars with an id > x...

    Re: Save Emails

    if you have some code already please post it up so we can see how you're generating your mails...

    if you save a message - then by default it will go to your draft folder...

    Below code is an HTML email generator but it should give you a general idea of how it's done in XL VBA...


    the mail object is obviously e ... setting to Save rather than Send will post to your draft folder rather than actual mailing it.

    Re: Button Size as s Cell Size?

    Here's a quick bit of code which resizes a control according to the dimensions of a cell you refer to ...

    Should point you in the right direction

    Re: Opening xl Hangs My PC - Critical Memory

    Hmm, I can't recall the exact folder location on Win98 but try and find the folder within your profile entitled Olk.... (something along those lines) - may be within the WinNT folder if there's one on 98

    MS Office sometimes caches your XL files (full size) and if you don't clear that it can cause *issues*

    I will root around - think I posted something on Mr Excel a whilst back on this (under lasw10 pseudonym).

    Re: Notify new data has been added

    to give an example of what I mean for point 3...

    the bat file itself would look something like this - (to generate a message box on your desktop whenever a record was added)

    net send NAMEOFYOURMACHINE "Record Added to Database"

    This is the ASP code (found this at Experts Exchange re: calling a .bat file from asp page... untested... obviously the path would need to be changed to reflect it's correct lcoation) ...
    You would put this code immediately after the code that amends the when the update db command runs in asp it then runs this to generate the message to you...

    Set oWSH= Server.CreateObject("WScript.Shell")
    oWSH.Run "c:\\netsend.bat", 1, True
    set oWSH = nothing

    This is all untested, an email solution would still be best but should never write off the simpler things in life...

    Re: Notify new data has been added

    OK my points would be as follows:

    1. If this is a work thing then you should be able to get access to your mail server from your IT dept - ie just get them to tell you the address of the server.
    Your options then are to either hardcode the mailhost address into your asp page (using something like ASPmail - I've not coded in ASP for a while but I will have some code lying around somewhere... I've been converted to the .Net suite) - using some reliable 3rd party mail object model means it will queue properly etc should the mail server become unavailable.. OR alternativerly use localhost but configure IIS localhost to use a smarthost to your given mailserver (from IT) - this is maybe the cleaner option.

    2. If you didn't necessarily need to receive an email when someone posted to your db you could simply i) create a new table in your .mdb which logs the db activity or ii) add a timestamp field to your current db table - so when the record is added you have a timestamp as to when it was added. You could then build another .asp page which retrieves the log and shows when records have been added - you could use a meta refresh (not great but workable) to constantly refresh the page all day for you.

    3. This one's out of leftfield.... you could create a .bat file that utilises NetSend in DOS and run it from your asp page when the .mdb is written to... netsend is used for popping up messages on your machine on a network (like an IT announcement when email is down). Have to confess that I've never tried this from a web page but my guess is it's not too difficult. To do this you would just need to know your machine's name so it can be called in the netsend.

    Or we can look into using VB in MS Access referencing OL to send you a message but I don't have MS Access on my machine anymore (blast!)

    We will actually answer your question soon.... I promise!

    Re: Loop does not work well

    the problem you're having is that you're iterating and specifying a range that will actually change once a row is deleted... ie if you delete a6 - a7 becomes a6 - but your code jumps to a7 instead of checking a6 again.... you could put in a "goto" instead of "next c" in these instances but you will still end up cycling through lots of unnecessary cells.