?
Solved

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

Posted on 2011-03-02
4
Medium Priority
?
294 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
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…

719 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