CODEWITHSUNDEEP
vbaexcel
Gustav
Gustav

Reputation: 3

VBA Excel - Extract first 10 characters of entire column

I would like to extract the first 10 characters from all cells in an entire column (B-column). The cells are dates on the format:

2014-09-03 08:00:00

And I want all of them to be like this:

2014-09-03

My code looks like:

Sub AdjustDate()

Dim NewDate As String

For Counter = 1 To 1000000
    Set NewDate = Left(Cells(Counter, 2), 10)
    Cells(Counter, 2).Value = NewDate
Next Counter


End Sub

I am no master at VBA and I cannot figure out what I do wrong..

Would be thankful for help!

Upvotes: 0

Views: 5314

Answers (2)

Dave84981654
Dave84981654

Reputation: 1

I suspect this has to do with the formatting since the dates are typically stored as a number of days past Jan 1 1900 then formatted to be whatever you like

also agree with above, no need to use "set" in this situation w/o having an object to modify

Upvotes: 0

Netloh
Netloh

Reputation: 4378

Set is used when assigning an object. Since a string variable is not an object Set should either be replaced by Let or left out all together.

In your case you wouldn't need to define the 10 first characters as a string variable and then replace the cell with the value of that variable. You could instead just do:

For Counter = 1 To 1000000
    Cells(Counter, 2).Value = Left(Cells(Counter, 2), 10)
Next Counter

Upvotes: 2

Related Questions