Aug 29 2022 09:48 AM
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!
Aug 29 2022 10:07 AM
SolutionSub 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 Sub
You 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.
Aug 30 2022 12:26 AM
Aug 29 2022 10:07 AM
SolutionSub 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 Sub
You 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.