CODEWITHSUNDEEP
vbaexcel
ZAX
ZAX

Reputation: 1006

Copy Value Of Cell, Not Formula

I am trying to grab only the current number of minutes in an hour.

I use the NOW() function and I have the format of the cell it's in set to only mm. So in the cell display, if it's 9:08, I get 08.

Whenever I try a excel formula like =VALUE(D5) or =D5, I get (I'm assuming this is what it is) an unformatted date string.

I am now looking to macros using VBA to solve my problem.

I've tried:

Worksheets("Sheet1").Cells(23, 1) = Worksheets("Sheet1").Range("E20").Value

However I get the same thing as with my excel formulas. Is there any VBA way of getting only the value that's displayed in the cell and copied to a new area. It doesn't really need to be copied over, I just need to use the value further in my macro.

I'm open to other ways of getting the current number of minutes in the current hour as well.

Upvotes: 0

Views: 1152

Answers (3)

pnuts
pnuts

Reputation: 59495

Might be calculated like so: 

=60*(NOW()*24-INT(NOW()*24))

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96791

If the displays 9:08

then select that cell and:

Sub dural()
    Dim s As String
    s = Split(ActiveCell.Text, ":")(1)
    MsgBox s
End Sub

Note this is a String

Upvotes: 0

Bathsheba
Bathsheba

Reputation: 234875

Do try not to confuse formatting and data.

why not use =MINUTE(D5) in a cell which will set that cell to the minute value irrespective of formatting? Use that cell as your source in your VBA.

=HOUR(D5) would operate similarly but return the hour part.

Upvotes: 2

Related Questions