I use excel with a xla add in to get data from a system called Khalix.

Anyhow, I call a function to refresh data in my workbook and need to figure
out a way to confirm that it finished getting the data. Some formulas return
#Value! when the data is not retrieved/refreshed. My solution is to loop
until the cell doesn't equal #VALUE, but I get a type mismatch error when
checking. I've used the immediate window and the cell value contains Error
2015 not #VALUE.

Do Until lws_Template.Range("e13").Value < "Error 2015"
For li_counter2 = 1 To 10000
Next li_counter2
lws_Template.Calculate
Loop


Any ideas?

Should your lws_Template.Calculate line be inside the For...Next loop?

"illini_99" wrote:

I use excel with a xla add in to get data from a system called Khalix.

Anyhow, I call a function to refresh data in my workbook and need to figure
out a way to confirm that it finished getting the data. Some formulas return
#Value! when the data is not retrieved/refreshed. My solution is to loop
until the cell doesn't equal #VALUE, but I get a type mismatch error when
checking. I've used the immediate window and the cell value contains Error
2015 not #VALUE.

Do Until lws_Template.Range("e13").Value < "Error 2015"
For li_counter2 = 1 To 10000
Next li_counter2
lws_Template.Calculate
Loop


Any ideas?

Yes, I believe it should.

The code fails on the Do Until line and never gets to the calculate function.

A lot of these workbooks are used with calculation mode set to manual, so I
am manually ensuring it re-calcs to ensure the #VALUE would resolve itself if
the data was retrieved.

Prior to retrieving the data, the Khalix proprietary Kfcell function returns
[data not read]. I pull in financial numbers and round to the nearest
thousand. The text [data not read] divided by the 1000 produces the #VALUE
error.

"JLGWhiz" wrote:

Should your lws_Template.Calculate line be inside the For...Next loop?

"illini_99" wrote:

I use excel with a xla add in to get data from a system called Khalix.

Anyhow, I call a function to refresh data in my workbook and need to figure
out a way to confirm that it finished getting the data. Some formulas return
#Value! when the data is not retrieved/refreshed. My solution is to loop
until the cell doesn't equal #VALUE, but I get a type mismatch error when
checking. I've used the immediate window and the cell value contains Error
2015 not #VALUE.

Do Until lws_Template.Range("e13").Value < "Error 2015"
For li_counter2 = 1 To 10000
Next li_counter2
lws_Template.Calculate
Loop


Any ideas?

You could check for the text:

Do Until lws_Template.Range("e13").Text < "#VALUE!"

or maybe you could look for any old error

Do Until not iserror(lws_Template.Range("e13").Value)



illini_99 wrote:

I use excel with a xla add in to get data from a system called Khalix.

Anyhow, I call a function to refresh data in my workbook and need to figure
out a way to confirm that it finished getting the data. Some formulas return
#Value! when the data is not retrieved/refreshed. My solution is to loop
until the cell doesn't equal #VALUE, but I get a type mismatch error when
checking. I've used the immediate window and the cell value contains Error
2015 not #VALUE.

Do Until lws_Template.Range("e13").Value < "Error 2015"
For li_counter2 = 1 To 10000
Next li_counter2
lws_Template.Calculate
Loop


Any ideas?

--

Dave Peterson