VBA to convert column range of date to long int

[Mr80s]

Hi Folks,

I have a script that copies ranges (columns) of data from one workbook to another. It copies a column of Dates in format MM/DD/YYYY and I'd like to convert that entire column to long integer.

So here is my code that copies the columns over:

Dim nmary As Variant, sh1 As Worksheet, sh2 As Worksheet, i As Long, rng As Range, fileLocation As StringDim fileName As String
Set sh1 = ThisWorkbook.ActiveSheet
nmary = Array("Last Name", "First Name", "Student ID", "Role", "End Date", "School Email Address")
Workbooks.Add
Dim col As Range
Dim value(0) As Integer
Set sh2 = ActiveWorkbook.Sheets(1)
    For i = LBound(nmary) To UBound(nmary)
        Set rng = sh1.Rows(inputValue).Find(nmary(i), , xlValues).EntireColumn
        rng.Copy sh2.Cells(1, i + 1)
    Next

I then do some renaming of my column headers:

sh2.Columns.AutoFit
sh2.Range("A:A").Replace "*Last Name*", "sN", xlWhole
sh2.Range("B:B").Replace "*First Name*", "givenName", xlPart
sh2.Range("C:C").Replace "*Student ID*", "otherIpPhone", xlPart
sh2.Range("D:D").Replace "Role", "templateName", xlPart
sh2.Range("E:E").Replace "*End Date*", "accountExpires", xlPart
sh2.Range("F:F").Replace "*School Email Address*", "mail", xlPart

What I'd like to do is now take all of Column E "End Date" of format MM/DD/YYYY and convert to long integer (needs to be that format to be imported into another program).

I'm trying :

Dim datevar As Long
For i = 1 To Rows.Count
 datevar = CLng(Cells(i, 5))
 Cells(i, 5) = datevar
Next i

but this A) is way to inefficient and B) doesn't end up converting.

Any ideas?

Thanks!

 

[Joe4]

You are welcome! I am glad you got it working now.