1.

Solve : Excel visual basic script wanted.?

Answer»

I'm entering a work schedule on a sheet named "hours" in Microsoft Excel. I want to create a variable so I can type in "open" or "close" and it automatically recognizes what those times are. So I don't have to manually type in "11:45 pm" or "23:35" every time.

How is it done?

Thanks if you can help.first, need to clarify few things:
1. is it vbs (windows SCRIPTING host - installed by default by windows) or is it vba (built-in inside excel)

Quote from: And 1 on March 19, 2009, 11:57:06 PM

I want to create a variable so I can type in "open" or "close" and it automatically recognizes what those times are. So I don't have to manually type in "11:45 pm" or "23:35" every time.
give EXAMPLE on how you type in open close. and which cell is it going to be inserted time.

there is METHOD both in vbs and vba to get current time, and coincedently it's named method Time()
VBA: MsgBox Time
VBS: wsh.echo Time

btw, there is a shortcut when working with excel to insert date or time:
CTRL+semicolon -> insert date on focused cell
CTRL+SHIFT+semicolon -> insert time on focused cellCan you give me more steps? Like what does the code look like in excel?

Something like this:


Private Sub Worksheet_Activate()
Dim noon As Date

noon = #12:00:00 PM#


Set ws = ActiveWorkbook.Sheets("hours")

End Subso, VBA it is.
i still don't understand what you are trying to achieve.

the following code will update K1 and K2 to current time everytime the worksheet is activated.
Code: [Select]Private Sub Worksheet_Activate()
Range("K1") = Format(Time, "hh:mm")
Range("K2") = Format(Time, "hh:mm AMPM")
End SubI want excel to replace the the string "noon" with the time value of 12:00 PM. I want it to be able to do it for any cell in the sheet.

Sorry for not being more clear.And1,

have you try to use the built-in Replace Function?
Press CTRL-H, and fill in "noon" to replace with "12:00 PM", then click ReplaceAll button

From Excel Help File (F1)
Quote
TIMEVALUE
Returns the decimal number of the time represented by a text string. The decimal number is a value ranging from 0 (zero) to 0.99999999, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).

Syntax
TIMEVALUE(time_text)

Formula Description (RESULT)
=TIMEVALUE("2:24 AM") Decimal part of a day, for the time (0.1)


or do really mean the timevalue function? which convert time to a decimal number.the VBA way
create macro and name it ReplaceNoon, assign a shortcut key to run the macro, then paste the following code

Code: [Select]Sub ReplaceNoon()
'Application.ActiveSheet.UsedRange.Select
For Each c In Application.ActiveSheet.UsedRange
If Not (IsEmpty(c) Or IsError(c)) Then
If UCase(c.Value) = "NOON" Then
'replace noon to string of 12:00 pm
c.Value = "12:00 PM"

'replace noon to formula timevalue of 11:59 pm
'c.Formula = "=TimeValue(""11:59 PM"")": c.NumberFormat = vbGeneral
End If
End If
Next
End Sub

Notes: TimeValue of 12:00PM is invalid value, but when tested it works and return 0.5


Discussion

No Comment Found

Related InterviewSolutions