#REF! Error when exporting Concatenated Cell VIA VBA code

Hi All,

I wrote a code that exports a cell to a .txt file. It is working properly with every cell, with the exception of the Concatenated cells. When exporting a concatenated cell, the text file reads #REF! only.

I've debugged this error on a previous project but I cannot seem to figure this one out.

Any help would be greatly appreciated.

I'll attach two excel files - one where the macro exports the concatenated cell correctly and the other where I am getting the error.

Thank you,

Alec

"Work in Progress" - try setting the cell to be copied to (2,27). I might have sent over the file testing a non-concatenated cell.

Hi,

What is the VBA procedure name ?

I'm not sure how to upload a screen shot here, but this is what I'm referencing:

Sub GenerateNote()
'Update 20130913
Dim wb As Workbook
Dim saveFile As String
Dim WorkRng As Range


On Error Resume Next


Set WorkRng = ActiveWorkbook.Sheets("NOTE").Cells(4, 24)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wb = Application.Workbooks.Add
WorkRng.Copy
wb.Worksheets(1).PasteSpecial
saveFile = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")
wb.SaveAs Filename:=saveFile, FileFormat:=xlText, CreateBackup:=False
wb.Close
Application.CutCopyMode = xlCopy
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Originally Posted by Marc L

Hi,

What is the VBA procedure name ?

"Generate Note()"


The code itself works. I've narrowed down the error to the cell/function of concatenate.

Originally Posted by rorya

That's not surprising as you are copying the formula. It refers to the cells three columns to the left, and if you copy that to column A, there are no cells three columns to the left.

This seems like a lot of overhead to write one cell to a text file.

Yeah, it may seem like overkill, but it is a time saver in the long run.

Which formula are you referring to? When I fill out the chart, Cell AA2 populates correctly.

Remove "On Error Resume Next", so that you will find

Please Login or Register  to view this content.

PasteSpecial is a method of Range object, not worksheet object.

Originally Posted by rorya

you could just use file IO:

Please Login or Register  to view this content.

That worked brilliantly!

Thank you very much!

Rorya, can you help me understand where I went wrong? I'm new to VBA but would love to learn from my mistakes.

Ahh I see, so if I'm understanding you correctly, I could have used absolute reference for the formula via $X, but that would have been overkill to re-type that for 91 cells.

I'm going to delve into this more, but again, I really appreciate your help!