Forum Discussion
Data Validation Cell Length with error message
Hi,
I am currently creating a template for suppliers where they have to fill in product information.
In order to avoid receiving bad quality data, I would like to create a macro that the suppliers can use to check if the values are entered correctly before sending me back the file.
I do not want to use simple data validation, because some do mass upload and can fill in the cell avoid the data validation rules.
To be clear, I would like a macro that when is activated, is check all the cell (from X21 to X7100, Y21 to Y7100 and Z21 to Z100) and state an error message when the length of the cell is over 32.
Additional informations:
The name of the sheet is "(3)Product Template" and the workbook is "Product Template 2022"
Not all the 7100 rows are filled in, they are fulfilled depending on the number of article the supplier has.
I tried several macro's found on internet but none of them were working for me (as I cannot code vba, I guess they worked but I am trash at adapting them to my file).
If anyone could help me, you would save my day!
Sub data() Dim i As Long Dim j As Long For i = 21 To 7100 For j = 24 To 26 If Len(Cells(i, j)) > 32 Then MsgBox ("Error in column:" & j & "row:" & i) Else End If Next j Next i End SubYou can try this code. In the attached file you can click the button in cell V2 to run the macro. If a cell is longer than 32 digits an error message shows the row and column number of that cell.
2 Replies
Sub data() Dim i As Long Dim j As Long For i = 21 To 7100 For j = 24 To 26 If Len(Cells(i, j)) > 32 Then MsgBox ("Error in column:" & j & "row:" & i) Else End If Next j Next i End SubYou can try this code. In the attached file you can click the button in cell V2 to run the macro. If a cell is longer than 32 digits an error message shows the row and column number of that cell.
- Thank you very much for your help! Saved my day!
