Check if data has already been formatted.

[Juan Correa]

Hello,
I have a sub() as part of a larger set of macros.
This particular sub() will take the raw data pulled from an in-house
reporting system and format it to suit our needs (adding a couple of columns
with formulas to facilitate pivot table creation).

This is the code as I have it so far:

Sub FormatData()
Application.ScreenUpdating = False
ActiveWindow.DisplayGridlines = False

' Declarations
Dim DataWks As Worksheet
Dim LastRow As Long
Dim LastCol As Long

' Set the DataWks variable
Set DataWks = Worksheets("Data")


' Make sure there is Data to be formatted
On Error Resume Next
If WorksheetFunction.CountA(DataWks.Cells) = 0 Then
MsgBox "SalesForce data" & vbNewLine & "Needs to be pasted" _
& vbNewLine & "Into the Data Worksheet" & vbNewLine & "Before
Formatting", vbCritical, "Warning!"

Else

' Start Formatting
With DataWks
.Select
LastRow = ActiveSheet.UsedRange.Rows.Count - 6
LastCol = Range("IV1").End(xlToLeft).Column

' Create the "Period" Column
.Cells(1, LastCol).Copy
.Cells(1, LastCol + 1).PasteSpecial Paste:=xlPasteFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Cells(1, LastCol + 1).WrapText = False
.Cells(1, LastCol + 1).Value = "Booked Month"
.Columns(LastCol + 1).AutoFit

' Populate the Month Column with new Monts
.Range(.Cells(2, LastCol + 1), .Cells(LastRow, LastCol +
1)).Formula = _

"=IF(MONTH(H2)=12,CurrentPeriod(H2),IF(H2<=LastFriday(H2),CurrentPeriod(H2),NextPeriod(H2)))"
.Range(.Cells(2, LastCol + 1), .Cells(LastRow, LastCol +
1)).NumberFormat = "MM-YYYY"

' Create the "Country" Column
.Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
.Cells(1, 1).Copy
.Cells(1, 2).PasteSpecial Paste:=xlPasteFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Cells(1, 2).WrapText = False
.Cells(1, 2).Value = "Country"

' Populate the Country Column with new Countries
.Range(.Cells(2, 2), .Cells(LastRow, 2)).Formula = _

"=IF(A2<>""PR"",VLOOKUP(A2,ctry_lookup,2,FALSE),IF(AND(A2=""PR"",C2=""""),VLOOKUP(A2,ctry_lookup,2,FALSE),""Distributors""))"
.Columns("B:B").AutoFit
End With

' Name the list range
LastCol = Range("IV1").End(xlToLeft).Column
Range("A1", Cells(LastRow, LastCol)).Name = "PivotData"

' Send me back to the Optioins Page
Sheets("Options").Activate

' Turn Screen Updating Back On
Application.ScreenUpdating = True
End If
Set DataWks = Nothing
End Sub

I have a check there to make sure that the raw data has been imported before
running the format routine -> It simply checks to see that the "Data"
worksheet isn't empty and returns a msgbox if it is.

Here is what I'm trying to accomplish:
I want to set up a second check that will stop the sub from formatting the
raw data if it already has been formatted before.

I tried adding a fourth variable like this:
Dim formatted as boolean
Making it true at the end of the sub() and then adding an ElseIf before the
formatting routine starts and checking to see if formatting is true or not...
But so far I have not been able to make it work.

If someone can point me in the right direction here, I'd appreciate it.

Thanks
Juan Correa

 

[Mike H]

Hi,

Right at the end of your sub before you go back to the options page you have
the 2 lines to which I've added a third

LastCol = Range("IV1").End(xlToLeft).Column
Range("A1", Cells(LastRow, LastCol)).Name = "PivotData"
Range("IV1").Value = "Formatted"

Now in the part of your sub where you check for data you can include a check
for the value in IV1. All you now have to do is when you delete old data
ensure IV1 is include for deletion

If Range("IV1").Value = "Formatted" Then
MsgBox "Already formatted"
Exit Sub
End If


If WorksheetFunction.CountA(DataWks.Cells) = 0 Then
MsgBox "SalesForce data" & vbNewLine & "Needs to be pasted" _
& vbNewLine & "Into the Data Worksheet" & vbNewLine & "Before


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Juan Correa]

Thank you Mike.
I will try it this way.

Cheer
Juan C