Seeking assistance with ths code

**kmakjop** · 10-04-2012, 02:02 PM

I am new to vba and i am trying to write a code to validate the "A" column. if the cell character length = 16 then add the letter "C" to the cell.
if the cell not <> 9 and it is <> 16 then delete the info entered in the cell. I would like the cell that was deleted to still be selected after the validation of the cell is done (this part is optional though). The primary thing is to validate the cell. I got both codes to work but not at the same time. if it captures one condition it misses out the other.

Kindly assist thank you.

below is a sample of the code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Worksheets("Sheet1").Range("a8:a") <> "" Then
If Len(Target.Value) = 16 Then
Target.Value = "C" & Target.Value
MsgBox ("test")
End If
End If
If Worksheets("Sheet1").Range("a8:a") <> "" Then
If Len(Target.Value) <> 9 And Len(Target.Value) <> 16 Then
MsgBox ("test again")
Target.Value = Left(Target.Value, 0)
End If
End If

End Sub

**Bishonen** · 10-04-2012, 02:13 PM

Might be that this is what you're after:

Sub test()

Dim Cell As Range

For Each Cell In Worksheets("Sheet1").Range("A8","A:A")
If Cell.Value <> "" Then
    If Len(Cell.Value) = 16 Then
        Cell.Value = "C" & Cell.Value
        MsgBox ("test")
    End If
End If
Next Cell

For Each Cell In Worksheets("Sheet1").Range("A8", "A:A")
If Cell.Value <> "" Then
        If Len(Cell.Value) <> 9 And Len(Cell.Value) <> 16 Then
        MsgBox ("test again")
        Cell.Value = Left(Cell.Value, 0) ' which is nothing btw
    End If
End If
Next Cell

End Sub

Please try to add some formatting to your code (i almost got blind

). Wrap your code in "[code]" & "[/ code](without the space)" next time.

**kmakjop** · 10-04-2012, 03:16 PM

Thank you, i like your code; however the code you gve me deletes the cell if the "C" is added to the cell. I only want the delete to execute only if the cell <> 9 but if it is = 16 just add "C" to the cell and keep content.

Thanks again.

Originally Posted by Bishonen

Might be that this is what you're after:

Sub test()

Dim Cell As Range

For Each Cell In Worksheets("Sheet1").Range("A8","A:A")
If Cell.Value <> "" Then
    If Len(Cell.Value) = 16 Then
        Cell.Value = "C" & Cell.Value
        MsgBox ("test")
    End If
End If
Next Cell

For Each Cell In Worksheets("Sheet1").Range("A8", "A:A")
If Cell.Value <> "" Then
        If Len(Cell.Value) <> 9 And Len(Cell.Value) <> 16 Then
        MsgBox ("test again")
        Cell.Value = Left(Cell.Value, 0) ' which is nothing btw
    End If
End If
Next Cell

End Sub

Please try to add some formatting to your code (i almost got blind

). Wrap your code in "[code]" & "[/ code](without the space)" next time.

**Bishonen** · 10-04-2012, 03:31 PM

Originally Posted by kmakjop

Thank you, i like your code; however the code you gve me deletes the cell if the "C" is added to the cell. I only want the delete to execute only if the cell <> 9 but if it is = 16 just add "C" to the cell and keep content.

Thanks again.

That's exactly what i tried to inform you about in my comment (which comes after an apostrophe).

Sub test()

Dim Cell As Range

For Each Cell In Worksheets("Sheet1").Range("A8", "A" & Rows.Count)
Select Case Len(Cell)

    Case Is = 16
    Cell.Value = "C" & Cell.Value
    
    Case Is <> 9
    Cell.Value = ""
    
    End Select
    
    Next Cell
    
End Sub

**mike7952** · 10-04-2012, 03:29 PM

Give this a try

Dim Cell As Range

For Each Cell In Worksheets("Sheet1").Range("A8", "A:A")
    With Cell
        If Trim(.Value) <> "" Then
            Select Case Len(.Value)
                Case Is = 16
                    .Value = "C" & .Value
                    MsgBox ("test 16")
                Case Is <> 9
                    .ClearContents
                    MsgBox ("test <> 9 but not 16")
            End Select
        End If
    End With
Next Cell

**kmakjop** · 10-04-2012, 04:41 PM

Ok i am starting to feel kinda stupid here. I tried Mike's code and it basically did th same thing (deleted the content even though there were 16 characters in the cell). I also tried Bishonen's code and it froze the application. What am i not doing or understanding here?

**kmakjop** · 10-04-2012, 05:05 PM

is there any other suggestions bishonen or mike? Are there anyone else who can suggest something that could assist with ths code. I think the suggestios i got from both Mike and bishonen will work if they are modified a bit. I am trying but its still not executing as i would like.

**jeffreybrown** · 10-04-2012, 05:08 PM

@ kmakjop,

Before we can continue, two things...

Please read the forum rules about proper thread titles and applying code tags when posting code. Thanks.

**mike7952** · 10-04-2012, 05:09 PM

The problem is that your doing this inside the Worksheets Change Event. Example when you type in say Cell A8 a 16 character it changes to 17 which fires the macro again, so then the 16 character value became C17 and gets set to nothing here Case Is <> 9. That make sense.

**kmakjop** · 10-08-2012, 03:55 PM

Thank you so much for your assistance, your code worked flawlessly. I also appreciate the explanation you gave as to why i couldnt get it to execute.

I am in jamaica so let me give that thank you in a Jamaican vibe!

Mike yu a di bhass (bhass meaning boss!), bless up!

Big up to bishonen for the guidance as well!

**jeffreybrown** · 10-08-2012, 04:47 PM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Seeking assistance with ths code

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Seeking assistance with ths code

Re: Seeking assistance with ths code

Re: Seeking assistance with ths code

Re: Seeking assistance with ths code

Re: Seeking assistance with ths code

Re: Seeking assistance with ths code

Re: Seeking assistance with ths code

Re: Seeking assistance with ths code

Re: Seeking assistance with ths code

Re: Seeking assistance with ths code

Re: Seeking assistance with ths code

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions