Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if data has already been formatted.
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<=LastFri day(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,F ALSE),""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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if data has already been formatted.
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" wrote: 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<=LastFri day(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,F ALSE),""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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if data has already been formatted.
Thank you Mike.
I will try it this way. Cheer Juan C "Mike H" wrote: 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" wrote: 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<=LastFri day(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,F ALSE),""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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I check if a cell is formatted as a date in Excel VB? | Excel Worksheet Functions | |||
Parsing formatted data | Excel Discussion (Misc queries) | |||
the format data - not formatted | New Users to Excel | |||
Importing Formatted Data | Excel Discussion (Misc queries) | |||
Converting 'General' formatted cells to Text formatted cell using. | Excel Worksheet Functions |