VBA has much more "in-built power" than Excel and thus you don't really need to rely on Excel formulae when using VBA. But if you want to do it anyway, you have to do something like this:
Range("B" & i).Value = "=Time(0, 0, (" & Last - r2 & ") / 1000)"
To get the value from the cell:
Dim cellVal As Date
cellVal = Range("B" & i).Value
You just have to make sure that the VBA variables and the strings written to the cells are treated separatedly (with " and &, as shown above) and that you store the value of the given cell in a variable of the right type (if you rely on
string type it would work with any content).
Some correction in your code:
Dim Count As Integer
Dim i As Integer
Dim Last As Double
Set myRange = Columns("B:B")
Last = 10 'What you want here??
Set r1 = ThisWorkbook.Sheets("Sheet1").Range("A2")
Dim r1Val As Long
r1Val = r1.Value
Count = Application.WorksheetFunction.CountA(myRange)
For i = 1 To Count
Set r2 = ThisWorkbook.Sheets("Sheet1").Range("B" & i)
r2.Value = "=" & r1Val & " - Time(0, 0," & CStr(Last) & "/ 1000)" 'r2 = r1 - Time(0, 0, (Last - r2) / 1000) -> r2 = ... Last - r2 does not make any sense; r2 is a whole date and Last - r2 refers to a part of a date
This is just a simple version of your code showing you some of things you shouldn't be doing: you are mixing up ranges with values, not considering different types, accounting for variables which I cannot even understand, etc.
Just take this code, analyse it carefully, understand what you have done wrong and keep adding functionalities step by step, by doing some research every time. The ideas are very simple:
- You can set as many cells as you wish as a range.
- A range can have different contents (got through range.Value); these contents are associated to a variable in VBA which has to have
the same type (string works for any content).
- When you write to a cell, you write the whole text (if you put "=" ahead, Excel understand it as a formula).
- When you read from a cell, you get the final value (the one from the formula, if applicable).
I recommend you to do some research and get much more used to VBA than what you are right now.