How to Fix a Kernel32.DLL Error? : Data validation in VBA

Data validation in VBA

Hello,

I'm struggling with a bit of coding.
I want some coding that will check a spreadsheet and do the following:

Change column G to sentence case (PROPER)
Change amounts in column I to be currency format and have 2 decimal places
Change dates of birth in column J to dd/mm/yyyy format and highlight any whose age is less than 12 or more than 75 (I usually use =ROUNDDOWN(($A$1-B1)/365.25,0) where A1 is today's date and B1 is the DOB)
Change column L to capital letters case (UPPER)
Column M shows a percentage e.g.
5% - i want it just to show 5 but with no % sign (maybe by changing it to general format and multiplying by 100)
The contents of column N are column I multiplied by column M.
If the result of this sum is equal to zero, I would like the cell to be blank.
This entry also needs to be currency format to 2 decimal places.
Finally, when all of this is done, I would like to highlight the whole sheet, copy the data and Paste Values to get rid of any formulas.

I can do 2 or 3 of the easier bits myself but my coding skils are next to nothing!

Anwsers to the Problem Data validation in VBA

Download SmartPCFixer to Fix It (Free)

Test this workbook:
http://www.mediafire.com/?8a2vj0ezg0obb4p
MasterProcessor.xlsm
28.27KB
Basic instructions (details in the workbook itself): create a folder just for it to use and put it in the folder. COPY data files you want to process into that same folder. Open the file and click the "do it..." button.
Examine a couple of test workbooks to make sure I got everything right.
Here's the actual working code that goes into a regular code module ( see
http://www.contextures.com/xlvba01.html#Regular ) :
Sub ProcessWorksheets()
'we define a lot of Const values so that if the worksheets layouts
'change in the future, you can adapt the code more easily.
'
'assumed that row 1 contains labels we need not mess with
'so set first row to examine when we do it cell by cell as 2
Const firstDataRow = 2
Const dobCellAddress = "B1" ' address of cell with Date of Birth
Const properCol = "G" ' column to change text to PROPER format.
Const currencyCol_1 = "I" ' this column to be formatted as currency
Const dobCol = "J" ' column with dob's in it; needs conditional formatting
'these are the limits for NOT Formatted ages
Const minAge = 12
Const maxAge = 75
Const upperCol = "L" ' column to change text to all UPPERCASE
Const mathCol = "N" ' where result is I# * M# 2 decimals, blank if zero
Const multiply_1Col = "I"
Const multiply_2Col = "M" ' also to be converted to whole number but used as % in math
'end of user re-definable constant values
'
Const fileTypes = "*.xls*"
Const currencyFormat = "$#,##0.00"
'you could use this if you want Accounting format to align the decimal points
' Const currencyFormat = "_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]* ""-""??_);_(@_)"
Const percentToNumberFormat = "0.00"
Const theDateFormat = "dd/mm/yyyy"
Dim basicPath As String
Dim nextFile As String
Dim otherWB As Workbook
Dim anyWS As Worksheet
Dim anyRange As Range
Dim anyCell As Range
Dim currentRow As Long
Dim theirAge As Integer

'this just cleans up the worksheet in this workbook for reporting
currentRow = Sheet1.Range("B" & Rows.Count).End(xlUp).Row
If currentRow > 1 Then
    Sheet1.Range("B2:B" & currentRow).ClearContents
End If
'now on to the actual job at hand
basicPath = ThisWorkbook.Path
If Right(basicPath, 1) <> Application.PathSeparator Then
    basicPath = basicPath & Application.PathSeparator
End If
'"seed" the nextFile name entry
nextFile = Dir$(basicPath & fileTypes)
'begin working with the other files in the folder
Application.ScreenUpdating = False ' really speeds things up
Do While nextFile <> ""
    'don't try to process this workbook!
    If nextFile <> ThisWorkbook.Name Then
      Application.DisplayAlerts = False
      'opens the workbook and updates any links and DOES NOT open as read only.
      Set otherWB = Workbooks.Open(basicPath & nextFile, True, False)
      Application.DisplayAlerts = True
      'this works through all worksheets in the other workbook
      For Each anyWS In otherWB.Worksheets
        'set up PROPER() formatting
        Set anyRange = anyWS.Range(properCol & firstDataRow & _
         ":" & anyWS.Range(properCol & Rows.Count).End(xlUp).Address)
        For Each anyCell In anyRange
          'while there is UCASE() and LCASE() there is no PCASE()
          'but this works as if there were!
          anyCell.Formula = StrConv(anyCell, vbProperCase)
        Next ' end of PROPER looping
        'set up currency formatting w/2 decimal places
        anyWS.Columns(currencyCol_1 & ":" & currencyCol_1).NumberFormat = currencyFormat
        '
        'here we deal with column J, this is the DOB column
        'mark less than 12 yrs old or greater than 75 yrs old
        '
        Set anyRange = anyWS.Range(dobCol & firstDataRow & _
         ":" & anyWS.Range(dobCol & Rows.Count).End(xlUp).Address)
        anyRange.NumberFormat = theDateFormat
        For Each anyCell In anyRange
          theirAge = Year(Now()) - Year(anyCell)
          If Month(anyCell) > Month(Now()) Then
            theirAge = theirAge - 1 ' haven't reached birthday this year
          End If
          'currently both set to same color, but set up so that
          'you could choose colors based on which end of the spectrum
          'they fall into.
          Select Case theirAge
            Case Is < minAge
              anyCell.Interior.ColorIndex = 3 ' or numeric 3=Red, 6 = Yellow, 8 = teal
            Case Is > maxAge
              anyCell.Interior.ColorIndex = 3 ' or numeric 3=Red, 6 = Yellow, 8 = teal
            Case Else
              anyCell.Interior.ColorIndex = xlNone
          End Select
        Next 'end of dob processing
        'set up UPPER() formatting
        Set anyRange = anyWS.Range(upperCol & firstDataRow & _
         ":" & anyWS.Range(upperCol & Rows.Count).End(xlUp).Address)
        For Each anyCell In anyRange
          anyCell.Formula = UCase(anyCell.Value)
        Next ' end UPPER conversion loop
        'convert % to whole numbers
        Set anyRange = anyWS.Range(multiply_2Col & firstDataRow & _
         ":" & anyWS.Range(multiply_2Col & Rows.Count).End(xlUp).Address)
        anyRange.NumberFormat = percentToNumberFormat
        'calculate values for column N
        'these may be empty to start with, so need to figure out
        'which to calculate based on entries in I and M
        currentRow = anyWS.Range(multiply_1Col & Rows.Count).End(xlUp).Row
        If anyWS.Range(multiply_2Col & Rows.Count).End(xlUp).Row > currentRow Then
          currentRow = anyWS.Range(multiply_2Col & Rows.Count).End(xlUp).Row
        End If
        Set anyRange = anyWS.Range(mathCol & firstDataRow & _
         ":" & mathCol & currentRow)
        'format the column as currency w/2 decimals
        anyWS.Columns(mathCol & ":" & mathCol).NumberFormat = currencyFormat
        For Each anyCell In anyRange
          currentRow = anyCell.Row
          anyCell = _
           Round(anyWS.Range(multiply_1Col & currentRow) * anyWS.Range(multiply_2Col & currentRow), 2)
          If anyCell = 0 Then
            anyCell.ClearContents
          End If
        Next
        'final step is to make sure there are no formulas on the sheet
        anyWS.UsedRange.Formula = anyWS.UsedRange.Value
      Next ' end of loop through all worksheets
      'finished processing sheets; close the workbook with Save Changes
      otherWB.Close True
      Set otherWB = Nothing
      Sheet1.Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = nextFile
    End If 'end of test for this workbook as newFile
    nextFile = Dir$() ' get next found filename of proper type
Loop
MsgBox "All identified files have been processed.", vbOKOnly + vbInformation, "Task Completed"
End Sub

Don't Download Malware

Where are you getting the download?

There are malicious people who download valid copies of a popular download, modify the file with malicious software, and then upload the file with the same name. Make sure you are downloading from the developer's web page or a reputable company.

Cancel or deny any automatic download

Some sites may automatically try start a download or give the appearance that something needs to be installed or updated before the site or video can be seen. Never accept or install anything from any site unless you know what is downloading.

Avoid advertisements on download pages

To help make money and pay for the bandwidth costs of supplying free the software, the final download page may have ads. Watch out for anything that looks like advertisements on the download page. Many advertisers try to trick viewers into clicking an ad with phrases like "Download Now", "Start Download", or "Continue" and that ad may open a separate download.