Solved: Formatting Data Imported From Web

[BigDawg15]

Have searched forums and web with no luck. In the attached workbook, I import some data from web. Once imported, I have a macro that will operate depending on formulas in column G and dates in column A.

Once the data is imported the formula will not pick up the date due to the formatting that is being pulled in from the import. If I manually enter the date it will work.

I am looking for any solution (e.g. macro, formula, helper columns etc) as I just cannot figure this out.

In attached workbook, row 5 has the date manually entered so you can see what I am talking about.

Any help is appreciated. I have tried a couple trim and len formulas in extra columns with no luck but that may be due to my limited knowledge.

Thanks in advance,

BigDawg15

VBMAX BigDawg15.xlsm

[SamT]

Put this Underneath your Worksheet_Activate Procedure.

Alternately, change its name, Delete the "If Intersect" line, and call it from your Worksheet_Activate Procedure
[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
'http://www.vbaexpress.com/forum/showthread.php?t=45625
Dim Cel As Range
Dim LastRowInA As Long
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'Do nothing if the change is not in Column A
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub

LastRowInA = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For Each Cel In Range("A2" & ":A" & LastRowInA)
If IsDate(Cel) Then GoTo Skip
Cel = CDate(Mid(Cel, 5))
Skip:
Next Cel
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
[/VBA]

You have so much going on that the Change event was triggered by something every time this procedure changed a cell to a date.
This meant that the procedure would rerun after it changed the first cell.

The second time around the first cell was in a Date format already and it would error out.

By adding the If Then Goto Skip, it skipped Cells that were already changed.

BTW, you may want to add the Display Alerts and Screen Updating sections to all your procedures.

[BigDawg15]

SamT,

Thank you for the response and solution.

You have so much going on that the Change event was triggered by something every time this procedure changed a cell to a date.
This meant that the procedure would rerun after it changed the first cell.

The second time around the first cell was in a Date format already and it would error out.

By adding the If Then Goto Skip, it skipped Cells that were already changed.

For clarification, are you saying my macro was causing the problems with yours?

BTW, you may want to add the Display Alerts and Screen Updating sections to all your procedures.

Good advice and I usually do include these in all my codes.

If you think my code could be cleaned up, please advise. I can use any help to clean it up.

Thanks again,

Mike

[SamT]

are you saying my macro was causing the problems with yours?

I think so, but WhaddaINo?

In the WorkSheet_Activate Procedure, I don't see what this code is used for. I see that it Activates the first Date Cell in Column "A", but why?
[VBA]ActiveSheet.Range("A1").Activate
ActiveSheet.Cells.Find(What:=Date, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
[/VBA]
More short posts to follow

[SamT]

You have the same code above in the WorkBook_open Procedure. Yeah those would mess with any Procedure using an active sheet.

[SamT]

I suggest removing module Module1 and Module11 as they contain duplicate declarations and I could not find where any of their procedures were called.

I would also delete the Code above from both locations. Make a copy of the workbook first. :0

[BigDawg15]

SamT,

Thanks again for your input.

I think so, but WhaddaINo?

A heck of a lot more than me.

In the WorkSheet_Activate Procedure, I don't see what this code is used for. I see that it Activates the first Date Cell in Column "A", but why?


VBA:

ActiveSheet.Range("A1").Activate ActiveSheet.Cells.Find(What:=Date, After:=ActiveCell, _ LookIn:=xlFormulas, LookAt:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate

This goes to the first current date in column A.

Thanks again for your help.

Mike

[SamT]

Where are you using that (activated) Cell? That is what I could not find.

[BigDawg15]

Sheet1. If the cells in column A are formatted correctly, the cursor will go to the first instance of the current date. I have it all working correctly in my workbook.

Thanks again,

Mike

[SamT]

Understand, You want to position the cursor.

[BigDawg15]

You got it. Sorry if I didn't explain it properly.

Thanks again for the quick response and solution. Very much appreciated.

Mike

[snb]

I'd prefer:

[vba]Private Sub Worksheet_Activate()
On Error Resume Next
c00 = "No games today"
c00 = Join(Application.Index(ThisWorkbook.Sheets("Sheet1").Columns(1).Find(Date + 1, , xlFormulas, 1).Offset(, 1).Resize(, 2).Value, 1, 0), " _ ")
MsgBox c00
End Sub[/vba]

No need for column G.

[BigDawg15]

SNB,

Thanks for the code. Can it be editted to display all games for today's date. It currently only displays first game. If it can be done and I don't need to use column G, that would be awesome.

Thanks in advance,

Mike

[snb]

You can simply use autofiler.

[BigDawg15]

snb,

Created macro using autofilter technique. Way too easy!!

Thanks for the suggestion.

Mike