Index function skips value in table.

[Mike246]

Hi,

I'm using an index function in VBA and for some reason it misses a value in the table it refers to. The referred section of code is pasted below:

NewIDin = WorksheetFunction.Index(Sheet2.Range("B38:B55"), NewID, 1)

Where NewID ranges from 1 to 18. I've manually replaced NewID with 1 and 2 to see which values for NewIDin are returned although it misses the value in the 2nd row of the table. So if I put '...,1,1)' it returns the value in the first column, first row but if I put '...2,1) it returns the value in the 3rd row, 1st column. The first 3 values in the table are 1.0, 1.5 and 2.0 so in other words it misses out 1.5. I can't figure out why this is happening at the moment and would appreciate and help.

Thanks,
Mike

[lecxe]

Hi Mike
Welcome to the board

How is NewIDin declared?

Remark: always post the declarations of the variables.

[Paul_Hossler]

If NewIDin is Dim-ed as a Long, Excel will be nice and round the 1.5 to a 2 for you

If B38:B35 is all numeric and contains only non-integer number, try to Dim NewIDin as a Double

[Paul_Hossler]

If B38:B35 is all numeric and contains only non-integer number, try to Dim NewIDin as a Double


I should have said

"If B38:B35 is all numeric, try to Dim NewIDin as a Double or a Variant"

[snb]

I don't see any problem.

[Paul_Hossler]

I don't see any problem.

Not with yours, but ...

1. This assumes everything is default Dim-ed as a Variant



Private Sub CommandButton1_Click()
For newId = 1 To 18
MsgBox Application.Index(Sheet2.Range("B38:B55"), newId, 1), , "newId = " & newId
Next
End Sub




2. OP's data was different from yours:



The first 3 values in the table are 1.0, 1.5 and 2.0 so in other words it misses out 1.5. I can't figure out why this is happening at the moment and would appreciate and help.



Since the OP said that the results were not returning the expected 1.5 but instead returning 2, my GUESS was that NewIDin was Dim-ed as a Long or and Integer and Excel was converting the 1.5 into a 2



Without a more complete sample and a example workbook it is difficult to tell for sure

[snb]

Still no problem.

[Paul_Hossler]

Yes, but your variables are still defaulted to Variants


OP asked why it was happening ...



. I can't figure out why this is happening at the moment and would appreciate any help


... and without having a sample WB the best guess would be an inappropriately typed variable, i.e. the OP dim-ed it as a Long and Excel converted 1.5 to 2


My preference (and it is a preference) is to use Variant types only when truly needed

[SamT]

There is a problem.

The problem is that this bit of code ain't the problem.

NewIDin = WorksheetFunction.Index(Sheet2.Range("B38:B55"), NewID, 1)
That code is functionally identical to

NewIDin = WorksheetFunction.Index(Sheet2.Range("B38:B55"), NewID)
And to

NewIDin = Sheet2.Range("B38:B55").Cells(NewID)

From what I can see looking at the rest of the code in your book, I'm going with Paul's wrong variable type for NewIDin. I reserve the right to guess again after I get that dang castle out of my line of sight to your house.

[snb]

@Paul

In that case you gave a perfect example why you shouldn't declare variables unless absolutely necessary (if you need them private or public).
MS made the Variant the default with good reason.

[Aflatoon]

Purely for counterpoint, I don't agree that one incorrect variable declaration is a reason for not declaring any.

[SamT]

I can't speil tht well and I can't remember what a one character veariable is for in 10 minutes, let alone 10 weeks.

[Bob Phillips]

Purely for counterpoint, I don't agree that one incorrect variable declaration is a reason for not declaring any.

Nor can I. MS didn't create variant types so as to avoid declaring data types, if that were so, they wouldn't have bothered with any other data types. Talk about twisting the argument to fit your hypothesis.