Strange results from offset formula

[Uncle Max]

Can anyone tell me what the problem is with this code snippet?

Range("D5").Formula = "=match(D6,ChTables!$A:$A,)"
Range("D7").Formula = "=OFFSET(ChTables!A1,D5,53)"
Range("D8").Formula = "=OFFSET(ChTables!A1,D5,47)"
Range("D9").Formula = "=OFFSET(ChTables!A1,D5,51)"
Range("D10").Formula = "=OFFSET(ChTables!A1,D5,72)"

The code for "D5", D7" & "D9" return the correct answers, although "D7" adds a decimal number to the referenced integer!

"D8" & "D10" both return 0, although the function arguments box (fx) show the correct numbers, so I must have the correct references.

Advice would be much appreciated

 

[wideboydixon]

MATCH() will return 1 for row 1, 2 for row 2 etc. You should also have a final parameter ideally (zero for exact match)
To use the value in D5 with your offset formula you'll need to subtract 1 from that value in order to find the right row.

Range("D5").Formula = "=MATCH(D6,ChTables!$A:$A,0)"
Range("D7").Formula = "=OFFSET(ChTables!A1,D5-1,53)"
Range("D8").Formula = "=OFFSET(ChTables!A1,D5-1,47)"
Range("D9").Formula = "=OFFSET(ChTables!A1,D5-1,51)"
Range("D10").Formula = "=OFFSET(ChTables!A1,D5-1,72)"

Not sure what you're doing, however, since you're adding a volatile function into the sheet which will make it slow. It might be better to just fetch the value you want rather than using the formula option.

WBD

 

[Uncle Max]

Many thanks - obvious when you think about it!