Share via

VB: Test Character Length / LEN ? of a cell in a row

Anonymous
2014-06-12T02:26:58+00:00

hi,  i am trying to test a cell in a row, other than the current column for a character length greater than 1.  thanks.

i have some examples i have been trying to make work:

If Len(Cells(Target.Row, RANGE(H7).Column)) > 0 Then   'syntax error

'If Application.Len(Me.Cells(Target.Row, RANGE(H7).Column)) > 0 Then

'If Application.Len(Cells(ActiveCell.Row, H7)) > 1 Then

    'If Len(Target) > 1 then  'rap

    'If Len(RANGE(H7).Column) > 1 Then

    'If Len(Cells(RANGE(H7))) > 1 Then

    'If Me.Cells(Target.Row, H7).Value > 1 Then

MsgBox "YES"

Else

MsgBox "NO"

End If

example of a working similar item:

    Dim J3 As String    'eg workcell shows:  CY:CY

    J3 = RANGE("J3")  'same for other work cells..

    If Not Intersect(Me.RANGE(J3), .Cells) Is Nothing Then        'column select vb

        If Me.Cells(Target.Row, J3).Value = "" Then       '

        Me.Cells(Target.Row, RANGE(J3).Column).Select     'jump

        ElseIf Me.Cells(Target.Row, D2).Value = "" Then   '

        Me.Cells(Target.Row, RANGE(D2).Column).Select     'jump

        Else

        Me.Cells(Target.Row, RANGE(D2).Column).Select     'jump

        End If

    End If

==========    ANSWER:

        If Len(Cells(Target.Cells.Row, H7).Value) > 1 Then  'yes

        'If Len(Cells(Target.Cells(2).Row, H7).Value) > 1 Then  'yes does same 1 row down test

        'If Len(Cells(Target.Cells(2, 1).Row, H7).Value) > 1 Then  'yes, note: if correct: (rows, na cols?) offset inclusive/ +1

        'If Len(Cells(Target.Cells(1, 1).Row, H7).Value) > 1 Then  'yes, not sure what 2nd digit is for

        Me.Cells(Target.Row, RANGE(H7).Column).Select     'jump

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2014-06-12T06:27:33+00:00

Here are a couple to get started with,

If Len(Cells(Target.Row, RANGE(H7).Column)) > 0 Then   'syntax error

If Len(Cells(Target.Cells(1, 1).Row, "H").Value) >0 Then

  ... or,

If Len(Cells(Target.Cells(1, 1).Row, 8).Value) >0 Then

  ... or,

If Len(Cells(Target.Cells(1, 1).Row, Range("H7").Column).Value) > 0 Then

I've use Target.Cells(1, 1).Row because there is no guarantee that Target does not refer to a range of more than a single cell. If you wanted to halt processing when Target references more than a single cell, use this to precede the code,

If Target.Cells.Count > 0 Then Exit Sub

Was this answer helpful?

0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2014-06-12T12:15:48+00:00

    hi,  i got that to work but do not know what the (1,1) numbers are for.  the first digit seems to be for testing an offset Rows.  what is the 2nd digit for?

    results had:

            If Len(Cells(Target.Cells.Row, H7).Value) > 1 Then  'yes

            'If Len(Cells(Target.Cells(2).Row, H7).Value) > 1 Then  'yes does same 1 row down test

            'If Len(Cells(Target.Cells(2, 1).Row, H7).Value) > 1 Then  'yes, note: if correct: (rows, na cols?) offset inclusive/ +1

            'If Len(Cells(Target.Cells(1, 1).Row, H7).Value) > 1 Then  'yes, not sure what 2nd digit is for

            Me.Cells(Target.Row, RANGE(H7).Column).Select     'jump

    Was this answer helpful?

    0 comments