I have this string........actually its the contents of a cell..........I want to extract the date part of this string and then save it back onto itself.
i am enclosing a file with the example strings.
Maybe:
Sub ExDate() Dim cell As Range For Each cell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row) cell.Value = DateValue(Left(cell.Value, 11)) Next 'cell End Sub
Regards
exactly what i was looking for.....thanks.
You're welcome. Thanks for the rep.
Here is the code i ended up with. I combined code from several different threads i have posted...........i keep learning about how to more efficiently do things........this code is cool because it puts values into array efficiently and then extracts them from the array very efficiently.
Dim ws As Worksheet Dim arrTime As Variant Dim arrIndex As Long Set ws = ActiveWorkbook.ActiveSheet 'Assign to the array the entries from cell A2 to the last value in the column 'very cool and fast way to put values into an array. arrTime = ws.Range("A2", ws.Cells(Rows.Count, "A").End(xlUp)) For arrIndex = 1 To UBound(arrTime, 1) arrTime(arrIndex, 1) = DateValue(Left(arrTime(arrIndex, 1), 11)) Next 'This takes values from array and puts them into the sheet ws.Range("A2").Resize(UBound(arrTime, 1), 1).Value = arrTime
Just thought i would share.
one question i did have about the array that someone might answer is.........why is the array two dimensional........not sure why the creator of the code did it this way but it works.
Interested in efficiency ?
Sub snb() [A1:A100] = [if(A1:A100="","",datevalue(left(A1:A100,11)))] End Sub
ok, that is just too cool. but what about if the number of rows in col A is variable and could be more than 100?
very cool !
Either you take a safe range:
or you use a dynamically named range:Sub snb() [A1:A10000] = [if(A1:A10000="","",datevalue(left(A1:A10000,11)))] End Sub
Sub snb_001() columns(1).specialcells(2).name="c00" [c00] = [if(c00="","",datevalue(left(c00,11)))] End Sub
thanks! Had a few questions.
1) What is c00?
2) what is this doing exactly?
columns(1).specialcells(2).name="c00"
"c00" is a text string; any string would do as long as you use it in the next line in the places where c00 is now.
second question: that line makes a named range of all the cells in column A that contain values. The name of that range is "c00" in this example.
If you assign a range to a Variant, you always get a 2D array, even if the range is only one row or column.Originally Posted by welchs101
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks