VBA UDF range function loop

[hfler]

I have the following loop in a UDF function,

Function(entry as Range, date as Range)
 
Dim answer as Variant
 
For j=1 to 10
   IF something THEN 
   answer = entry(j)
      IF something else THEN Exit For
      End If
   End If
Next j

I have 10 cells in 2 separate columns highlighted for my range and date variables. This code doesn't work if I start at j=0, but the first entry I want to loop with is in the cell j=0. Any advice on how to structure this?

 

[Scott Huish]

Your function also needs a name. I also wouldn't use date as a variable name as this is a VBA keyword. date isn't referenced in your function, what's it for?

Can you explain what you are trying to accomplish exactly?

 

[hfler]

Hi Hotpepper,

Thanks for your reply. I only recreated a scratch version of my code - my original code doesn't contain the problems you mentioned (function name missing, date used as variable). I didn't want to paste my whole code because most of it's functioning properly, it's just when I change the beginning of the loop to j=0 that I get a "#VALUE!" returned; the loop has to start at j=0, because that's the first value in the input range "entry", and I need to incorporate that into the code. Do you have any idea insight into how I can do this? Thanks very much for your help - I'm a new VBA user and as a result run into a lot of issues, any advice is much appreciated!

 

[Scott Huish]

Why don't you just use a For Each Next loop to iterate through your range?

 

[xenou]

This code doesn't work if I start at j=0, but the first entry I want to loop with is in the cell j=0

I think that ranges are 1-based. The first cell of a range is cells(1). Why do you *think* you have an entry(0)?