Is it possible to have a VBA macro capitalize the first letters in someone's name? For example, if a name is John Stoneburner and a user only enters "john stoneburner' in a text box could it would automatically capitalize it to be John Stoneburner. With Excel, anything is possible! There are two options you could try:
Dim myString as String
myString = Application.WorksheetFunction.Proper(TextBox1.Value)
Or use this option:
Range("A1") = UCase(Left(Range("A1"), 1)) & Mid(Range("A1"), 2)
| Budget |
Hotels | --- |
Airfare | --- |
Car Rentals | --- |
Activities | --- |
Meals | --- |
Petrol | --- |
Misc. | --- |
Total | $0.00 |
I am working on changing the Excel spreadsheet I used to help plan my honeymoon vacation into a downloadable user template. Here are the major categories I have listed to help users plan their budgets and activities. Are there any categories that I am missing that you would like to see added? I'm sure I will be able to add more unexpected items after I go on my actual honeymoon.
Booked | Item | Cost | Budget | Balance |
YES | Hotels | $1,511.83 | --- | #VALUE! |
YES | Airfare | $600.80 | --- | #VALUE! |
SELECT | Activities | $874.00 | --- | #VALUE! |
SELECT | Car Rental | $279.13 | --- | #VALUE! |
SELECT | Meals |
| --- | #VALUE! |
SELECT | Petrol |
| --- | #VALUE! |
SELECT | Misc. |
|
| $0.00 |
| Total | $3,265.76 | $0.00 | #VALUE! |
I recently needed to import some elapsed time data from a scientific instrument into Microsoft Excel for in order to compute some complex calculations. The scientific instrument provided elapsed time in the following format:
01:02:03:123 where 01 = hours, 02 = minutes, 03 = seconds and 123 = milliseconds.
At first glance, Excel does not seem to recognize milliseconds. However, you just have to know what Excel is looking for. In this case, Excel expects to see milliseconds as:
01:02:03.123
Notice the difference? It is tiny yet important (a "." instead of a ":").
Another method to recognize milliseconds in Excel may be to try this formula:
=REPLACE(A1,9,1,".")+0 where your time values are in column A. Then, format the converted values as hh:mm:ss.000
How can you do bullet points in Excel similar to those in Microsoft Word? There are at least six methods that I know of:
1. =CHAR(149)
2. Alt+0149
3. Alt+249
4. The lower case letter n in Wingdings font
5. You can also import them from Word or use the character map (char.exe)
6. VBA code line: ActiveCell.Value = "•"
There may be others that I haven't thought of yet. Any suggestions?
I was recently asked this question: Is there a way in Excel to write a macro to copy a cell's numeric value only and not the formula? Basically, can you use VBA to paste only the value, like the Paste Special feature in the Values Only menu? The answer is yes!
To do a simple Paste operation you might write this line of code:
ActiveSheet.Paste Destination:=Worksheets("Positions").Range("A2")
Instead of '.Paste', use '.PasteSpecial." Try it with this line:
Worksheets("Positions").Range("A2").PasteSpecial xlPasteValues
If you still have questions then see the Microsoft Excel VBA help file for the use of the PasteSpecial method. It's pretty straightforward.
Another suggestion, instead of using code like this:
Set rng6 = .Range("A3").End(xlDown).Offset(0, 41)
rng6.Copy
ActiveSheet.Paste Destination:=Worksheets("Positions").Range("A2")
you could use something like this:
Worksheets("Positions").Range("W2").value = .Range("A3").End(xlDown).Offset(0, 41).value
This new code will accomplish the same thing as the original but it will also allow your code to run more quickly by avoiding the use of copy and paste althogether.
