Hi Team

I am struggling to wrap my head around this.

I have a sheet that get populated via data off a clipboard( exported from Sapphire DB.

Does anyone have a handy snippet that will look at the range and detect what it is then do stuff:

I tried using this:

..Numberformat = "dd/mm/yy"
..Value = DateValue(.Value)

But it halts on the second part.

I then tried this ( and strangely enough, it worked once ).

With c
.. Formula = LEFT(.offset(,-1), Worksheetfunction.Find(".",.offset(,-1),-1))
End with

When I ran it again it kept throwing up a "Find" execution error.

Kind of hoping for something like:

IF IsDate(Range("A2:A100") Then
"Do something to trim off Time"
ELSE
IF IsText(Range("A2:A100") Then
"Do something else to trim off Time"
End If
End If

As always
TIA

Cheers
Mark.

Hi Mark,

Am Thu, 5 Oct 2017 05:20:22 -0700 (PDT) schrieb Living the Dream:

I have a sheet that get populated via data off a clipboard( exported from Sapphire DB.

Does anyone have a handy snippet that will look at the range and detect what it is then do stuff:

did you try Data = Text to column = Delimited = Delimter is comma
In step 3 choose for second column "Do not import column"

If that doesn't work try:

Sub DateTime()
Dim rngC As Range
For Each rngC In Range("A2:A100")
rngC = Int(rngC)
Next
Range("A2:A100").NumberFormat = "dd/MM/yy"
End Sub

If that also doesn't work provide a workbook with the data in column A
that we can look for the format and how to solve the problem.


Regards
Claus B.
--
Windows10
Office 2016

Hi Claus. As always, you are a legend. Worked a treat.

Thank you heaps.

Cheers
Mark.