CODEWITHSUNDEEP
excelvba
Dante1986
Dante1986

Reputation: 59989

Excel 2010 select each cell in a row, activate them one by one

I have an XML file that I drop into Excel to make it automatically generate a table. First column(A) has a name, and the second column(B) has a date. There are a few more columns, but those are not relevant to this question.

So it looks like this screenshot:

Screenshot

Now there is a very idiotic bug in Excel (2010). When I have the Data column, I set its property to be Dates. But Excel does not understand they are dates now. But if I double click in a cell to activate it, at that point it starts to understand its a date... strange bug, but I have to to this for each cell.

Now what I was thinking, is to have some macro or something, that activates each cell (if there is content) one after another in the B column.

Now, I don't have a clue how I can do this in the build in VBA of Excel.

Is there someone out here that could help me? I can imagine it would only need a handful of lines to make it work.

Upvotes: 3

Views: 18268

Answers (6)

office
office

Reputation: 1

I use this:

Sub makeDate()
Dim rAn As Range
Application.ScreenUpdating = 0: Application.EnableEvents = 0: Application.Calculation = xlCalculationManual
  For Each rAn In Selection.SpecialCells(xlCellTypeVisible)
    rAn.NumberFormat = "dd/mm/yyyy"
    rAn = Trim(rAn.Text)
    rAn = DateValue(rAn)
  Next rAn
Application.ScreenUpdating = 1: Application.EnableEvents = 1: Application.Calculation = xlCalculationAutomatic
End Sub

Upvotes: 0

Neil
Neil

Reputation: 41

Highlight column B / click Data / Text to Columns / select Delimited / click Finish (without a delimiter selected).

That should do it.

Upvotes: 4

Tim J aus M
Tim J aus M

Reputation: 1

The easiest thing ist to just run a calculation on the respective cells:

=A1-0

And the copy & paste the values :-)

Upvotes: 0

PynkRabbit
PynkRabbit

Reputation: 1

I have experienced this before - in the Formula's Ribbon > Calculation Box > Calculation Options drop down check to make sure 'Automatic' is selected. Usually when 'Manual' is selected mass formatting changes like that wont take effect unless you manually refresh cell (i.e. select and press enter)

Upvotes: 0

datatoo
datatoo

Reputation: 2059

It appears you have AutoCorrect working for or against you. If you notice the small blue triangle in the bottom of row 20. Although smart tags were discontinued in 2010, so it may be this is not be your situation.

This is the Offce Link regarding AutoCorrect Buttons In Preferences>Proofing>AutoCorrect Options>Actions you may control additional actions and whether Excel is trying to correct your date input. I don't have a sample of your data to test with, but fiddling in those preferences may get excel to not help so much.

Upvotes: -1

Storm
Storm

Reputation: 682

Try this:

Sub MacroTest()

    Sheets("Sheet1").Select

    LastRangeRow = Cells.Find("*", after:=Range("A1"), searchdirection:=xlPrevious).Row

    Set Rng = Range("Sheet1!B2:B" & LastRangeRow)

    For Each c In Rng.Cells
       c.Select
       SendKeys "{F2}", True
       SendKeys "{ENTER}", True
       Selection.NumberFormat = "dd/mm/yyyy hh:mm:ss"
    Next

    Range("A1").Select
End Sub

Upvotes: 3

Related Questions