tomhoneyfield
New Member
- Joined
- Jul 19, 2012
- Messages
- 3
Hi,
I'm currently writing an automated process that opens up a productivity report (xls) and has to add together a bunch of different times to work out a total.
Now, when I try to add together the values from individual cells, rather than adding together the raw value within the cell, I get a concatenated result like the following;
The cell in column "O" = "00:05:50"
The cell in column "Q" = "00:03:04"
myVar = wbInb.Sheets("Inbound SLA").Range("O" & raRow).Value + _
wbInb.Sheets("Inbound SLA").Range("Q" & raRow).Value
MsgBox myVar
Result = "00:05:5000:03:04"
It could be important to note that the information sits in cells that are formatted general. Also O and Q are merged with the cell immediately to their right. I don't know if this has any impact.
Interestingly, without changing any formatting of any cells, if I set the formula of a random general formatted cell to;
"=O25+Q25" (as an example)
Result = 0.006180556 - which then when formatted to hh:mm:ss translates to "00:08:54" - Hey presto! That's what we want.
Now it's a bit ridiculous but to get around this in my macro, I coded up like the following;
wbInb.Sheets("Inbound SLA").Range("Z" & raRow).Formula = "=O" & raRow & "+Q" & raRow
myVar = wbInb.Sheets("Inbound SLA").Range("Z" & raRow).Value
MsgBox Format(myVar, "HH:MM:SS")
Result = "00:08:54" - Bob's your uncle (he's not)
I suppose my question is, does anyone know a much easier way to add this data type together in VBA without having to write formula on the fly? It doesn't really matter because I have a working macro, but I just have a feeling that there's a much easier way to approach this that I'm over looking without having to use so much code.
Thanks,
Tom
I'm currently writing an automated process that opens up a productivity report (xls) and has to add together a bunch of different times to work out a total.
Now, when I try to add together the values from individual cells, rather than adding together the raw value within the cell, I get a concatenated result like the following;
The cell in column "O" = "00:05:50"
The cell in column "Q" = "00:03:04"
myVar = wbInb.Sheets("Inbound SLA").Range("O" & raRow).Value + _
wbInb.Sheets("Inbound SLA").Range("Q" & raRow).Value
MsgBox myVar
Result = "00:05:5000:03:04"
It could be important to note that the information sits in cells that are formatted general. Also O and Q are merged with the cell immediately to their right. I don't know if this has any impact.
Interestingly, without changing any formatting of any cells, if I set the formula of a random general formatted cell to;
"=O25+Q25" (as an example)
Result = 0.006180556 - which then when formatted to hh:mm:ss translates to "00:08:54" - Hey presto! That's what we want.
Now it's a bit ridiculous but to get around this in my macro, I coded up like the following;
wbInb.Sheets("Inbound SLA").Range("Z" & raRow).Formula = "=O" & raRow & "+Q" & raRow
myVar = wbInb.Sheets("Inbound SLA").Range("Z" & raRow).Value
MsgBox Format(myVar, "HH:MM:SS")
Result = "00:08:54" - Bob's your uncle (he's not)
I suppose my question is, does anyone know a much easier way to add this data type together in VBA without having to write formula on the fly? It doesn't really matter because I have a working macro, but I just have a feeling that there's a much easier way to approach this that I'm over looking without having to use so much code.
Thanks,
Tom