VBA code to have min of 7 characters in column B and also check if column C has a value?

[danbates]

Hi,

Please can someone help me?

I have this code and it works fine if I just want it to look at column B to see if it has a minimum of 7 characters anything less except 0 and it calls for a macro called ColumnB.

'Update Only if Column 'B' is 7 or more characters.'Try and add Column C into the code and see if it has a value?
sValueA = Trim(ActiveSheet.Cells(target.Row, "B").Value)
If Len(sValueA) = 0 Then
GoTo HEREB
Else
If Len(sValueA) < 7 Then
ColumnB
Exit Sub
End If

What I would like if possible is the following:
If Column B is less than 7 characters = ColumnB macro
If Column B has 7 or more characters but Column C has nothing = ColumnB macro
If Column B has 7 or more characters and Column C has a value then carry on

Any help would be much appreciated.

Kind Regards

Dan

 

[Joe4]

Is this what you are looking for?

'   Update Only if Column 'B' is 7 or more characters.'Try and add Column C into the code and see if it has a value?
    sValueA = Trim(Cells(Target.Row, "B").Value)
    
    Select Case sValueA
        Case 0
            GoTo HEREB
        Case Is < 7
            ColumnB
        Case Is >= 7
            If Cells(Target.Row, "B") = "" Then
                ColumnB
            End If
    End Select


End Sub

 

[Rick Rothstein]

Is this what you are looking for?

'   Update Only if Column 'B' is 7 or more characters.'Try and add Column C into the code and see if it has a value?
    sValueA = Trim(Cells(Target.Row, "B").Value)
    
    Select Case [B][COLOR="#FF0000"]Len([/COLOR][/B]sValueA[B][COLOR="#FF0000"])[/COLOR][/B]
        Case 0
            [B][COLOR="#0000FF"]GoTo HEREB[/COLOR][/B]
        Case Is < 7
            ColumnB
        Case Is >= 7
            If Cells(Target.Row, "B") = "" Then
                ColumnB
            End If
    End Select


End Sub

Don't you need a call to the Len function like I show in red above?

I know you just copied the OP's code, but I think it necessary to point out the the GoTo statement's target label is missing in the code posted (not sure if it appears above the code snippet that was post or it the OP simply forgot to include it).

 

[Joe4]

Don't you need a call to the Len function like I show in red above?

Ah yes. I look two quickly and thought it was already part of the sValueA calculation, which it is not.
So I was thinking something along the lines of:

sValueA = Len(Trim(Cells(Target.Row, "B").Value))

I know you just copied the OP's code, but I think it necessary to point out the the GoTo statement's target label is missing in the code posted (not sure if it appears above the code snippet that was post or it the OP simply forgot to include it).

Yes, and I would also question whether this is really necessary at all. I guess it all depends on where the code is located and what it does.
Generally speaking, we usually try to avoid GoTo statements whenever possible.

 

[Rick Rothstein]

Ah yes. I look two quickly and thought it was already part of the sValueA calculation, which it is not.
So I was thinking something along the lines of:

sValueA = Len(Trim(Cells(Target.Row, "B").Value))

I put the Len function call on the Select Case object instead of where you show it above because of the variable name... the leading lower case "s" suggests to me that sValueA is a String variable rather than an Integer or Long variable.

Generally speaking, we usually try to avoid GoTo statements whenever possible.

I fully agree with that.

 

[danbates]

Hi Joe4 and Rick,

Thank you for your replies.

The code works now but I had to change this part in red:

' Case Is >= 7            
               If Cells(target.Row, "[COLOR=#ff0000]C[/COLOR]") = "" Then
                ColumnB
            End If
    End Select

To answer your question about the GoTo part in my code.
Basically I've copied this code off someones thread and the GoTo was in the code. I altered the code as much as I could to make it work for me and because the GoTo works, I left it in.
Now looking at it again this morning, I've realised I can just change the "GoTo HEREB" to "Exit Sub" and that works.
All the GoTo was doing was skipping the rest of the code to exit the sub.

Thanks again for all your help Joe and Rick, it really is appreciated.

Kind Regards

Dan

 

[Joe4]

You are welcome.

Now looking at it again this morning, I've realised I can just change the "GoTo HEREB" to "Exit Sub" and that works.
All the GoTo was doing was skipping the rest of the code to exit the sub.

I figured it might be something like that. I am glad you figured it out.