#REF error not exported as text from Excel to txt with VBA

Hi,

I am exporting data from an Excel spreadsheet to txt with the following code:

Please Login or Register  to view this content.

The sheet contains various formulas but for one or another reason the code is not working when the formulas show a #REF error (I am expecting that this value is exported as simple text).

Any ideas what could be the reason?

Many thanks!

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Hello kavond,

Welcome to the Forum!

#REF is the text that Excel displays to indicate the cell contains an error. VBA also sees this an error but instead of displaying #REF, it displays the Excel error value of 2023.

You can get the text representation of a cell in VBA by using the Text property. Read the individual cell Text properties into a 2-D array and then format the array.

Alternatively you can use error handing in your formulas on the sheet like IFERROR(<Your Formula Here>,"") to show blank where your formulas return an error or IFERROR(<Your Formula Here>,"#Ref!") to show #Ref! error as a text. In both the cases your code should work fine.