Solved: Check if numerical with no decimal

[Djblois]

I have created a part of my addin to allow users to add product information to a spreadsheet without typing them all in, all they need to type in is the product code. I am trying to make the addin more intuitive by guessing what column holds the product code which would be a six digit numeral with no decimals and is always positive. This is what I have so far:

[VBA]If Len(ActiveCell.Offset(, -1)) = 6 Then
frmExtraAddTo.sbColDiff.Value = -1
ElseIf Len(ActiveCell.Offset(, -2)) = 6 Then
frmExtraAddTo.sbColDiff.Value = -2
ElseIf Len(ActiveCell.Offset(, -3)) = 6 Then
frmExtraAddTo.sbColDiff.Value = -3
ElseIf Len(ActiveCell.Offset(, -4)) = 6 Then
frmExtraAddTo.sbColDiff.Value = -4
ElseIf Len(ActiveCell.Offset(, -5)) = 6 Then
frmExtraAddTo.sbColDiff.Value = -5
ElseIf Len(ActiveCell.Offset(, -3)) = 6 Then
frmExtraAddTo.sbColDiff.Value = -6
ElseIf Len(ActiveCell.Offset(, -6)) = 6 Then
frmExtraAddTo.sbColDiff.Value = -3
ElseIf Len(ActiveCell.Offset(, -7)) = 6 Then
frmExtraAddTo.sbColDiff.Value = -7
ElseIf Len(ActiveCell.Offset(, -8)) = 6 Then
frmExtraAddTo.sbColDiff.Value = -8
ElseIf Len(ActiveCell.Offset(, -9)) = 6 Then
frmExtraAddTo.sbColDiff.Value = -9
ElseIf Len(ActiveCell.Offset(, -10)) = 6 Then
frmExtraAddTo.sbColDiff.Value = -10
End If[/VBA]

it works correctly but I also want it to test if it is a number and no decimal. Checking if it is positive is easy. Also, I will change this into a loop after I get it working.

[Djblois]

I have added the IsNumeric, which I knew how to do but I still can't figure out how to test if it has a decimal.

[VBA]If Len(ActiveCell.Offset(, -1)) = 6 And IsNumeric(ActiveCell.Offset(, -1)) Then
frmExtraAddTo.sbColDiff.Value = -1
ElseIf Len(ActiveCell.Offset(, -2)) = 6 And IsNumeric(ActiveCell.Offset(, -2)) Then
frmExtraAddTo.sbColDiff.Value = -2
ElseIf Len(ActiveCell.Offset(, -3)) = 6 And IsNumeric(ActiveCell.Offset(, -3)) Then
frmExtraAddTo.sbColDiff.Value = -3
ElseIf Len(ActiveCell.Offset(, -4)) = 6 And IsNumeric(ActiveCell.Offset(, -4)) Then
frmExtraAddTo.sbColDiff.Value = -4
ElseIf Len(ActiveCell.Offset(, -5)) = 6 And IsNumeric(ActiveCell.Offset(, -5)) Then
frmExtraAddTo.sbColDiff.Value = -5
ElseIf Len(ActiveCell.Offset(, -6)) = 6 And IsNumeric(ActiveCell.Offset(, -6)) Then
frmExtraAddTo.sbColDiff.Value = -6
ElseIf Len(ActiveCell.Offset(, -7)) = 6 And IsNumeric(ActiveCell.Offset(, -7)) Then
frmExtraAddTo.sbColDiff.Value = -7
ElseIf Len(ActiveCell.Offset(, -8)) = 6 And IsNumeric(ActiveCell.Offset(, -8)) Then
frmExtraAddTo.sbColDiff.Value = -8
ElseIf Len(ActiveCell.Offset(, -9)) = 6 And IsNumeric(ActiveCell.Offset(, -9)) Then
frmExtraAddTo.sbColDiff.Value = -9
ElseIf Len(ActiveCell.Offset(, -10)) = 6 And IsNumeric(ActiveCell.Offset(, -10)) Then
frmExtraAddTo.sbColDiff.Value = -10
End If[/VBA]

