Posts by Timbo

    I have two sums as follows -



    Sometimes the area they are looking up has no figures in it at all and if that is the case I want the sums to return a zero. Could someone help me achieve that please.


    Re: Multiple Criteria Sum With Dynamic Ranges

    Thanks Richard & Kris,

    What you have given me works for one column, what about if I want to copy the calculation across 100 columns?

    This defined data won't work because it doesn't refer to Column L and I can't set up 100 defined ranges.


    kCol =DATA_STORE2!$K$4:INDEX(DATA_STORE2!$K:$K,LastRow)

    If there a way of changing the calculation so that kcol will refer to lCol when copied one row to the right?

    Re: Use Indirect With A Named Range

    Hi Kris,

    Good to hear from you again, I have missed your expertise on the forum.

    Tim.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Hi Kris,

    Good to hear from you again, I have missed your expertise on the forum.

    Unfortunately it doesn't work, it just returns 0.

    The full sum is this - {=SUM(IF(DATA_STORE2!$I$4:$I$500=$A4,IF(DATA_STORE2!$J$4:$J$500="I", IF(DATA_STORE2!$DH$4:$DH$500>99,DATA_STORE2!$K$4:$K$500))))/$CZ4}

    You may remember it you helped me with it.

    500 is currently last row of the worksheet but the sheet keeps having data added to it. and I have to keep changing the 500 to say 510 then 520 etc.

    The first worksheet in the workbook called DAILY_INPUT has this sum in it =COUNTA(A4:A505)+3 to count the number of rows with data in.

    I thought that if I could use indirect to pick up this number the worksheet would always pick up all of the data on the worksheet with no changes required.


    I have a sum part of which is $I$4:$I$500.

    The 500 part of the sum is constantly being manually changed using edit replace because of a growing amount of data.

    In cell A1 ona another worksheet within the workbook I use the Count function to count the number of rows containing data. This cell is named DataCols

    Is it possible to combine $I$ with the named Range DataCols?

    I have got this far - ="$I$"&INDIRECT("ColNo") which returns 500 if I remove "&I$"& in front of it.


    The following calculation works fine in every column but this one (COLUMN J).

    =SUMPRODUCT(($A$7:$A$66="Media - INSERT")*($C$7:$C$66="Y")*(J$7:J$66))

    Column J picks up data from the main frame and either returns a number or #N/A.

    As a consequence I have a mix of numbers and #N/A's in the column and the sumproduct is just returning #N/A.

    Can anyone suggest a way to overcome this please?

    I have tried IF(ISNA(SUMPRODUCT( but I am not sure where to put the ,"" part of the calculation or whether there ought to be an extra part to the calculation i.e. *(J$7:J$66<>#N/A)

    Many thanks.

    Re: Run-Time Error Setting Worksheet Variable

    Sorry for the delay in getting back to all of you but a bit of a crisis situation here meant that I had to leave the project and come back to it.

    Dave I am sorry I seem to offend you with my posts.

    rbrhodes thanks for your macro it worked and highlighted the error.

    Will your theory was right, the name picked up from the Membership Book is Blake's and the Worksheet is called BLAKES no apostrophe.

    Is there a way that I can rectify that without changing the Sheet Name, after I get to this point?

    strWks = .Offset(0, 9).Value

    I have tried -

    If strWks = "BLAKE'S" Then
    Wks2 = "BLAKES"
    End If

    but it doesn't seem to work.

    The sheets are linked through into Word using Mail Merge and to change the Sheet Name and redo all of that would take hours.

    This macro is falling over at the last hurdle and I just can't work out what is wrong.

    The user is asked for one piece of information "Enter the ID Number.

    What the macro should do then is go to Wks1 find the ID Number and change some cells as a result. This bit works.

    It works fine until it reaches this point

    Set Wks2 = Worksheets(strWks)

    7 rows from the bottom.

    My intention was to capture the the name of another worksheet which is held on the same row as the ID Number on Wks1 and call it strWks.

    Then further down the macro set the value of Wks2 to that of strWks so that the macro will then go to that sheet and remove data from the row with the same ID Number.

    I get a Time Run Error 9.

    It just seems to be the bit at the bottom where I am trying to identify Wks2 using strWks.

    All help much appreciated.

    Re: Userform Basics

    Hi Roy,

    I had limited time last week but I have taken one of the examples on the page that Kris suggested last week and I have worked out how to add more boxes for data to be typed into and how to get the tab order right.

    I want the data that goes into these boxes to direct a new macro (not written yet) to certain directories, open specific files and rename specific them.

    So my next step will be to work out how to link the Userform to the macro. One of the pieces of data I am capturing is a filename CFINS1 an example of how a macro captures that one piece of information from the Userform and open the file would probably get me started on the next stage.

    Many thanks.

    I have been looking into refining the way a couple of big macros that I wrote several years ago work and have decided that instead of loads of inputboxes that a Userform might be the way to go.

    I have never used them, don't know how to link code to the objects to make them work and can on make a guess as to the right way to start going about this.

    Can anyone suggest a sort of Userforms for Dummies site where I could pick up the basics?

    I am going looking toward having a box come up where the user can enter about 8 pieces of data and based on that data the macro will then go off and open specific files anc change specific data.

    Any help appreciated.

    This piece of code successfully replaces a module in a range of workbooks -

    Is there a way to change the code so that I can slot it into another sub-routine which I use to open a large range of workbooks and make specific changes so that the sub routine works on the Activeworkbook?

    ie. vWbkNames = ActiveWorkbook.Name

    I have tried this but the For lLoop code further down the macro doesn't like it. I asume because there isn't an array therefore the loop isn't needed.

    I have commented out the For lLoop piece of code and the macro runs but the module isn't copied across.

    Re: Remove Vba From Worksheet

    Hi Andy.

    I have changed Thisworkbook to Activeworkbook and this works fine does it look okay?

    Re: Remove Vba From Worksheet

    Hi Andy,

    In break mode the Macro runs down to the last line and then I get a message "Can't enter break mode at this time. With options Continue End Debug or Help.

    Continue lets the macro finish and removes the code from the worksheet.

    If I run the macro with no code in the worksheet it doesn't stop all.

    Is there anyway to overcome this and can I run it from personal.xls instead of from the workbook with the code in it?

    Re: Remove Vba From Worksheet

    Hi Andy,

    I have tried this

    Set objVBCode = objVBProject.VBComponents("Sheet1").CodeModule

    Is it because Sheet1 (CAMOB) is a worksheet and not a CodeModule? Can In replace CodeModule with something that will pick up the code in the worksheet?

    Re: Remove Vba From Worksheet

    Where do I put the quotes to make this work? do I split the sheet and the worksheet name like this or is there a way to combine them?

    Set objVBCode = objVBProject.VBComponents("Sheet1")("CAMOB").CodeModule

    Re: Remove Vba From Worksheet

    Hi Andy,

    Thanks for your help, your code has fixed that problem but now it stops further down the macro here -

    Set objVBCode = objVBProject.VBComponents("CAMOB").CodeModule

    I get a run time error 424 Object required

    Is it because the worksheet is called CAMOB but in the Project Window it appears as Sheet1 (CAMOB)?