Just can't get the LEN/MID thing quite right...

[ste mac]

Hi guys and gals

The code below looks down a column of numbers, the column can
have 2, 3 or 4 digits in each cell... the macro it is supposed to look
at the amount of digits in each cell and execute from there...

It works fine if the cell has 4 digits... but not if the cell has 2 or
3 digits!
I must have something wrong with the LEN and MID bit, can anyone see
the error?

As an example if a cell has 619 entered...when the code breaks, in
debug
mode, two1 holds 61 and two2 holds 9 whereas it should be 6 and 19...
same with 2 digits...
I have been struggling with this a bit...cheers

ste

Sub condata()
Application.ScreenUpdating = False
Sheets("The data").Select
Dim xlrow As Long
Dim two1 As Integer
Dim two2 As Integer
Dim two1prob
Dim two2prob
xlrow = 3
ActiveSheet.Range("J3").Select
Do While Not (ActiveSheet.Cells(xlrow, 10).Value = "")


If Len(ActiveSheet.Cells(xlrow, 10).Value = 2) Then
two1 = Left(ActiveSheet.Cells(xlrow, 10).Value, 1)
two2 = Mid(ActiveSheet.Cells(xlrow, 10).Value, 2, 1)
End If

If Len(ActiveSheet.Cells(xlrow, 10).Value = 3) Then
two1 = Left(ActiveSheet.Cells(xlrow, 10).Value, 1)
two2 = Mid(ActiveSheet.Cells(xlrow, 10).Value, 2, 2)
End If

If Len(ActiveSheet.Cells(xlrow, 10).Value = 4) Then
two1 = Left(ActiveSheet.Cells(xlrow, 10).Value, 2)
two2 = Mid(ActiveSheet.Cells(xlrow, 10).Value, 3, 2)
End If

ActiveSheet.Range("V3:V51").Select
Selection.Find(What:=two1, After:=ActiveCell, LookIn:=xlValues,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 2).Select
two1prob = ActiveCell.Value

ActiveSheet.Range("V3:V51").Select
Selection.Find(What:=two2, After:=ActiveCell, LookIn:=xlValues,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 2).Select
two2prob = ActiveCell.Value

ActiveSheet.Cells(xlrow, 12).Value = two1prob * two2prob
xlrow = xlrow + 1

Loop

End Sub

 

[impslayer]

ste mac skrev:

If Len(ActiveSheet.Cells(xlrow, 10).Value = 2) Then
two1 = Left(ActiveSheet.Cells(xlrow, 10).Value, 1)
two2 = Mid(ActiveSheet.Cells(xlrow, 10).Value, 2, 1)
End If

Seems you have the rightmost parenthesis in the wrong place, move it a
couple of characters to the left

/impslayer, aka Birger Johansson

 

[impslayer]

impslayer skrev:

ste mac skrev:


Seems you have the rightmost parenthesis in the wrong place, move it a
couple of characters to the left

/impslayer, aka Birger Johansson

Ehm, on the "If Len..." line.

/impslayer

 

[ste mac]

impslayer,
Thanks for your help, I have been messing around with this for a while
all I can say is thanks a lot..

cheers

ste