Thx Dave,
I was at work and the rotten buggers kept calling me away to do my job, of all things :nana:
Didnt have time to supply an example.
Nice solution too Andy.
Bedtime - Nighty Night :beergrin:
Weasel
Thx Dave,
I was at work and the rotten buggers kept calling me away to do my job, of all things :nana:
Didnt have time to supply an example.
Nice solution too Andy.
Bedtime - Nighty Night :beergrin:
Weasel
Garry,
Not sure if you can reference that but one way would be to test to see if the sheet still exists: Yes the user clicked cancel/No the user clicked OK.
Regards
Weasel
The activeworkbook.sendmail doesnt have a body parameter, the code below should do the trick.
Regards
Weasel
Sub sndmail()
Dim objOutlook As Object
Dim objMailMessage As Outlook.MailItem
Dim emlBody, sendTo As String
Dim wkbook As String
Application.ScreenUpdating = False
Set objOutlook = CreateObject("Outlook.Application")
Set objMailMessage = objOutlook.CreateItem(0)
sendTo = Range("a1").Value & "@abc.com"
wkbook = ActiveWorkbook.FullName
emlBody = "My special message"
With objMailMessage
.To = sendTo
.Body = emlBody
.Subject = "Operating Report - " & ThisMonth & " 2003"
.Attachments.Add wkbook, olByValue
.Display
.Send
End With
End Sub
highlight the range you wish to apply this to and select Data, Validation,Length,Less Than or Equal to, 250.
Regards
Weasel
The code below will select the range of cells limited by the parameters returned in the function you supplied. You can change the last line to start the selection at any point you wish ie. if the first used cell on the sheet that you wanted to select was A7 then replace A1 with A7 in the last line.
I will have to look atthe rest when I get home from work.
Regards
Weasel
Sub getrng()
Dim numRows, numCols As Integer
numRows = LASTCELL(Sheet1).Row
numCols = LASTCELL(Sheet1).Column
Range("a1").Select
rng2 = ActiveCell.Offset(numRows - 1, numCols - 1).Address
Range(("a1"), rng2).Select
End Sub
Ian,
Could you please post a small sample of your data, both before and after.
Regards
Weasel
PCFish,
I will give you an option on your first question before tackling the rest. The follow sub will return a variant named rng that will give the address of the used range in a sheet (provided that every record has data in each column and that there are no gaps)
Sub findrange()
Dim rng As Variant
Range(Range("IV1").End(xlToLeft), Range("a65536").End(xlUp)).Select
rng = Selection.Address
End Sub
I will look at the other points and get back to you a bit later.
Regards
Weasel
Sedso,
Not enough data to formulate a system.
How does the sreadsheet know that Thomasville beat Wilcox?
All that is in the example is a bunch of random facts with no clue as to how they were arrived at.
You say that you want only the rankings, not the score to change but in the example the Thomasville despite losing not only leapfrogs Dallas County but has its score changed???
Could you provide clearer explanation
Weasel
Sedso,
Please only post the each request once. Posting multiple entries of the same thing will not get faster answers but will fill up the board unnecessarily.
What you are asking for is a little more than a function, more like an entire logic system. You have not provided enough info in your post to really get stuck into it.
Can a team only leapfrog a team it has beaten? If so........
What if there is a team in between them yet all 3 teams lie withing the leapfrogging margin?
Have you set up a table that stores all the results so head to head comparisons can be made?
Please post back.
Regards
Weasel
RedGlow,
Check to see if you have a file named book.xlt in your xlstartup folder. If yes:
Open it and type the following line into the workbook_open module
activeworkbook.savelinkvalues = false
save & close
If no:
do the above in a blank workbook and save it as book.xlt in the xlstart folder.
This will only work for workbooks opened on your PC.
Regards
Weasel
Quite true Will.
I guess I didnt look at it that way - whenever I have to add leading zeroes it is to standardise a number to match a six digit ID code required by a database - which is the way I looked at this question.
Have a good day
Weasel
:cheers:
YA!
time for bed!
nighty Night everybody
:dead:
JITUK
WOW, I can answer a question that YOU ask! :yes:
My son (8) does a program by the name of KUMON. This is international and they must have it back in the mother country.
It throws away all these new fandagled, communist, fairy , left wing, hippie methods of education and makes kids learn their maths by wrote.
My son has been doing this for 6 months and is now 2 years ahead of the school system.
The philosophy is if you dont know basic addition/multiplication off by heart then how the hell can you ever learn complex algebra. Gotta take kids back to basics these days. I will not even begin to show my son Excel until he can do it himself.
I deal with training adults ona day to day basis on things that involve maths and I am finding that without a calcuator they are screwed. I blame the education system.
The KUMON method is not the ultimate answer, you could do it yourself if you had time, but lets face it : who has these days. http://www.kumon-abingdon.freeserve.co.uk/othcentr.htm
Have a look above - highly recommended!!
kindest regards
Weasel
Maybe I missed a little something here but...............
If you want all your numbers to be 5 digit numbers with leading zeroes:
Right Click
Format Cell
Number
Custom
00000
should do the trick
regards
Weasel
DOH!
Here it is:
Edit:
A Will can type faster than a Weasel
ayeletgini
Welcome to the Board!!!! :thumbcoo:
This is a much asked question! but NO :no: a formula or function cannot change the contents of a cell other than the cell it is entered in.
You will need to enter a seperate IF statement for each cell that you want to update. Either A1 contains the word "hello" or not.
I have attatched a VERY simple example.
You can nest up to 7 IFs to create some very complicated formulae, but chances are a VLOOKUP would have done you better.
look at this website under http://www.microsoftexceltraining.com/Excel/TipsAndTricks.htm
to see a VERy handy way to create nested formulae.
good Luck
Weasel
schattenjagergk
With regard to using vlookups to other workbooks please keep the following in mind:
I work in a large company and have developed spreadsheets with vlookups for stafflists that can contain as many as 2500 staff members. These workbooks are stored on network drives.
I have found that although it is possible to open the workbook and have it update the vlookups each time WITHOUT the other workbook already being open - this can take a BLOODY long time. The work around to this is I set the workbook calculation to manual in the onOpen subroutine and then have a commannd button that runs a macro that opens the other workbook before updating and then closes it. This makes the update run much much much faster.
Just something to keep in mind as it can be very annoying waiting for the ss to update the other way with such large numbers of records.
:beergrin:
learn,
the code sent should work fine on the orignal data you have posted once you change B2 to B5, but see above edit about the inserted column.
eg. Range(("b5"), Range("b5").End(xlDown)).Select
Weasel
:cheers:
sry Paddyd wasnt trying to butt in
Sedso10,
A function can only effect the content of the cell it is enetered in. If you were to type anything else in that cell it would type over and delete the function.
For your situation I would recommend a VLOOKUP function working from a list that would return a numerical value NEXT to the cell that you enter "airplane" in.
It works like
=VLOOKUP(A11,A1:B6,2,0)
where A11 contain the value you want to lookup - in this case airplane.
A1:B6 is the range that has the list of criteria and values
2 is the column to return - in this case the column containing the numbers
0 means you want an exact match - 99% rec.
HTH
Weasel