Replace Cell Value in formula as loop executes?

[JP Romano]

I'm sure I'm going to kick myself for this, but I just can't get the syntax right. Perhaps I'm a wee bit fried... anyway, hopefully one of you experts can set me straight again.

All I'm trying to do is increment a value in a formula as a loop exectues through a range. I can't put it in one cell and do a fill down throughout because I'll end up with inaccurate results. When I embed the api call in an if statement, only about 75% of them execute - so I'm basically looking for that 25% and refiring the call.

Here's what I have:

For i = 1 To 10000 Step 1

Range("F" & i).Select
If ActiveCell.Value = "#N/A Requesting Data..." Then
    ActiveCell.formula = "=BDP(A2&"" Equity"", ""best_eps"",""best_fperiod_override"",""1fy"",""best_data_source_override"",XRef!$D$1,""best_consolidated_override"",""C"")"
    
    End If
Next i

I just want to replace the A2 in :ActiveCell.for mula = "=BDP(A2&""
with A& the loop execution number.

I'm sure this is basic, but like I said, I'm fried! Thanks for any help!

[MikeTheBike]

I'm sure I'm going to kick myself for this, but I just can't get the syntax right. Perhaps I'm a wee bit fried... anyway, hopefully one of you experts can set me straight again.

All I'm trying to do is increment a value in a formula as a loop exectues through a range. I can't put it in one cell and do a fill down throughout because I'll end up with inaccurate results. When I embed the api call in an if statement, only about 75% of them execute - so I'm basically looking for that 25% and refiring the call.

Here's what I have:

For i = 1 To 10000 Step 1

Range("F" & i).Select
If ActiveCell.Value = "#N/A Requesting Data..." Then
    ActiveCell.formula = "=BDP(A2&"" Equity"", ""best_eps"",""best_fperiod_override"",""1fy"",""best_data_source_override"",XRef!$D$1,""best_consolidated_override"",""C"")"
    
    End If
Next i

I just want to replace the A2 in :ActiveCell.for mula = "=BDP(A2&""
with A& the loop execution number.

I'm sure this is basic, but like I said, I'm fried! Thanks for any help!

Hi

Perhaps somethink like

For i = 1 To 10000 
	If Cells(i,6) = "#N/A Requesting Data..." Then
		Cells(i,6) = "=BDP(A”  & i & """ Equity"", ""best_eps"",""best_fperiod_override"",""1fy"",""best_data_source_override"",XRef!$D$1,""best_consolidated_override"",""C"")" 
   End If 
Next i

??

MTB

[JP Romano]

Mike - thanks a million - will give it a shot today and see how it works out. Appreciate the help!