Posts by Richie(UK)

    Re: Assign a macro


    As regards the question, I think you'll find it easier if you work with a workbook variable so that you can easily reference the appropriate workbook.

    Re: Find value in range copy nth cell value to another sheet


    Hi and welcome to the forum.


    Take a look at Offset. This will enable you to refer to a range by offsetting the desired number of rows and columns from an existing range. For example, the following refers to C2 by offset from A1:


    Code
    1. MsgBox Range("A1").Offset(1, 2).Address

    Re: On Error GoTo Issue, please help!


    Hi,


    You've already had some feedback regarding the general structure of your code so I'll just comment on the error-handling part.


    When an error arises it effectively flips a switch so that error mode is 'on'. Whilst in error mode any subsequent errors will not be handled (which is what you were experiencing). The way to switch it 'off' again lies in the use of Resume (or exiting the sub). It's also good practice to have your error-handling routines at the end of a sub.


    Compare the following:


    Re: User defined variables in a path reference


    Hi Matt,


    You could take a look at using the 'Indirect' worksheet function. It does, however, have a fairly significant drawback - it won't work as a link to closed workbooks.


    There have been various 'fixes' attempted by VBA coders over the years. For example, the 'Indirect.ext' function found within the Morefunc add-in and also a UDF called 'Pull' (I'm afraid I can't remember who wrote that. Anyone else remember?). However, I believe that these fixes have 'issues' with the most current version of Excel - ie they no longer work!


    You could, perhaps, try combing Indirect with the use of a VBA routine that would open all of the associated workbooks to update the data and then close them again. It's not very elegant but it may work if you don't have too many workbooks to work with.


    EDIT: Found a link that to a YouTube video that discusses the Pull function (it was written by Harlan Grove)
    http://www.youtube.com/watch?v=crbGfqEorXQ

    Re: Macros, Copy, Paste, Condition


    Hi and welcome to the forum.


    How about something like this:



    If you are new to VBA then a good tip is to try using the macro recorder to record the process that you want to code. It won't be exactly what you want but it is often a good starting point.

    Re: Problem with For each


    I agree with pike, UsedRange can be a little unpredictable at times. How about just defining the range in column A that you want to use. Something like this perhaps:


    Re: Viewing application.ontime scheduled events


    Hi,


    I'm guessing, based upon the first and last blocks of code within the BeforeClose event code that you posted, that this is a trimmed-down version of your code. Could it be that any of the other code is causing trouble (especially as you are driving the OnTime routine from a number of events and the BeforeClose event switches off EnableEvents)? Just a thought.


    The key with OnTime is passing the exact time in Ending it as was used to Start it. Could the ScheduledTime variable be being changed somewhere such that the time when switching off OnTime doesn't match the time when it was switched on?

    Re: look up value in another sheet by referencing a cell containing a sheet name


    Hi and welcome to the forum.


    If Sheet1 Cells D3 contains the text Sheet10, then to return the value from Cell A1 on Sheet10 you would enter:


    =INDIRECT("'" & D3 & "'!" &"A1")


    Note that the D3 cell reference is surrounded by a single quote (within double quotes) on the left and a single quote and exclamation mark (again within double quotes) on the right.

    Re: VBA Automate Unzip with SecureZip


    Hi and welcome to the forum.


    I know when I wrote a Winzip routine a while ago it was fairly straight-forward to modify the code for Winrar (see below). What code have you got and what have you tried so far?


    Re: VBA code for updating master workbook with data in raw file


    Hi VarunV


    Actually you are not using a worksheet variable - Sname is a Variant variable that you are using to hold details of the unique ID data. Try using Workbook variables for the workbooks that you are working with and Worksheet variables for the sheets within them. If I get time I'll re-work your routine to show what I mean but you should give it a go for yourself.


    In the meantime, the following should help to illustrate what I mean. (Note that I've used Activeworkbook in the illustration just for speed after downloading your example workbook - I would normally open it by assigning it to a workbook variable. For example, declare a variable like this : Dim wbkRaw As Workbook and then open the Raw workbook like this : Set wbkRaw = Workbooks.Open(Filename:=NewFN) - much easier to reference this way.)




    Re: Creating new new worksheet from from a list


    Hi and welcome to the forum.


    I haven't had time to test this nor to add all of the things that you want but it should help get you started.


    Re: VBA code for updating master workbook with data in raw file


    Hi again and thanks for amending the post.


    I'd suggest a couple of things that may help make things a little easier.


    1. Make use of a worksheet variable. It's much easier to make reference to the intended object that way.
    2. Try using simple functions to undertake tests like seeing whether a worksheet already exists.


    Combing the above might give us something like the code below. It makes use of a worksheet variable, wsNew, and a function that tests for the existence of a worksheet - in this case, if the sheet name doesn't already exist then a new sheet is added and given that name.

    Re: looking up files and deleting


    Hi John,


    You could try something like the code below. The code has not been tested - given that it involves the deletion of files I strongly suggest that you test it first on some dummy data in a dummy directory.


    Re: Status Bar


    Quote from Shums;620117

    Richie thanks for your response, but I am not VBA expert, I am just a beginner. ...


    In that case I would offer two bits of advice (just my opinion - you may see it differently):
    1. Don't blindly copy bits of code from multiple sources and then put it all together and expect it to work. Try to understand how the code works and how it can be used to achieve your objective.
    2. If you want to progress from being a beginner you need to try to improve. It doesn't matter if you don't get it right first time - we often learn by our mistakes - but if somebody points you in the right direction you should at least give it a go.

    Re: Look at cell value and split file


    Hey, you've made some good progress :)


    How about the following: