PDA

View Full Version : Solved: Macro coding help.



gatorcc
06-26-2009, 12:25 PM
Hello,
I have a report that is generated daily that I end up having to go in and adjust about 1,700 cells to either date (mm/dd/yyyy), General or Number (#,###.##). The problem is all three of these format types are in the same column. So what I am needing help with is an IF statement that goes something like this:
If Column G has a row of text that includes *AMT* or *Balance* format column f on the same row as Number (#,###.##)
If Column G has a row of text that includes *Date* format column f on the same row as Date (mm/dd/yyyy)
If Column G has a row of text that included *CHARGE-CODE* or *Percent* format column f on the same row as General

Unfortunately the report cannot be changed so my only option is to manually make these every day.
Any suggestions would be great.

p45cal
06-26-2009, 12:43 PM
try running this macro with the sheet you want adjusting being the active sheet (untested):Sub blah()
With ActiveSheet
For Each cll In Intersect(.UsedRange, .Columns("G")).Cells
If InStr(cll.Value, "AMT") > 0 Or InStr(cll.Value, "Balance") > 0 Then .Cells(cll.Row, 6).NumberFormat = "#,###.##"
If InStr(cll.Value, "Date") > 0 Then .Cells(cll.Row, 6).NumberFormat = "mm/dd/yyyy"
If InStr(cll.Value, "CHARGE-CODE") > 0 Or InStr(cll.Value, "Percent") > 0 Then .Cells(cll.Row, 6).NumberFormat = "General"
Next cll
End With
End Sub

gatorcc
06-30-2009, 06:07 AM
Thank you! This worked very well. I tweaked a couple things to make it fit the report better but overall it worked great.

My next challenge is to add to this code to find REPORTID in column C and delete that row and the next 5 rows.

Thanks once again!