Solved

EXCEL - If cell contains more than 6 characters then go to other cell - How to?

Posted on 2016-08-15
63 Views
Hi Experts.
I need a simple formula that says if a certain cell has more than 6 characters do nothing and let cursor drop down to cell below.
If there is exactly 6 character then move cursor to another cell.

DUMMY-FILE.xlsx
0
Question by:chris pike

LVL 27

Expert Comment

ID: 41757044
Re-stating your question to make sure I understand:
You want an automated procedure to move the cursor to cell H7 if the number of digits/characters in the currently-selected cell is six (6).

If so:
1) There is no formula that will do this; you'll have to use VBA to reselect cells given these conditions.
2) Your "H6" cell scenario is a contradiction.  If there are either six or more characters in H6, cell H7 would be selected.

What would be your expected response if there are less than six characters in a cell?  Halt there?

-Glenn
0

LVL 17

Accepted Solution

xtermie earned 500 total points
ID: 41765222
You need a macro to do that.
However Glenn is right, you need to work out the logic for this
Now, the following code will:
1) Perform your checks, as specified, for cell H2 (H3 in your reference)
``````Sub testx()
Dim ws As Worksheet
Set ws = Application.ActiveWorkbook.ActiveSheet

ws.Range("H2").Select
If Len(Selection.Value) = 6 Then
ws.Range("H8").Select
Else
If Len(Selection.Value) > 6 Then
ws.Range("H2").Offset(1, 0).Select
End If
End If
End Sub
``````
2) Perform your checks as specified in the range you specify (H2 to H6)
``````Sub testx2()
Dim ws As Worksheet
Dim r As Range

Set ws = Application.ActiveWorkbook.ActiveSheet
Set r = ws.Range("H2:H6")

For Each Cell In r
Cell.Select
If Len(Selection.Value) = 6 Then
ws.Range("H8").Select
Else
If Len(Selection.Value) > 6 Then
Selection.Offset(1, 0).Select
End If
End If
Next
End Sub
``````

Can be used in your Excel file as macros, hit Alt+F11 and then copy-->paste the macros
0

Author Closing Comment

ID: 41774868
Thanks looks good.
0

Featured Post

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…