Solved: else and else if with part text

[wibbers2000]

Hi,

I have a problem with my else if's. I have a sheet with just one populated column. This column is in text format and consists of 5 char then a "-" the 5 more char (01801-12564, or OBN03-15684 etc).

I would like to do a elseif within the first 3 to 5 char.

the code below works, but when I add an else if and only the first part works, all the others display "none". I am not sure what I need to do to get a match.

[VBA] Sub changename()
Dim Count As Integer
Dim cell As Integer

Count = Range("a1").End(xlDown).Row
Range("a1").Offset(0, 2).Select
ActiveCell = Count
Range("a1").Offset(0, 1).Select

For cell = 1 To Range("c1").Value
ActiveCell.Offset(1, 0).Select

If ActiveCell.Offset(0, -1).Value Like "011*" Then
ActiveCell = "Briv"

Else
ActiveCell = "none"
End If

Next

End Sub
[/VBA]

Can anyone point me in the right direction.

Regards
Wibbers

[wibbers2000]

sorted it. not sure what I was doing wrong but it works now.

[Rembo]

Hello Wibbers,

An error that is easy to spot is in the line 'ActiveCell = Count' where you compare different type of objects. ActiveCell is a range object, Count is an variable of type Integer. Perhaps you wanted to set the Row of Activecell equal to the Count value (ActiveCell.Row = Count).

In general it's good to know that you don't have to Select a cell to be able to read the value in it, or write a value to it. Selecting takes time, if you don't need it, don't use it.

A tip that might be of use to you is that when you use variables and objects, preceed them with a character that says something about that variable or object. Rather then using Count, use iCount (Integer - Count) and rather then using Cell, use rCell (Range - Cell). Whever you see something like rMyBook = iThelastone you know something is not right, eventhough you can't really tell from the variable/object names what is being meant.

That aside, I created some code that determens the number of rows of your data using column A, then checks each corresponding cell in column B for a 'value Like "011*" ' and writes "Briv" to the corresponding cell in column C if the condition is True, or "none" if the condition is False.

Hope that helps,

Rembo

[VBA]Sub changename()
Dim lLastrow As Long, l As Long
Dim rCheckedCell As Range
lLastrow = Range("A1").End(xlDown).Row
For Each rCheckedCell In Range("C1:C" & lLastrow)
If rCheckedCell.Offset(0, -1).Value Like "011*" Then
rCheckedCell.Value = "Briv"
Else
ArCheckedCell.Value = "none"
End If
Next rCheckedCell
End Sub[/VBA]

[wibbers2000]

thanks Rembo


again your reasoning is sound and logical. I will take on board your advice and thanks for the code.

Wibbers