Posts by REP013

    I'm so rusty and am hoping one of you guys can help me?

    I have a front sheet with a list of all the sheets in the workbook in column A.
    In column B I need to use the counta function to count the number of entries in the corresponding sheet to column A.

    As there are 70 sheets I'd thought I'd use a Macro but I'm having great difficulties. I've done a search but can't find what I'm looking for.

    So heres part of my code (it uses loops etc which I've managed but this just errors)

    cell.Offset(0, 1).FormulaR1C1 = "=counta(" & str2&" C[-1])-1"

    The str2 is the string name to reference the sheet in the formula (for example sheet1), I can't figure out how to enter this into the code so it works and is accepted.

    Any help, as ever, greatly appreciated.

    Re: Userform With List Box

    Firstly I hope you mean a combobox and not a listbox, assuming that you do then the following is true (I have continued to use the term listbox for ease)

    Within the listbox their should be a property called rowsource try using that

    e.g. Totals_Dropdowns!K2:K11

    The second question is split into 2 parts.


    To show the userform you need a macro, what is the userforms name?

    Assuming it is default (userform1)


    This will show the form

    The second part about populating a specific cell with their choice is a little more complex and really, for me anyway, requires a copy of the spreadsheet

    In essence though something like this may work

    Dim varUF01 as variant
    varUF01 = userform1.combobox1.value
    sheets("Regenerate Request").range("C40").value = varUF01

    Re: Send Email From Protected Spreadsheet

    I've tested this, sorry should have done that first and it doesn't work for me ... mmmm?

    Anyway, using the same theory and some old code this does ... I have also changed the string to a variant as that is what it should be.

    Re: Send Email From Protected Spreadsheet

    I assume when you refer to editing the body you mean of the email and not excel sheet?

    I think you may be better off using the sendforreview command and using show message as the changes
    Something like

    Dim str As String
    str = Application.InputBox("Enter the changes you require here")
    thisworkbook.sendforreview Recipients:="",,showmessage:=str,includeattachment:=true 'or false if you don't want it

    Re: Count Last 10 Days

    Thanks but neither seem to work correctly.

    hth, I've changed yours to , and it doesn't work properly and I get a #Ref for Jim's.

    Jim, can you explain how yours works like what it is offsetting against as this may enable me to do it myself?

    Thanks for your time!

    Re: Count Last 10 Days

    h1h, thanks for your response. Unfortunately I appear to have not made myself clear. i know how to count the number of x in the row, what i need to do is be able to do this for the last 10 days every day.

    Today is the 21/09/06 and this is in column Q so today I would need to measure the x's between columns H & Q, tomorrow would be the 22nd which is in column R so I would need to measure the x's between columns I & R and so on.

    I know there is a way to write a formula that counts the top row and can then be used to work back 10 days but I can't figure it out myself.

    Re: Cell Names :do I Remove Them ?

    I assume this is via the Insert > Name > Define method? If so go into Insert > Name > Define and remove the relevant "names" individually.

    Alternatively you can use some VB to delete all the names


    I have a spreadsheet with a list in column A, and starting from column D in row 1 are daily dates e.g. 01/10/06, 02/10/06, 03/10/06.

    On a daily basis an x is added manually to the spreadsheet where the list criteria matches the specific date e.g

    A          B          C          D          E          F
    1                                      01/10/06 02/10/06 03/10/06 
    2       M123                              x                     x
    3       M456                                          x         x
    4       M789                              x           x

    What i would like to do in column C is to measure how many x's are in the last 10 days for each row. I have used formulas in the past to do something similar but I can't find them. Sorry I can't upload a sample but firewall won't allow.

    I hope this is clear enough and that someone can help me?

    Re: Adding Named Ranges To Charts

    I have finally found the answer and can update the chart directly by updating the series requirements in in Source Data.

    Thanks for those that looked anyway.

    I have searched and searched but can't find the answer, it doesn't help that I can't dowload any examples as the firewall at work won't allow it.

    I have a chart embedded into a sheet called charts

    I have created 2 named ranges to cover the X & Y categories, however, the data is on a different sheet called Weekly stats

    How do I add the named ranges into the chart? I either get a formula not valid if I try to create this directly in the data range or if I use the named ranges once I click o.k. it reverts back to the source data and the named ranges are no longer seen in the series.

    I am using Excel 2003.

    All I really need to know is how to refer to a named range in the chart Data range or series ranges.

    Re: Count numbered only entries

    Badger, fantastic, many thanks for this.

    Dave, of course you are right, it is best to have numbers in excel, however, in this case it may not be completely practical but if it is I will change it.

    Thanks all for your help and time.

    Hi, I have a spreadsheet with data down column A. The data is either numeric or alpha numeric, however, it is not seen as numerical.

    Is there a formula I can use to count the total number of cells with only numbers in against other criteria too? I can use Sumproduct for 2 criteria but can't figure out how to do the 3rd.

    A bit confusing so I attach a small sample.

    Re: Named Range References on Different Sheets to be the Same

    Jamie, have you tried looking here for more info on named ranges?

    Am I right in saying you have "duplicated" named ranges for every sheet of the workbook? Obviously with different names but to cover the same area of its specific sheet? Within this link it shows you how to define column and row amounts by referencing a number in another cell, this may help as you can have your master number e.g. 10 rows referenced by the range and if you need to change the number of rows / columns just change the cell number. I'm not 100% certain as to what you have requested but have a look at the link and report back with a small example if you need further assistance.

    Re: copy and append text file data to worksheet

    Jon, yes, I "cocked up" the code which I have rectified in my third post (I hope).

    You say your copying from a text file, is this a "pure" text file?

    Try changing to this

    The problem is you are copying everything from the text file (as if it were an excel file) and then there isn't enough room within the new excel sheet to add everything. This code (providing the text is opening within excel) will only copy the relevant cells so, in theory, should work!

    Re: copy and append text file data to worksheet

    OOOooops! My apologies in another code mode ... need to do like this

    Re: copy and append text file data to worksheet

    Sorry, HIH means hope it helps!

    Not having your set-up means I can test it, however the theory is sound. What you are doing is looking for the last cell in column A and then going down 1 row to the 1st blank cell and then appending your text.

    Using your code and adding mine try this ...

    If you still get an error change the "with activesheet" to