VBA code to add 0 if len is different

[jbesclapez]

Hello,

I am trying to build a VBA that would :
1- Check the lastrow in A
2- From D2:D lastrow it will give me the MAX of LEN of this array (max nb of character value)
3- Go on all cells from D2:D lastrow and add a 0 at the end of the cellvalue if the LEN of this cell value is less than the value in 2 (MAX LEN of array)

So far I am struggling with something stupid : I do not know how to have my formula work in the VB!!!

Thanks for your help

Public Function maxRangeLength(data As Range) As Integer 'USAGE =maxRangeLength(A8:D11)
    Dim ret As Integer

    ret = 0

    For Each cell In data
        ret = Application.max(ret, Len(cell))
    Next cell

    maxRangeLength = ret
End Function

Sub ReformatID()

Dim sht As Worksheet
Dim LastRowA As Integer
Dim MaxLenD As Integer

Set sht = ActiveSheet

LastRowA = sht.Cells(sht.Rows.Count, "A").End(xlUp).row
MaxLenD = maxRangeLength("D2:D" & LastRowA)

End Sub

 

[rlv01]

MaxLenD = maxRangeLength("D2:D" & LastRowA)

Function maxRangeLength expects a range parameter, but you are giving it a string. Try this instead:

 MaxLenD = maxRangeLength(Range("D2:D" & LastRowA))

 

[jbesclapez]

OOops thanks rlv01 I did not spot this. I am now finished