Hi Ive got a simular question to before with a slight variation. The
prev advice was excellant and worked perfect.
I hope I can explain clearly, given a set of numbers
2
2 2
2 3
2 4
....etc
2 K
2 2 2
2 2 3
2 2 4
2 2 5
2 2 6
......etc I need to count in a certain pattern. The first number 2 is
fixed, moving to 2 3, 2 4 I need to ignore my fixed number 2, and count
the 2 = 1 occurence, move to the next, again ignore the fixed number
and count the 3 etc for the set of two digits, if the 3 occured more
than ounce then this woud be a second occurence. The data goes into a
column call 2 digits. moving to 2 2 2 ignore my fixed number 2, count
the second 2 add this result to the column 2 digits then count the
final 2 wich then goes into a colum called 3 digits. And on upto 5
digits.
What Ive written Im sure will look clumsy and I hope you dont mind me
adding here.

Dim x As Integer
Dim W As Variant, s As Variant, t As Variant
Dim getcard As Variant
Dim trimcard As Variant
Dim xng As Range

Set xng = Range("g1:g13")

Range("a1").Select
CardString = Range("a1")
checkstring = Replace(Range("a1"), " ", "")


For i = 2 To Range("a2:a" & Range("a65536").End(xlUp).Row).Count +
1
Cells(i, 1).Select
trimcard = Replace(Cells(i, 1), " ", "")
getcard = Replace(trimcard, checkstring, "")

If Len(trimcard) Len(checkstring) Then


If Len(getcard) = 1 Then
s = 1
ElseIf Len(getcard) = 2 Then
s = 2
ElseIf Len(getcard) = 3 Then
s = 3
ElseIf Len(getcard) = 4 Then
s = 4
End If



For t = 1 To s

If Mid(getcard, t, 1) = "A" Then
x = 11
ElseIf Mid(getcard, t, 1) = "J" Then
x = 12
ElseIf Mid(getcard, t, 1) = "Q" Then
x = 13
ElseIf Mid(getcard, t, 1) = "K" Then
x = 14
ElseIf Mid(getcard, t, 1) = "T" Then
x = 10
Else
x = Mid(getcard, t, 1)
End If


If t = 1 And s = 1 Then
If Len(trimcard) = 2 Then
xng.Cells(x, 1) = xng.Cells(x, 1) + 1
ElseIf Len(trimcard) = 3 Then
xng.Cells(x, 1 + 1) = xng.Cells(x, 1) + 1
ElseIf Len(trimcard) = 4 Then
xng.Cells(x, 1 + 2) = xng.Cells(x, 1 + 2) +
1
ElseIf Len(trimcard) = 5 Then
xng.Cells(x, 1 + 3) = xng.Cells(x, 1 + 3) +
1
End If
ElseIf t = 1 And s 1 Then
If (Len(checkstring) + 1) = 2 Then
xng.Cells(x, 1) = xng.Cells(x, 1) + 1
ElseIf (Len(checkstring) + 1) = 3 Then
xng.Cells(x, 1 + 1) = xng.Cells(x, 1) + 1
ElseIf (Len(checkstring) + 1) = 4 Then
xng.Cells(x, 1 + 2) = xng.Cells(x, 1 + 2) +
1
ElseIf (Len(checkstring) + 1) = 5 Then
xng.Cells(x, 1 + 3) = xng.Cells(x, 1 + 3) +
1
End If
ElseIf t 1 And s 1 Then
If Len(trimcard) = 2 Then
xng.Cells(x, 1) = xng.Cells(x, 1) + 1
ElseIf Len(trimcard) = 3 Then
xng.Cells(x, 1 + 1) = xng.Cells(x, 1) + 1
ElseIf Len(trimcard) = 4 Then
xng.Cells(x, 1 + 2) = xng.Cells(x, 1 + 2) +
1
ElseIf Len(trimcard) = 5 Then
xng.Cells(x, 1 + 3) = xng.Cells(x, 1 + 3) +
1
End If
End If
Next t

End If

Next i



This works in a fasion, but given the data above, when trimcard = 2 and
checkstring = 22 then it replaces all the 2's and I need to trim only
the two.

Any advice appreciated .
Regards Robert

If you don't want to consider the first character then

trimcard = Replace(Cells(i, 1), " ", "")
trimcard = Right(Trimcard,len(trimcard)-1)
getcard = Replace(trimcard, checkstring, "")

--
Regards,
Tom Ogilvy



