+ Reply to Thread
Results 1 to 5 of 5

How to find the number of characters in a cell of specific column (for example column 8)

  1. #1
    Registered User
    Join Date
    05-25-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    11

    Question How to find the number of characters in a cell of specific column (for example column 8)

    How to find the number of characters in a cell of specific column (for example column 8)

    Lets say I have Cells filled up with some characters in column 8

    Column 8
    AA
    BB

    CC
    IIIII
    EEEEEE

    Result should be ( assume Column 8 is the last column)
    Column 9
    A
    A
    A
    I
    E

    I want to write a macro that finds the number of characters in a cell and if that number is less than 3 then assign "A" to the last active column +1
    If number of characters are greater and equal to 5 then write 4 th character in to the last active column+1
    If the number of characters =0(Empty) Then delete the whole row.

    I am new to VBA so I don't know how some syntax work like the Len(), but the Idea is this:

    Option Explicit
    Sub Check()
    Dim myRange As Range, sRange, Sheet1 As String
    Dim I, ROWNUM, COLNUM As Long
    Dim ws1 As Worksheet


    ROWNUM = Worksheets(sheet1).Cells(Rows.Count, 1).End(xlUp).Row
    COLNUM =Worksheets(sheet1).Cells(1, Columns.count).End(xlToLeft).Column
    Set ws1=Worksheets(Sheet1)

    For i=1 To ROWNUM
    If Len(8)<3 Then
    ws1.Cells( i,COLNUM +1)="A"
    Else If Len(8) >=5 Then
    ws1.Cells( i,COLNUM+1)=Mid(ws1.Cells( i,8),4,1)
    Else If Len(8)=0 Then
    Rows( i).Delete
    End if

    Next i

    End Sub

    Please Help!!!
    Thanks
    Last edited by lee478; 05-26-2013 at 01:39 PM.

  2. #2
    Forum Contributor
    Join Date
    10-13-2012
    Location
    Southern California
    MS-Off Ver
    Excel 2007
    Posts
    401

    Re: How to find the number of characters in a cell of specific column (for example column

    You were on the right track. The thing is, if you're looping down through the rows, if you delete a row then you don't want to increment the row number (deleting a row, in effect, already takes care of that) and it looks like your code was doing just that.

    Thus, rather than use a FOR NEXT loop, I changed it to a DO UNTIL loop.

    This code seems to work with all of the data I tested it with. Modify as needed.

    Please Login or Register  to view this content.
    Edited. Found a small bug.

    Please confirm you're not doing anything when the length is exactly four.
    Last edited by Ed_Collins; 05-26-2013 at 02:49 PM.

  3. #3
    Registered User
    Join Date
    05-25-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: How to find the number of characters in a cell of specific column (for example column

    Dim last_row As Integer, dont_increment As Boolean, deletes As Integer?

    By the way what does Range("H65536") mean?

  4. #4
    Forum Contributor
    Join Date
    10-13-2012
    Location
    Southern California
    MS-Off Ver
    Excel 2007
    Posts
    401

    Re: How to find the number of characters in a cell of specific column (for example column

    No, "last_row" ideally, should be dimensioned as Long, since in theory you might have more than 32,767 rows of data.

    Yes, "don't increment" is just a switch that will either be 1 or 0. You can dimension this as an Integer or as Boolean.

    "Deletes" keeps track of the number of rows deleted. Since this, in theory, might also be more than 32,767 in number, this should also be dimensioned as Long.

    last_row = Range("H65536").End(xlUp).Row is just one of the many ways of finding which of your rows is the last one to have data. In this case, the H is for the H column. I believe the statement searches the H row, from bottom to top, starting with row 65536, I guess. (That number should probably be changed... I just grabbed that code somewhere, when I was looking for a method to determine the last row of data.)

    I hope this helps.

  5. #5
    Forum Contributor
    Join Date
    10-13-2012
    Location
    Southern California
    MS-Off Ver
    Excel 2007
    Posts
    401

    Re: How to find the number of characters in a cell of specific column (for example column

    Note that if you don't dimension variables, the default type is "variant" which often will be exactly what you need anyway.

    http://office.microsoft.com/en-us/ex...080556594.aspx

    (Strings need to be dimensioned as strings, however.)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1