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
Bookmarks