if statements with strings

[chammy88]

Hi all,

I'm trying to do something like this:
If RealExp(i) = "any string" then
RealExp(i) = ""
End if

RealExp is an array

my problem is that I don't know what to put for "any string" I thought VB would recognize "string" as any letters in there but it didn't work.

Is there a way I can say if RealExp has words in any of it's rows then delete them?

Thanks

 

[brettdj]

If you wanted to erase the string regardless of contents then something like

 If Len(RealExp(i)) > 0 Then RealExp(i) = vbNullString

But if you want to distinguish between say numbers and alphabetic characters then you need to test the string before deciding to empty it

If it is the later please provide a sample

Regards

Dave

 

[Trebor76]

Hi chammy88,

See if this earlier post of mine helps.

Regards,

Robert

 

[mikerickson]

I see no reason to test, unless RealExp is an array with elements that may or may not be strings.

Redim RealExp(LBound(RealExp) to UBound(RealExp))
'or
For i = LBound(RealExp) To UBound(RealExp)
    RealExp(i) = vbNullString
Next i
'or
For i = LBound(RealExp) To UBound(RealExp)
    If TypeName(RealExp(i)) = "String" Then
        RealExp(i) = vbNullString
    End If
Next i

But, "Is there a way I can say if RealExp has words in any of it's rows then delete them" suggests that RealExp is a Range object. If that is the case

On Error Resume Next
RealExp.SpecialCells(xlCellTypeConstants, xlTextValues).ClearContents
On Error Goto 0

 

[chammy88]

Hey guys,

thanks for all your suggestions, I have overcome that hurdle now.

I have one other questions that you guys may be able to help me with.

I've got two columns and I want a code to go through the first column and then I want to put some if statements in.
For example:
1. if any of the cells say "Stacking.STK02" I want that cell to display the 5 letters on the right (ie. "STK02)
2. If the text is > 41 characters, only show the last 7 characters.

I have tried numerous methods that I think should work but they are coming up with a "Subscipt out of range error".

If anyone wants me to I can put the code up that I wrote but I think I'd rather see other peoples fresh ideas first.

cheers