Posts by smuzoen

    Re: how to count text in no of Sheets

    I do not mean to be rude but I have no idea what you are talking about. Please supply a sample workbook with no sensitive data showing how your data appears and the result you expect. Please explain clearly what you require otherwise you have about a 0% chance of getting any meaningful answer.

    Re: Help to edit VBA code for saving data with merge cell

    You are using the Excel Evil of merged cells - they will cause you nothing but heartache and reduce simple ability to copy and paste data as you have discovered. You have 2 choices - redesign your workbook e.g. for Date Of Payment, Company Name use Textboxes then just use a single cell for Description and widen the cell. If you can avoid merged cells then always do so. They cause nothing but grief.
    If you choose not to redesign then you will need to copy values from CPV to GL essentially cell by cell using the first cell of the merged cells as the copy from location.
    Good design of a worksheet from the beginning will save you a whole lot of problems in the future.

    Re: Distribute hours over certain days of the week

    Perhaps a sample workbook showing your inputs and a sample result sheet - you will need to supply a sample workbook and a better explanation - what hours can be allocated on the three days, between what times - in general what you are describing is possible however you need to put this into some sort of context - sample workbooks with no sensitive data showing expected result sheet and how data is entered (as there are 2 variables - participants, no. of hours (function of participants) will vastly improve the chances of getting a meaningful helpful answer :)

    Re: combo box list - suggest entry based on characters entered

    If you create a combobox list and start typing then it will show automatically if there is a partial match
    e.g. List contains Australia - as soon as you type Aus it will populate with Australia
    If it contains Europe and England as soon as you type E will show either England or Europe (depending on order) then type "u" will show Europe - it basically does predictive text if value exists
    Am I missing your question?

    Re: Go to negative row and delete

    See attached workbook

    Re: Go to negative row and delete

    Can you explain this a little more clearly - is it find the negative value in a column and then delete the value in the same row but 4 columns to the left? So if E5 is a negative number, then delete A5? Is this from a particular starting cell or for all values in the worksheet - perhaps a dummy workbook with no sensitive data that explains more clearly what you require. A sample workbook and a good explanation and people will be more than happy to help - if we cannot understand what you want then often the question will go unanswered. :)

    Re: VBA Correlation Matrix with varying series length - nearly works

    I am with StephenR but for what it is worth could you hide the rows based on shortest column

    temp = Cells(Rows.Count, 1).End(xlUp).Row
    For k = 2 To Cells(1, Columns.Count).End(xlToLeft).Column
        lrow = Cells(Rows.Count, k).End(xlUp).Row
        If lrow < temp Then temp = lrow
    Rows(temp + 1 & ":" & Cells(Rows.Count, "A").End(xlUp).Row).EntireRow.Hidden = True

    Re: VBA Insert Formula Into Each Cell In Range

    Sub LoopMethod()
    Dim myRng As Range
    Dim ws As Worksheet
    Set ws = Worksheets("Team")
    Set myRng = ws.Range("C1").CurrentRegion
    For Each cell In myRng
        cell.Value = 100 - cell.Value
    End Sub


    Re: Add.Attachment not working

    What version of Excel are you using. In Excel 2007

    In 2010/2013

    Sub AddAttachment() 
     Dim myItem As Outlook.MailItem 
     Dim myAttachments As Outlook.Attachments 
     Set myItem = Application.CreateItem(olMailItem) 
     Set myAttachments = myItem.Attachments 
     myAttachments.Add "C:\Test.doc", _ 
     olByValue, 1, "Test" 
    End Sub

    Re: first in first out

    So 5 is left over with Column D - where does the 5 get put into the workbook - Into Returned? I understand why 5 is left over but where does that go? With Column E am I right that
    2 is in E4 so 1 is taken from E6, 1 from E9 which leaves 2 in E9. Then 4 in E8 takes the 2 from E9 and the 2 from E10 and you are left with 0? Where does the 0 get put into the workbook. I know that English is probably not your main language but I am struggling to work out what to do with the remainder values - the only reason I wrote the code is that I understand there is more to it than simple 18-13 = 5 (Sum From A to B minus Sum B to A). You are going to have to help me more to understand exactly what you need

    Re: Before Opening a File, Trap if access is not available

    I must admit I am not a Sharepoint guru so someone may have a solution for you however there is another way to perhaps attack this. I assume you are on a domain - you could use VBA to access a number of environmental variables. Is it only a particular group that should access to the excel file or better yet if the number of people who are allowed access to the file is small then you could let VBA handle the situation. Essentially place the users into an array and if the user is not part of that array then do not even allow them to access to try and check out the file. If it is a particular Group it could work, or if there is only a hand full of users that should access the file then you could code to check the user name and if the user is not in the array then terminate the code. Is that an option?

    That being said it probably does not solve the real problem of "Checking Out" the file. Just a thought for what it is worth. If it is worth exploring I could help you with code to access the number of environmental variables that VBA can access.

    Re: Graphing series on multiple graphs

    Perhaps something like

    Re: why Date &amp; Month are interchange ?

    The zip file has a form - it has .frm extension -Open your project, go into VBA editor and select File, Import - Browse to when you unzipped the files - Import it into your project (you will need both files in the zip file) - It will add the Calendar form to your project - I just offer it as an option

    Re: my Code is not working as expected