"RobcPettit" wrote in message
oups.com...
Hi Ive got a simular question to before with a slight variation. The
prev advice was excellant and worked perfect.
I hope I can explain clearly, given a set of numbers
2
2 2
2 3
2 4
...etc
2 K
2 2 2
2 2 3
2 2 4
2 2 5
2 2 6
.....etc I need to count in a certain pattern. The first number 2 is
fixed, moving to 2 3, 2 4 I need to ignore my fixed number 2, and count
the 2 = 1 occurence, move to the next, again ignore the fixed number
and count the 3 etc for the set of two digits, if the 3 occured more
than ounce then this woud be a second occurence. The data goes into a
column call 2 digits. moving to 2 2 2 ignore my fixed number 2, count
the second 2 add this result to the column 2 digits then count the
final 2 wich then goes into a colum called 3 digits. And on upto 5
digits.
What Ive written Im sure will look clumsy and I hope you dont mind me
adding here.

Dim x As Integer
Dim W As Variant, s As Variant, t As Variant
Dim getcard As Variant
Dim trimcard As Variant
Dim xng As Range

Set xng = Range("g1:g13")

Range("a1").Select
CardString = Range("a1")
checkstring = Replace(Range("a1"), " ", "")


For i = 2 To Range("a2:a" & Range("a65536").End(xlUp).Row).Count +
1
Cells(i, 1).Select
trimcard = Replace(Cells(i, 1), " ", "")
getcard = Replace(trimcard, checkstring, "")

If Len(trimcard) Len(checkstring) Then


If Len(getcard) = 1 Then
s = 1
ElseIf Len(getcard) = 2 Then
s = 2
ElseIf Len(getcard) = 3 Then
s = 3
ElseIf Len(getcard) = 4 Then
s = 4
End If



For t = 1 To s

If Mid(getcard, t, 1) = "A" Then
x = 11
ElseIf Mid(getcard, t, 1) = "J" Then
x = 12
ElseIf Mid(getcard, t, 1) = "Q" Then
x = 13
ElseIf Mid(getcard, t, 1) = "K" Then
x = 14
ElseIf Mid(getcard, t, 1) = "T" Then
x = 10
Else
x = Mid(getcard, t, 1)
End If


If t = 1 And s = 1 Then
If Len(trimcard) = 2 Then
xng.Cells(x, 1) = xng.Cells(x, 1) + 1
ElseIf Len(trimcard) = 3 Then
xng.Cells(x, 1 + 1) = xng.Cells(x, 1) + 1
ElseIf Len(trimcard) = 4 Then
xng.Cells(x, 1 + 2) = xng.Cells(x, 1 + 2) +
1
ElseIf Len(trimcard) = 5 Then
xng.Cells(x, 1 + 3) = xng.Cells(x, 1 + 3) +
1
End If
ElseIf t = 1 And s 1 Then
If (Len(checkstring) + 1) = 2 Then
xng.Cells(x, 1) = xng.Cells(x, 1) + 1
ElseIf (Len(checkstring) + 1) = 3 Then
xng.Cells(x, 1 + 1) = xng.Cells(x, 1) + 1
ElseIf (Len(checkstring) + 1) = 4 Then
xng.Cells(x, 1 + 2) = xng.Cells(x, 1 + 2) +
1
ElseIf (Len(checkstring) + 1) = 5 Then
xng.Cells(x, 1 + 3) = xng.Cells(x, 1 + 3) +
1
End If
ElseIf t 1 And s 1 Then
If Len(trimcard) = 2 Then
xng.Cells(x, 1) = xng.Cells(x, 1) + 1
ElseIf Len(trimcard) = 3 Then
xng.Cells(x, 1 + 1) = xng.Cells(x, 1) + 1
ElseIf Len(trimcard) = 4 Then
xng.Cells(x, 1 + 2) = xng.Cells(x, 1 + 2) +
1
ElseIf Len(trimcard) = 5 Then
xng.Cells(x, 1 + 3) = xng.Cells(x, 1 + 3) +
1
End If
End If
Next t

End If

Next i



This works in a fasion, but given the data above, when trimcard = 2 and
checkstring = 22 then it replaces all the 2's and I need to trim only
the two.

Any advice appreciated .
Regards Robert

Thanks for your reply. That will work perfect. Regards Robert
Tom Ogilvy wrote:
If you don't want to consider the first character then

