Posts by REP013

Re: Use A String In A Vbe Formula

Many thanks, that's excellent.

Slowly scraping that rust away.

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.

1.

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

Assuming it is default (userform1)

Userform1.show

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.

Sub tstmail()


Dim varBody As Variant




Set msotlApp = CreateObject("Outlook.Application")
Set msotlNewMail = msotlApp.CreateItem(olMailItem)


varBody = Application.InputBox("Please advise the changes you would like here")


With otlNewMail
.to = "[email protected]"
.cc = ""
.Subject = "Changes to spreadsheet"
.body = varBody
.send
End With


End Sub

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:="@email.com", Subject:=thisworkbook.name,showmessage:=str,includeattachment:=true 'or false if you don't want it

Re: Count Last 10 Days

Jim, thanks, I will look at this.

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

Sub DeleteNamedRanges()


Dim namR    As Name


'Remove all named ranges from the workbook
For Each namR In ActiveWorkbook.Names
    If namR <> "No" Then
        namR.Delete
    End If
Next namR
    
'Message box to advise that named ranges have been removed.
MsgBox ("Clean Completed")


End Sub

HTH

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

All, many thanks again for your help.

Re: Count numbered only entries

Oops, I forgot to ask, how do I do this in reverse e.g items which are not numbers?

Thanks in advance.

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?

http://www.ozgrid.com/Excel/DynamicRanges.htm

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

FileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt") 
If FileToOpen <> False 
Then 
Workbooks.Open FileToOpen 
Else 
    Exit Sub 
End If 
 
''New code I've added and removed the cells.select & cells.copy
Range(Range("A1"), Range("A1").SpecialCells(xlLastCell)).Copy
 
Windows("myfile.xls").Activate 
 
 ''Code I've added!
With activesheet 
    .Range("A65536").end(xlup).offset(1,0).paste 
End With

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

FileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt") 
If FileToOpen <> False 
Then 
Workbooks.Open FileToOpen 
Else 
    Exit Sub 
End If 
 
Cells.Select 
Selection.Cut 
 
Windows("myfile.xls").Activate 
 
 ''Code I've added!
With activesheet 
    .Range("A65536").end(xlup).offset(1,0).paste 
End With

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

With sheets("[COLOR="Red"]YOURSHEETNAMEHERE[/COLOR]")

FileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt") 
If FileToOpen <> False 
Then 
Workbooks.Open FileToOpen 
Else 
    Exit Sub 
End If 
 
Cells.Select 
Selection.Cut 
 
Windows("myfile.xls").Activate 


''Code I've added! 
With activesheet 
    .Range("A65536").end(xlup).row).offset(1,0).paste 
End With