Replies

The problem was completly my fault.

I was changing the code in a part of the module that I had already run. So every time I ran the second part of my code the dates never changed ’cause, duh!, the code was in the module that I had already run

Oops.. I fogot to thank you guys for all of you help!

Thank you!

I have found the error of my ways.. I am to embarrased to tell you what I did, but I will say it wasn’t because of text in my column…

I hate when I make stupid mistakes, they are so time consuming!

I can’t use the last 2 numbers in the year because 2002 ends on 12/30/02 (Therefore anything done on or before 12/30/02 falls in 2002)
2003 starts on 12/31/03 and ends on 12/29/03. Anything on or after 12/30/03 falls in 2004.

I just reentered the formula directly into a cell and it works fine, it seems to be a problem in VBA. Does the DATEVALUE work differently in VBA?

Directly in Cell:
=IF(S2DATEVALUE(“12/29/2003″),”2004″,”2003”))

VBA:
Range([t2], [t2].End(xlDown)).Offset(0, 2).FormulaR1C1 = _
“=IF(RC[-3]DATEVALUE(“”12/29/2003″”),””2004″”,””2003″”))”

Well, I am now finding inaccurate data.

The fomula I am using in VBA is:
“=IF(RC[-3]DATEVALUE(“”12/29/2003″”),””2004″”,””2003″”))”

I have the following dates reporting as 2004:
1/6/03
1/22/03
12/30/02

12/28/02 is coming back as 2004…

Hmmm, anyone have any help?

That worked!

Thank you so very much!

John/Andrew..

Thank you both so much for your help.

I will create a modified spreasheet to post so I can show you a better example of what I am doing…

Thanks again for your helpl…!

John,

I was posting at the same time as you were.

1. I think my problem is the sorting/subtotaling
2. So I should add column B first and then put the left characters there
3. Yeah, they do seem strange. I’m putting the spreadsheet into a more readable format, I’ll relook at this part to make sure it is effiecient.
4. Screenupdating is turned off in the sub that runs this one and then turned back on at the end
5. The only forumla on this page is the left 3 characters

I posted what i have so far in a post above. I wish I could post everything including the worksheet I start off with, but it would take me forever to alter it enough for public view!

Just to give you an idea of what I am doing – We need a report formatted in a certain way. I download an excel file from one of our databases and then have to do A LOT of additional work to make it what the users need. That is why I am doing it in VBA. This is something that get’s done 4 times a month and it will save a lot of time to automate it.

Yup, I sure did.

Also – I had to add Sheets(“Billinger”).select before the For Each or it just runs the code on the worksheet it is on.

I downloaded your sheet and copied and pasted right into my code. Here is what it did

It did the first two sets of items: The concatenation & cell cutting but when it got to the subtotaling, it just kept repeating it on the Billinger sheet. Is this because I don’t have addition periods in the other parts of my code?

John/Andrew….

Maybe you could help me with another question…. John – I have used your solution of cycling through the worksheets. Here is the code I have currently:

Sub CreateSDFDetailSheets()

Application.StatusBar = “Creating SDF Detail”

‘Cycles through the Billinger, Master & Deluca worksheets to apply formatting & changes
Sheets(“Billinger”).Select
Dim ws As Worksheet
For Each ws In Worksheets(Array(“Billinger”, “Masterson”, “DeLuca”))
ws.Activate
With ActiveSheet
‘Get’s the Risk
.Range([T2], [T2].End(xlDown)).Offset(0, 1).Formula = “=RIGHT(T2,3)”

‘Column Alignment
.Columns(“U:U”).Copy
.Columns(“U:U”).PasteSpecial Paste:=xlValues
.Columns(“U:U”).Cut
.Columns(“B:B”).Insert shift:=xlToRight
.Columns(“U:U”).Delete
.Columns(“T:T”).Cut
.Columns(“J:J”).Insert shift:=xlToRight
.Columns(“D:D”).Cut
.Columns(“c:c”).Insert shift:=xlToRight
.Columns(“t:T”).Cut
.Columns(“F:F”).Insert shift:=xlToRight

‘Sort by FBC & Risk
Range(“B1”).Value = “Risk”
Range(“A1”).Select
Selection.sort Key1:=Range(“A2”), Order1:=xlAscending, Key2:=Range(“B2”) _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom

‘Subtotals
Selection.Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(1), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

‘Adding SDF Name & Period to Cell A1
Range(“A1”).Value = “Period ” & Period & ” Forecast”

‘Formatting
Cells.Font.Name = “Arial”
Cells.Font.Size = 10
Rows(“1:1”).Font.Bold = True
Rows(“1:1”).WrapText = True
Rows(“1:1”).HorizontalAlignment = xlCenter
Cells.EntireColumn.autofit

‘Adding SDF Name & Period to Cell A1
Range(“A1”).Value = “Period ” & Period & ” Forecast”

‘Freeze Panes
Range(“F2”).Select
ActiveWindow.FreezePanes = True
End With
Next ws

End Sub

This is called from another sub that has the Application.ScreenUpdating = False in it.

Now when I run this code it takes two minutes to complete. (I timed it )

Each of these worksheets only has approximately 60 lines in it. Can you tell me if I have any extraneous code that is causing the slow-down (Also, the screen turns white during this process)

-Leslie

Hi Andrew,

I tried your code, but it didn’t seem to work for me. It doesn’t cycle through the worksheets, it keeps updating the same worksheet over and over.

-Leslie

That did the trick!

Thank you so much my helpful friend

John,

That doesn’t seem to be working for me. It makes the same changes to the first worksheet 3 times..

Here is what I have:

Dim ws As Worksheet
For Each ws In Sheets(Array(“Billinger”, “Masterson”, “DeLuca”))
Range([U2], [T2].End(xlDown).Offset(0, 1)).Formula = “=RIGHT(T2,3)”

Columns(“U:U”).Copy
Columns(“U:U”).PasteSpecial Paste:=xlValues

‘Moves Column U to Column B
Columns(“U:U”).Cut
Columns(“B:B”).Insert shift:=xlToRight

Columns(“U:U”).Delete

Columns(“T:T”).Cut
Columns(“J:J”).Insert shift:=xlToRight

Next ws

What code does it seem I have missed?

Thank you for all your help..

Thank you,

Those short codes, worked great, and I can understand and apply them in the future!

Thank you John! You are my savior for the day

I have a question about:

Set rngTextCells = ActiveSheet.Columns(“U”).SpecialCells(xlCellTypeConstants, 2)

What does .SpecialCells(xlCellTypeConstants, 2) do?

Thanks again!