VBA = Cell Reference Question/ Formatting issue

[VBA learner ITG]

Good afternoon,

I have the below VBA code which references a cell in another tab of a project document and returns the data from my SBD_Data tab.

With Range("W6:W2151")
.Formula = "=SDB_Data!V8"
.Value = .Value
EndWith

However, the issue i can't seem to resolve is that the cell that is being referenced is a time field and the answer should be exactly as the field.

However when the code is run its returning a 0.

To complicate the issue is that I can't change the format of where the answer is going to as that field needs to be be a general field.

so the answer i am expecting to see is 00:00:00 and not 0.

IS IT POSSIBLE TO GET THE DATA TO BE 00:00:00 WITHOUT CHANGING THE FORMAT FROM GENERAL TO A TIME FIELD HH:MM:SS?

 

[yky]

In a blank cell with general format, I entered "0:0:0". It's automatically changed to "00:00:00". When I checked the format, it became custom: hh:mm:ss. So, I guess the answer to your question is no.

 

[VBA learner ITG]

Thank you for looking.

Can you shed some light to why if i only use the formula =SDB_Data!V8 written as a formula outside of VBA it works by bringing the data over in the correct format if the doc is formatted as general.


I even tried using the amended VBA code below. However, i get an error message on my Mac, even though it should work in principle.

With Range("W6:W2151")
.Formula = "=SDB_Data!V8"
R.Value = R.Value
EndWith

 

[steve the fish]

Its possible to keep the cell as general and show 00:00:00. Why though?? This for instance:

="00:00:00"

on a general formatted cell will appear as 00:00:00

 

[steve the fish]

Or do you mean something like this:

With Range("W6:W2151")
    .NumberFormat = Range("SDB_Data!V8").NumberFormat
    .Value = Range("SDB_Data!V8").Value
End With

 

[VBA learner ITG]

Hi Steve,

Thank you for taking the time to respond to my post.

The reason I have to have the format in general for the time fields as the doc thats being populated is being imported into a company software which doesn't like formatting changes.

The software for some reason reads the line and sees that its not in the general format as it expects and each time i do an import it rejects the whole document.

Thats my rational for trying to amend the VBA code to accommodate these restrictions set, this otherwise I wouldn't have wasted anyone's time on this.

I have to say I am grateful for the replies as it gives me a chance to discuss this with my peers.

 

[steve the fish]

This will produce a text field that appears as time:

tm = Range("SDB_Data!V8").Value
With Range("W6:W2151")
    .NumberFormat = "@"
    .Value = Format(Hour(tm), "00") & ":" & Format(Minute(tm), "00") & ":" & Format(Second(tm), "00")
    .NumberFormat = "General"
End With

 

[VBA learner ITG]

Hi Steve,

Just to let you know your code works and the import completed without any rejections.

I hope anyone else reading this who has the same issue as myself finds this post helpful.

Thank you so much for your support.