CODEWITHSUNDEEP
vbafloating-pointinteger
user11077504
user11077504

Reputation:

Check for integer value vs float value

I My vba doesnt seem to quite work. I need it to multiply a value in column N by 2 if it is a decimal and do nothing for all other values.

currently it is

Sub multiply()
Dim i, j, k, l As Integer

Dim n As Variant

i = Application.InputBox("enter end row")

j = Application.InputBox("enter factor")

For k = 1 To i

n = Cells(k, 14) * j

If VarType(n) = vbSingle Then Cells(k, 14) = n

Next k

End Sub

This does nothing. Dont know why

Upvotes: 0

Views: 984

Answers (1)

Matteo NNZ
Matteo NNZ

Reputation: 12655

Your For loop can be written like this: 

For k = 1 To i

    If Cells(k, 14) <> Int(Cells(k, 14)) Then 'if it's decimal
        Cells(k, 14) = Cells(k, 14) * j 'then multiply cell by factor
    End If

Next k

Note that I determine whether it's decimal by checking if the Int() of that number is different than the number itself.

A couple of tips:

  • If it's always the end row you look for, you don't need to ask the input to the user. You can use endRow = Cells(1,14).End(xlDown).Row (assuming your data starts from the first cell of the column 14).
  • Use better naming of the variables, you will thank yourself if you'll re-read this code in few weeks. For example, j should be called multiplyFactor, i should be called endRow.
  • When you write Dim i, j, k, l As Integer, you are declaring i, j and k as Variant (default type) and only l as Integer that you're not even using in your code (at least not in what you posted). You should write instead Dim i As Integer, j As Integer, k As Integer, l As Integer.

Upvotes: 1

Related Questions