[unmarkedhelicopter]

decimal ???
you mean :-
If activecell.value = int(activecell.value) then
???

[Djblois]

Yes, but I want it to skip it if it has a decimal but stop if it (is a number, doesn't have a decimal, is 6 digits long, is positive)

[Bob Phillips]

[vba]

With ActiveCell
If Len(.Offset(, -1)) = 6 And IsNumeric(.Offset(, -1)) And .Value = Int(.Value) Then
frmExtraAddTo.sbColDiff.Value = -1
'etc.
[/vba]

[charlesa920]

Are these 6 digit intergers limited to actual current product codes? Or are your users creating new product codes?

[Djblois]

Xld, thank you. Unfortunately it is giving me a "Type Mismatch" error with that code added.

I figured out why it is giving me the type mismatch (it was testing a percentage), however it is still finding a number with a decimal in it.

[Djblois]

Charlesa,

I have a list of codes that I update when new products are added.

[Bob Phillips]

I missed the offset

[vba]

With ActiveCell.Offset(, -1)
If Len(.Value) = 6 And IsNumeric(.Value) And .Value = Int(.Value) Then
frmExtraAddTo.sbColDiff.Value = -1 'etc.
[/vba]

[Djblois]

Xld, that fixed the crash (sorry I didn't notice something so simple) but it is still finding numbers with a decimal. I don't want that, I want it to find it only if there is no decimal.

This is what I currently have:

[VBA]For intTestCol = -1 To -15 Step -1
With ActiveCell.Offset(, intTestCol)
If Len(.Value) = 6 Or Len(.Value) = 5 And IsNumeric(.Value) _
And .Value = Int(.Value) Then
frmExtraAddTo.sbColDiff.Value = intTestCol
GoTo AddNextColumn
End If
End With
Next intTestCol[/VBA]

I removed your code Xld until I can get it to work correctly.

[Bob Phillips]

[vba]

For intTestCol = -1 To -15 Step -1
With ActiveCell.Offset(, intTestCol)
If (Len(.Value) = 6 Or Len(.Value) = 5) And IsNumeric(.Value) _
And .Value = Int(.Value) Then
frmExtraAddTo.sbColDiff.Value = intTestCol
GoTo AddNextColumn
End If
End With
Next intTestCol
[/vba]

[charlesa920]

Djblois vbmenu_register("postmenu_126204", true);
are your working on a Purchase Order, Service Order, Inventory List, or similar?

And are these numbers truly numbers or can they be numeric text?

[Djblois]

xld,

I know that is how I add it in, I tried that already and it still finds numbers with decimals, I want the opposite (no decimals)

Charlesa,

I am not working on any of those, it is part of my add-in where a user can type in a column of product numbers then goto the column that they want to put the product into and press a button on my toolbar and it will add the product info. However, I want it to guess the column that the product code is in now.

Thank you both for the help.

[Djblois]

Xld my apologies I notice you put paranthesis around my or and I tried that and it works perfectly. You are the man.

[Djblois]

In case anybody else has a need for the code here is the final version:

[VBA]For intTestCol = -1 To -15 Step -1
With ActiveCell.Offset(, intTestCol)
If IsNumeric(.Value) Then
If (Len(.Value) = 6 Or Len(.Value) = 5) And .Value = Int(.Value) Then
frmExtraAddTo.sbColDiff.Value = intTestCol
GoTo AddNextColumn
End If
End If
End With
Next intTestCol[/VBA]

I put the isnumeric on a seperate if statement because if it is in the same one then the int() causes it to crash.

[Bob Phillips]

That is because of a practice called short-circuiting, or lack of it in the case of VBA. VBA will evalaute each part of an IF statement, even if gets a TRUE value in an early part. Pain, but ther you are.