S
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
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