Quote marks in Formulas passed from VB Script in Excel

[Guest]

Interesting one. I have a calculation subroutine that goes row by row to see
if there are any matching records that have three same fields of information
in them. If they do then it tries to replace the value of the last record
with a formula that will calcuate a difference between two times.
Unfortunately I don't know how to pass the " marks in the
TEXT(CELL1-CELL2,"h:mm") formula. Or I could try calculating the time
difference in the routine and just passing the value, but I don't know how to
use an excel function in a VB subroutine (the problem is that the values are
TIMES in the cells to be calculated). It's Friday, I'm toast.

Sub CalculateTurnover()
For n = 2 To 5392 (my row range)
Rem - see if date, employee and room match (columns 3,4,10) with the
following row
If (Cells(n, 3) = Cells(n + 1, 3)) And (Cells(n, 4) = Cells(n + 1, 4)) And
(Cells(n, 10) = Cells(n + 1, 10)) Then
Rem - if so put formula to calculate time difference and store it into
column 12
Cells(n + 1, 12).Formula = "=TEXT(" & Cells(n, 9).Value & "-" &
Cells(n + 1, 5).Value & ',"h:mm")'
End If
Next n
End Sub

 

[Dave Peterson]

Double them up.

But since you're populating the cell with a formula, I would think that your
formula should be a formula that adjusts when the cell precedents change--maybe:

Option Explicit
Sub CalculateTurnover()
Dim n As Long
For n = 2 To 5392 '(my row range)
'see if date, employee and room match (columns 3,4,10)
'with the following Row
If (Cells(n, 3).Value = Cells(n + 1, 3).Value) _
And (Cells(n, 4).Value = Cells(n + 1, 4).Value) _
And (Cells(n, 10).Value = Cells(n + 1, 10).Value) Then
'if so put formula to calculate time difference
'and store it into Column 12
Cells(n + 1, 12).Formula _
= "=TEXT(" & Cells(n, 9).Address & "-" _
& Cells(n + 1, 5).Address & ",""h:mm"")"
End If
Next n
End Sub

 

[Guest]

Just in case someone needs to know how - I figured out how to do it:
Sub CalculateTurnover()

For n = 2 To 5392
If (Cells(n, 1) = Cells(n + 1, 1)) And (Cells(n, 2) = Cells(n + 1, 2))
And (Cells(n, 3) = Cells(n + 1, 3)) Then
Rem MsgBox ("Duplicate Date & Room & Employee in " & Cells(n + 1,
1).Address & Cells(n + 1, 2).Address & Cells(n + 1, 3).Address)
Cells(n + 1, 6).Formula = "=TEXT(" & Cells(n, 4).Value & "-" & Cells(n
+ 1, 5).Value & "," & """h:mm""" & ")"

End If
Next n
End Sub