Hello guys,
I have 2 excel files:
1) My utility(that contains code which is run when a button is pressed) and
2) A forecasting file which has several columns starting with Project ID, Project MU Desription, Cluster and so on...
All these columns needs to be validated for the kind of data they hold.
Background: forecasting file already had validations incorporated through data validation list but our team is facing a lot of issue doing corrections. We want to created a macro for the people who prepare these sheets so that they can run it and check whether they have entered something incorrectly.
Main Purpose: Validations
(Purpose is to make sure that columns have specific entries, if not I am highlighting them in red color)
Roadblocks: Blank cells in between, selecting a range dynamically, find method issues
Explanation of code:
Code in green: Is the one that ask the user to open the file on which the macro should run. In here is a function that extracts the filename and I am using this filename to move between sheets. This way I can use the same macro for several other workbooks.
Code in pink: Few lines which open the Project worksheet under the workbook and remove all filters, unhide rows and columns
Code in blue:
Starts with Find method (taking what values through an array because some sheets can have different column names)
So this macro
> finds the Project MU Description column
> then uses a shift ctrl down to select all the values including the header in that column.
> then Copy selects values and pastes them in a new sheet2 of my utlity itself.
> goes back to the file using
> using shift ctrl down I go further down under the same column because it has values from which drop down lists (for data validations) are prepared and I am using these only to validate the values. (Every column has data validation list being prepared from the values right below them at say X column onwards, but from which cell the value starts in not specific)
> then it Copies these values to sheet2 again under a header primary
> comparison is run on Sheet2 itself and highlighted with green or red color.
> The values in B column are then copied back to the file which was being validated by using another Find method.
Issues are:
1) If it doesn't find Project MU Column it gives run time error and copies a random column name till the end of excel sheet - is there way that it moves ahead to search next column that is Cluster or anything else...?
2) If imacro finds a blank cell in between while doing a shift ctrl down, it copies incomplete data to sheet2 of my utlity, comes back and again copies wrong data, though validations runs but it's all incorrect. values picked up for comparison are incorrect - is there a way to skip these blanks and still copy all the values present in a column ?
3) Am I using find method incorrectly ? - please alter
Is my approach incorrect to perform this entire thing ?
Should I instead hardcode what needs to be found in a column ?
Please help.
Code:
Option Compare Text
___________________________
Function ExtractFileName(filespec) As String
' Returns a filename from a filespec
Dim x As Variant
x = Split(filespec, Application.PathSeparator)
ExtractFileName = x(UBound(x))
End Function
_______________________________
Sub loopyarray()
''''''This section opens the required filename''''''
''''''************************************''''''
Dim filenames As Variant
Dim UseName As String
' set the array to a variable and the True is for multi-select
filenames = Application.GetOpenFilename("Excel Files (*.xls; *.xlsx; *.xlsm),*.xls;*.xlsx;*.xlsm", , , , False)
Workbooks.Open filenames
UseName = ExtractFileName(filenames)
''''''The section above opens the workbook where validations need to be performed''''''
''''''*****Project MU Description validation starts*****''''''
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Project").Select
Cells.Select
Selection.EntireColumn.Hidden = False
Selection.EntireRow.Hidden = False
Rows("3:3").Select
Selection.AutoFilter
Range("A1").Select
Dim vWhat As Variant
On Error Resume Next
For Each vWhat In Array("Project MU Description", "Project MU & MU Description")
Err.Clear
Cells.Find(What:=vWhat, _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate
If Err.Number = 0 Then
'MsgBox Prompt:="Column found for being validated", _
' Title:="Forecasting Validations"
End If
Next vWhat
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Utility_forecasting_validations.xlsm").Activate
Sheets("Sheet2").Select
Range("B2").Select
ActiveSheet.Paste
Columns("B:B").EntireColumn.AutoFit
Windows(UseName).Activate
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("Utility_forecasting_validations.xlsm").Activate
Range("D3").Select
ActiveSheet.Paste
Range("D2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Primary"
Columns("D:D").Select
Columns("D:D").EntireColumn.AutoFit
Dim RowRandom, RowPrimary, RandomCount, PrimaryCount, PrimaryEnd As Integer
'
'
With Worksheets("Sheet2")
Range("B3").Select
RandomCount = Range(Selection, Selection.End(xlDown)).Count
Range("D3").Select
PrimaryCount = Range(Selection, Selection.End(xlDown)).Count
PrimaryEnd = PrimaryCount + 1
For RowRandom = 1 To RandomCount
For RowPrimary = 1 To PrimaryCount
If .Cells(RowRandom + 2, 2) = .Cells(RowPrimary + 2, 4) Then
.Cells(RowRandom + 2, 2).Interior.ColorIndex = 4
Exit For
End If
Next RowPrimary
If RowPrimary = PrimaryEnd Then
.Cells(RowRandom + 2, 2).Interior.ColorIndex = 3
End If
Next RowRandom
End With
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows(UseName).Activate
Dim vsWhat As Variant
On Error Resume Next
For Each vsWhat In Array("Project MU Description", "Project MU & MU Description")
Err.Clear
Cells.Find(What:=vsWhat, _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate
If Err.Number = 0 Then
'MsgBox Prompt:="Column found for pasting validations", _
' Title:="Forecasting Validations"
End If
Next vsWhat
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Windows("Utility_forecasting_validations.xlsm").Activate
Cells.Select
Range("A13").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
ActiveWindow.SmallScroll Down:=-3
ActiveWindow.LargeScroll Down:=-1
ActiveWindow.SmallScroll Down:=1
Sheets("Utility").Select
Range("C3").Select
Windows(UseName).Activate
Range("A1").Select
'''''''*****************Project MU Description Validation complete**********************''''''
End Sub
Regards!
I have 2 excel files:
1) My utility(that contains code which is run when a button is pressed) and
2) A forecasting file which has several columns starting with Project ID, Project MU Desription, Cluster and so on...
All these columns needs to be validated for the kind of data they hold.
Background: forecasting file already had validations incorporated through data validation list but our team is facing a lot of issue doing corrections. We want to created a macro for the people who prepare these sheets so that they can run it and check whether they have entered something incorrectly.
Main Purpose: Validations
(Purpose is to make sure that columns have specific entries, if not I am highlighting them in red color)
Roadblocks: Blank cells in between, selecting a range dynamically, find method issues
Explanation of code:
Code in green: Is the one that ask the user to open the file on which the macro should run. In here is a function that extracts the filename and I am using this filename to move between sheets. This way I can use the same macro for several other workbooks.
Code in pink: Few lines which open the Project worksheet under the workbook and remove all filters, unhide rows and columns
Code in blue:
Starts with Find method (taking what values through an array because some sheets can have different column names)
So this macro
> finds the Project MU Description column
> then uses a shift ctrl down to select all the values including the header in that column.
> then Copy selects values and pastes them in a new sheet2 of my utlity itself.
> goes back to the file using
> using shift ctrl down I go further down under the same column because it has values from which drop down lists (for data validations) are prepared and I am using these only to validate the values. (Every column has data validation list being prepared from the values right below them at say X column onwards, but from which cell the value starts in not specific)
> then it Copies these values to sheet2 again under a header primary
> comparison is run on Sheet2 itself and highlighted with green or red color.
> The values in B column are then copied back to the file which was being validated by using another Find method.
Issues are:
1) If it doesn't find Project MU Column it gives run time error and copies a random column name till the end of excel sheet - is there way that it moves ahead to search next column that is Cluster or anything else...?
2) If imacro finds a blank cell in between while doing a shift ctrl down, it copies incomplete data to sheet2 of my utlity, comes back and again copies wrong data, though validations runs but it's all incorrect. values picked up for comparison are incorrect - is there a way to skip these blanks and still copy all the values present in a column ?
3) Am I using find method incorrectly ? - please alter
Is my approach incorrect to perform this entire thing ?
Should I instead hardcode what needs to be found in a column ?
Please help.
Code:
Option Compare Text
___________________________
Function ExtractFileName(filespec) As String
' Returns a filename from a filespec
Dim x As Variant
x = Split(filespec, Application.PathSeparator)
ExtractFileName = x(UBound(x))
End Function
_______________________________
Sub loopyarray()
''''''This section opens the required filename''''''
''''''************************************''''''
Dim filenames As Variant
Dim UseName As String
' set the array to a variable and the True is for multi-select
filenames = Application.GetOpenFilename("Excel Files (*.xls; *.xlsx; *.xlsm),*.xls;*.xlsx;*.xlsm", , , , False)
Workbooks.Open filenames
UseName = ExtractFileName(filenames)
''''''The section above opens the workbook where validations need to be performed''''''
''''''*****Project MU Description validation starts*****''''''
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Project").Select
Cells.Select
Selection.EntireColumn.Hidden = False
Selection.EntireRow.Hidden = False
Rows("3:3").Select
Selection.AutoFilter
Range("A1").Select
Dim vWhat As Variant
On Error Resume Next
For Each vWhat In Array("Project MU Description", "Project MU & MU Description")
Err.Clear
Cells.Find(What:=vWhat, _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate
If Err.Number = 0 Then
'MsgBox Prompt:="Column found for being validated", _
' Title:="Forecasting Validations"
End If
Next vWhat
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Utility_forecasting_validations.xlsm").Activate
Sheets("Sheet2").Select
Range("B2").Select
ActiveSheet.Paste
Columns("B:B").EntireColumn.AutoFit
Windows(UseName).Activate
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("Utility_forecasting_validations.xlsm").Activate
Range("D3").Select
ActiveSheet.Paste
Range("D2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Primary"
Columns("D:D").Select
Columns("D:D").EntireColumn.AutoFit
Dim RowRandom, RowPrimary, RandomCount, PrimaryCount, PrimaryEnd As Integer
'
'
With Worksheets("Sheet2")
Range("B3").Select
RandomCount = Range(Selection, Selection.End(xlDown)).Count
Range("D3").Select
PrimaryCount = Range(Selection, Selection.End(xlDown)).Count
PrimaryEnd = PrimaryCount + 1
For RowRandom = 1 To RandomCount
For RowPrimary = 1 To PrimaryCount
If .Cells(RowRandom + 2, 2) = .Cells(RowPrimary + 2, 4) Then
.Cells(RowRandom + 2, 2).Interior.ColorIndex = 4
Exit For
End If
Next RowPrimary
If RowPrimary = PrimaryEnd Then
.Cells(RowRandom + 2, 2).Interior.ColorIndex = 3
End If
Next RowRandom
End With
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows(UseName).Activate
Dim vsWhat As Variant
On Error Resume Next
For Each vsWhat In Array("Project MU Description", "Project MU & MU Description")
Err.Clear
Cells.Find(What:=vsWhat, _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate
If Err.Number = 0 Then
'MsgBox Prompt:="Column found for pasting validations", _
' Title:="Forecasting Validations"
End If
Next vsWhat
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Windows("Utility_forecasting_validations.xlsm").Activate
Cells.Select
Range("A13").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
ActiveWindow.SmallScroll Down:=-3
ActiveWindow.LargeScroll Down:=-1
ActiveWindow.SmallScroll Down:=1
Sheets("Utility").Select
Range("C3").Select
Windows(UseName).Activate
Range("A1").Select
'''''''*****************Project MU Description Validation complete**********************''''''
End Sub
Regards!