trimcard = Replace(Cells(i, 1), " ", "")
trimcard = Right(Trimcard,len(trimcard)-1)
getcard = Replace(trimcard, checkstring, "")

--
Regards,
Tom Ogilvy



"RobcPettit" wrote in message
oups.com...
Hi Ive got a simular question to before with a slight variation. The
prev advice was excellant and worked perfect.
I hope I can explain clearly, given a set of numbers
2
2 2
2 3
2 4
...etc
2 K
2 2 2
2 2 3
2 2 4
2 2 5
2 2 6
.....etc I need to count in a certain pattern. The first number 2 is
fixed, moving to 2 3, 2 4 I need to ignore my fixed number 2, and count
the 2 = 1 occurence, move to the next, again ignore the fixed number
and count the 3 etc for the set of two digits, if the 3 occured more
than ounce then this woud be a second occurence. The data goes into a
column call 2 digits. moving to 2 2 2 ignore my fixed number 2, count
the second 2 add this result to the column 2 digits then count the
final 2 wich then goes into a colum called 3 digits. And on upto 5
digits.
What Ive written Im sure will look clumsy and I hope you dont mind me
adding here.

Dim x As Integer
Dim W As Variant, s As Variant, t As Variant
Dim getcard As Variant
Dim trimcard As Variant
Dim xng As Range

Set xng = Range("g1:g13")

Range("a1").Select
CardString = Range("a1")
checkstring = Replace(Range("a1"), " ", "")


For i = 2 To Range("a2:a" & Range("a65536").End(xlUp).Row).Count +
1
Cells(i, 1).Select
trimcard = Replace(Cells(i, 1), " ", "")
getcard = Replace(trimcard, checkstring, "")

If Len(trimcard) Len(checkstring) Then


If Len(getcard) = 1 Then
s = 1
ElseIf Len(getcard) = 2 Then
s = 2
ElseIf Len(getcard) = 3 Then
s = 3
ElseIf Len(getcard) = 4 Then
s = 4
End If



For t = 1 To s

If Mid(getcard, t, 1) = "A" Then
x = 11
ElseIf Mid(getcard, t, 1) = "J" Then
x = 12
ElseIf Mid(getcard, t, 1) = "Q" Then
x = 13
ElseIf Mid(getcard, t, 1) = "K" Then
x = 14
ElseIf Mid(getcard, t, 1) = "T" Then
x = 10
Else
x = Mid(getcard, t, 1)
End If


If t = 1 And s = 1 Then
If Len(trimcard) = 2 Then
xng.Cells(x, 1) = xng.Cells(x, 1) + 1
ElseIf Len(trimcard) = 3 Then
xng.Cells(x, 1 + 1) = xng.Cells(x, 1) + 1
ElseIf Len(trimcard) = 4 Then
xng.Cells(x, 1 + 2) = xng.Cells(x, 1 + 2) +
1
ElseIf Len(trimcard) = 5 Then
xng.Cells(x, 1 + 3) = xng.Cells(x, 1 + 3) +
1
End If
ElseIf t = 1 And s 1 Then
If (Len(checkstring) + 1) = 2 Then
xng.Cells(x, 1) = xng.Cells(x, 1) + 1
ElseIf (Len(checkstring) + 1) = 3 Then
xng.Cells(x, 1 + 1) = xng.Cells(x, 1) + 1
ElseIf (Len(checkstring) + 1) = 4 Then
xng.Cells(x, 1 + 2) = xng.Cells(x, 1 + 2) +
1
ElseIf (Len(checkstring) + 1) = 5 Then
xng.Cells(x, 1 + 3) = xng.Cells(x, 1 + 3) +
1
End If
ElseIf t 1 And s 1 Then
If Len(trimcard) = 2 Then
xng.Cells(x, 1) = xng.Cells(x, 1) + 1
ElseIf Len(trimcard) = 3 Then
xng.Cells(x, 1 + 1) = xng.Cells(x, 1) + 1
ElseIf Len(trimcard) = 4 Then
xng.Cells(x, 1 + 2) = xng.Cells(x, 1 + 2) +
1
ElseIf Len(trimcard) = 5 Then
xng.Cells(x, 1 + 3) = xng.Cells(x, 1 + 3) +
1
End If
End If
Next t

End If

Next i



This works in a fasion, but given the data above, when trimcard = 2 and
checkstring = 22 then it replaces all the 2's and I need to trim only
the two.

Any advice appreciated .
Regards Robert