Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Max data return for lengh, but need max less than 300

Posted on 2011-03-02
4
Medium Priority
?
297 Views
Last Modified: 2012-05-11
I have received the answer on how to loop through columns to get the max length for each one.  I have attached all the code and it does work.  However, the first column will always return the 300 + as that is where the footnote is located, so now, how to I actually get the Max value, but the max value less than 300
Public Sub LengthCountTest()
Dim rngCell             As Range
Dim intColTextLength    As Integer
Dim intRowCount         As Integer
Dim intColCount         As Integer
Dim aryColCount()       As Integer
Dim intColumns          As Integer
Dim intColNumber        As Integer
Dim strNextColumn       As String
Dim intMax              As Integer
'First set the print area to a named range
With ActiveSheet
    .Names.Add "ReportArea", _
        "=" & Range("Print_Area").Address
    intRowCount = .Range("ReportArea").Rows.Count
    Debug.Print "Number of Rows: " & intRowCount
    intColCount = .Range("ReportArea").Columns.Count
    Debug.Print "Number of Columns: " & intColCount
'End With

ReDim aryColCount(1 To intColCount)

intColumns = 1
    Do Until intColumns > intColCount
        With ActiveSheet
            strNextColumn = WhichColumn(intColumns)
            For Each rngCell In Range(strNextColumn & ":" & strNextColumn).Columns
                    intMax = fntMaxInCol(strNextColumn)
                Debug.Print intMax
            Next rngCell
            intColumns = intColumns + 1
        End With
    Loop
End With

End Sub
Function fntMaxInCol(col As String) As Integer
'Finds the longest string in a column
Dim lngLastrow As Long
Dim i As Long
   
   lngLastrow = Range(col & Rows.Count).End(xlUp).Row
   fntMaxInCol = 0
   
   For i = 1 To lngLastrow
      If Len(Range(col & i).Value) > fntMaxInCol Then fntMaxInCol = Len(Range(col & i).Value)
   Next

End Function
Public Function WhichColumn(intColNumber As Integer) As String
'Determines which colum is next in the loop based on inter passed in
    Select Case intColNumber
        Case 1
            WhichColumn = "A"
        Case 2
            WhichColumn = "B"
        Case 3
            WhichColumn = "C"
        Case 4
            WhichColumn = "D"
        Case 5
            WhichColumn = "E"
        Case 6
            WhichColumn = "F"
    End Select
End Function

Open in new window

0
Comment
Question by:ssmith94015
  • 2
4 Comments
 
LVL 24

Accepted Solution

by:
StephenJR earned 2000 total points
ID: 35021851
Perhaps change line 46 to:

If Len(Range(col & i).Value) > fntMaxInCol And  Len(Range(col & i).Value)<300 Then
    fntMaxInCol = Len(Range(col & i).Value)
End If
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35021940
Or maybe search for something like the number of spaces (or words), like this:

if Len(Range(col & i).Value) > fntMaxInCol And UBound(Split(Range(col & i).Value, " "))< 5 then
   ' less that 5 "words" found so it must be a non-memo value
   fntMaxInCol = Len(Range(col & i).Value)
end If
0
 

Author Closing Comment

by:ssmith94015
ID: 35021946
I never thought of limiting the value to less than 300, this is perfect.
0
 

Author Comment

by:ssmith94015
ID: 35021966
rspahtiz, thank you for your attention. but the less than 300 works best for what I am trying to do.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question