Excel Macro not working correctly HELP!!!

[EdHunter]

I recorded the following code using the Macro recorder (Have cleaned it up to remove the pointless bits like scrolling etc)

The problem is that it does not actually work when I run the macro (And yes I tried it prior to cleaning it up - Or rather re-recorded it - and this has no effect on it working.

The code is as follows:



Sub Converttodates()
Range("W11").Select
ActiveCell.FormulaR1C1 = "1"
Range("W11").Select
Selection.Copy
Range("B:B").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = "m/d/yyyy"
Range("W11").ClearContents
End Sub


This code is just to ensure that dates which have been pasted into the spreadsheet are recognised as such by Excel.

When ever I manually do the task it works flawlessley but the Macro seems to skip some of the cells within the range.

[deyken]

Hi There,

From briefly looking over the macro code, it seems that it recorded your copy/paste function to one cell only and not the entire range you would initially use. Simply look at the "W11" range and expand it (format: "Col#:Row#") to cover the entire range (rows and columns as required).

That should work.

[Bob Phillips]

This works fine for me (but it does update all the blanks as well)



Sub Converttodates()
Range("W11").Value = 1
Range("W11").Copy
With Range("B:B")

.PasteSpecial Paste:=xlPasteAll, _
Operation:=xlMultiply, _
SkipBlanks:=False, _
Transpose:=False
.NumberFormat = "m/d/yyyy"
End With
Application.CutCopyMode = False
Range("W11").ClearContents
End Sub

[EdHunter]

Xld - I have attached a copy of the spreadsheet which I am having the problems with (All private data removed) - I tried your altered code which still didn't work properly for me,

The macro is saved in the workbook called "Cleanup" - As you will see when you run it - by stepping in (or at least as I see when I do so!) The actual code works without a problem there are just some of the date cells which it seems to totally ignore - Again if I do the same task manually it works fine just not within a Macro apparently


I have included both my code and your code in the macro - They both skip the same cells!.

Excel DOES recognise all the cells with dates in as having dates in the problem is doing any calculations on these cells - So Cols N-U for wether excel is happy or not.


Any help on this would be appreciated.

[Bob Phillips]

Sub cleanup()
Dim myrange As Range, Lastrow As Long
Dim i As Long
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''
'This will clean up the data copied in from the KPI Query in the Workflow log. '
'It will start by ensuring Excel recognises the dates by doing a "paste special/ Multiply by 1" '
'It inserts a lookup forula to find out the "Category" '
'It then clears the contents of all the cells which are in the date columns without dates in them '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''

Columns("B").TextToColumns Destination:=Range("B1"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
Tab:=True, _
FieldInfo:=Array(1, 4), _
TrailingMinusNumbers:=True
For i = 5 To 11

Columns(i).TextToColumns Destination:=Cells(1, i), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
Tab:=True, _
FieldInfo:=Array(1, 4), _
TrailingMinusNumbers:=True
Next i

Range("E:K,B:B").NumberFormat = "m/d/yyyy"

Lastrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Range("N2:U2").AutoFill Destination:=Range("N2:U" & Lastrow)

For Each myrange In Sheets("Sheet1").Range("A2:A" & Lastrow)

'myrange.Offset(0, 3).FormulaR1C1 = "=VLOOKUP(RC[-1],bert,3,FALSE)"

If IsDate(myrange.Offset(0, 4)) = False Then myrange.Offset(0, 4).ClearContents
If IsDate(myrange.Offset(0, 5)) = False Then myrange.Offset(0, 5).ClearContents
If IsDate(myrange.Offset(0, 6)) = False Then myrange.Offset(0, 6).ClearContents
If IsDate(myrange.Offset(0, 7)) = False Then myrange.Offset(0, 7).ClearContents
If IsDate(myrange.Offset(0, 8)) = False Then myrange.Offset(0, 8).ClearContents
If IsDate(myrange.Offset(0, 9)) = False Then myrange.Offset(0, 9).ClearContents
If IsDate(myrange.Offset(0, 10)) = False Then myrange.Offset(0, 10).ClearContents

Next myrange
End Sub

[EdHunter]

Thanks XLD, not sure what the code is doing but it seems to work great,

Just one last question - How do I stop it filling in to row 65536 - i only need it to sort the dates down as far as the last filled cell in col "A".

Again thanks for your help it has saved me going even more bald through pulling out my hair.