Monday, September 26, 2016

Data validation in VBA

Data validation in VBA

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:
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 ) :
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
          End If
        '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
  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.

Recommended Method to Fix the Problem: Data validation in VBA:

How to Fix Data validation in VBA with SmartPCFixer?

1. Click the button to download Error Fixer . Install it on your system.  Run it, and it will scan your computer. The junk files will be shown in the list.

2. After the scan is finished, you can see the errors and problems need to be repaired. Click Fix All.

3. The Repair part is finished, the speed of your computer will be much higher than before and the errors have been fixed. You can also use other functions in this software. Like dll downloading, junk file cleaning and print spooler error repair.

Related: How to Update & Download NVidia GeForce 6100/nForce 420 WHQL Certified driver v.178.13,How to Update & Download NVidia GeForce 7600 GS Video Driver v.295.75 Certified,How Can I Update & Download NVidia GeForce 820M Driver v.344.48 WHQL,[Solved] Download NVidia GeForce GTX 560M VGA Driver v.296.17 Certified,Way to Update & Herunterladen NVidia GeForce GT 130M Video Treiber v.295.75 Certified,How to Update & Download SONY SVE14A1X1RH Realtek Ethernet Driver,Where to Download SONY SVS13A2W9ES Bluetooth Driver (Intel) v.2.6 -,Best Way to Update & Download SONY VGN-CR203E Conexant HDAUDIO SoftV92 Data Fax Modem with SmartCP Setup Program v. driver,Best Way to Download SONY VGN-FE790G/N Wireless LAN Driver v.,Method to Update & Download SONY VGN-NR31Z/S Firmware Extension Parser Device v.,How to Fix Error 0x0000c1f5 Solución?,Error 0x80071a91 Win7 Fix Patch,Error 0x800ccc60 Windows Live Mail [Solved],Error Message 0x800ccc79 Outlook 2000 Fix,What is Error 0xc1ab0001?,[Answered] x64 saplugin dll,How to Fix Problem - Kb943729 Download?,Troubleshooting: Bccode D1 Error,Java Runtime Tech Support,Troubleshooting: ERROR NO SUCH GROUP Error
Read More: How to Fix Problem - Deleted part of Registry, cannot open .exe, .lnk and can't access sytem restore?,How Can I Fix - Dell Latitude E6410 - will not enter hibernation or sleep?,Fast Solution to Error: DataTier.API.dll is missing my computer,Troubleshoot:Default in Word 2010's Search function,Fast Solution to Problem: DESKTOP GOES BLACK AFTER AWHILE

No comments:

Post a Comment