Format conversion

[Alex24]

Hello everyone,

Since some time I'm trying to write a macro command, but I fail to finish it because the values in one field are skipped by the code because (I assume) they are strings and not integers. The values in column E (Release Date) are retrieved from another data source and have a problematic format.

Could you please give me a hint on how I could convert the values of this field so that the code can fully run?

Attached you can find the file and the code and commentaries on the objectives of the code.

Thanks in advance!

 

[Hui]

I would add a small piece of code like below to the start of the macro
It will convert Column E to Dates

  Dim LR As Double
  LR = Range("E" & Rows.Count).End(xlUp).Row
  Range("E2", Cells(LR, 5)).TextToColumns _
  Destination:=Range("E2"), _
  DataType:=xlDelimited, _
  TextQualifier:=xlDoubleQuote, _
  ConsecutiveDelimiter:=False, _
  Tab:=True, _
  Semicolon:=False, _
  Comma:=False, _
  Space:=False, _
  Other:=False, _
  FieldInfo:=Array(1, 4), _
  TrailingMinusNumbers:=True

Also not that column A only has values in down to row 561
But Column E has values to row 7,367
That seems odd?

I would suggest clearing the whole worksheet before importing data to avoid issues

 

[Alex24]

Thank you for your prompt reply. At a first glance the code seems to work. I see that the values have changed from the initial format to a short date format. I would have to adjust the rest of the code a bit as I see there are some glitches in the logic.

However, I would appreciate if you could indicate some training materials so I can understand the logic behind your code. This would help me to enhance my skills and help others on my turn.

Thanks again for your help!

 

[Alex24]

Hi again,

One last question on this one. I've tried to adapt the code to another report which has to do pretty much the same thing. The only difference is that the report in question has the problematic date format in column AV and not in E. I've written the code below while modifying some references, but it does not work. Could you please point out where the issue lies?

Dim LR As Double
LR = Range("AV" & Rows.Count).End(xlUp).Row
Range("AV2", Cells(LR, 48)).TextToColumns _
Destination:=Range("AV2"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(1, 47), _
TrailingMinusNumbers:=True


Thanks in advance!

 

[Alex24]

More precisely, Excel gives me this error:

 

[Marc L]

Hi !

Come back to original FieldInfo (no matter with column number !)
and AV column is not the #47 but #48 in Range.TextToColumns !
You can use "AV" in Cells instead of number …
​

 

[Alex24]

I've made the changes and it works! Thank you! However, I am still trying to find out the logic behind the code. What is the role of the FieldInfo row?

I've modified it with 47 instead of 4 because I though it represent the number of fields before the field in question (eg. 4 field before field E in the initial example).

Thanks! Very much appreciated!

 

[Marc L]

All is explained in TextToColumns inner VBA help !

Its parameter FieldInfo is for data type (convert) …
​

 

[Hui]

The line
FieldInfo:=Array(1, x), _

Specifies the following Format conversions
FieldInfo:=Array(1, 1) = General Format
FieldInfo:=Array(1, 2) = Text Format
FieldInfo:=Array(1, 3) = Date Format MDY
FieldInfo:=Array(1, 4) = Date Format DMY

etc

 

[Alex24]

Thank you! Very much appreciated!