How to fix #REF! as using Array formula to copy and paste data?

[feipezi]

Hi,

Please check the code:


Function GetValuesFromAClosedWorkbook(fPath, fName, sName, c_Rng, b_Rng As String)
With ActiveSheet.Range(b_Rng)
.FormulaArray = "='" & fPath & "\[" & fName & "]" & sName & "'!" & c_Rng
.Value = .Value
End With
End Function

Sub GetData()
Sheets("RawData").UsedRange.Clear
Sheets("RawData").Activate
GetValuesFromAClosedWorkbook "C:\Oncology\tmp\", "AFRB_TERR_DATA.xlsx", "AFRB_TERR_DATA", "A1:JN150", "A1:JN150"
Sheets("RawData").Activate
x = Columns("a").Cells.Find(What:="0", LookAt:=xlWhole, LookIn:=xlValues).Row
y = ActiveSheet.UsedRange.Rows.Count
col = ActiveSheet.UsedRange.Columns.Count
Range(Cells(x, "a"), Cells(y, col)).Clear
End Sub


As I run the code, get error on "x=Columns("a").Cells.Find...." simply because the data I get from AFRB_TERR_DATA.xlsx all turns into "#REF!". But why?
I checked the source tab, the data are perfectly OK. But it has lots of columns (274). It cannot be memory problems, can it? The funny thing is that sometimes
it comes out fine; but most times, it's just #REF!.

Thanks in advance.

 

[SkipVought]

Hi,

What happens on the sheet if you BREAK on the line after the function?

 

[feipezi]

Thanks for the quick return.

If I have a BREAK on Sheets("RawData").Activate, then the tab was already full of #REF!; if I did it before that line, nothing happened to the RawData tab since it was a BREAK. Either way, the BREAK line will turn yellow. That's it.

Thanks again.

 

[SkipVought]

The the #REF! has nothing to do with the statement in question!

As I run the code, get error on "x=Columns("a").Cells.Find...."

It your FUNCTION that is the problem.

I'd use MS Query if its a table that you're going after. Much cleaner IMNSHO.

 

[feipezi]

I tried ExecuteExcel4Macro, which functions similar to GetValuesFromAClosedWorkbook, I got exactly the similar bunch of #REF!.

I guess I'd better try some VBS or the usual way of opening the source book and copy & paste and close the book.

Thanks again.

 

[SkipVought]

No MS Query try?

 

[combo]

Have you tried R1C1 reference style instead (see

https://msdn.microsoft.com/en-us/library/office/ff837104.aspx)?

combo

 

[feipezi]

Hi folks,

Sorry about the late response.

I tried what you guys suggested, except MS Query. I thought MS Q was for pretty large data files but mine has only 112 rows but lots of columns (274). Besides, my job is to set up the application, the way as simple as possible. Now I have Excel Macros, VBS and SAS, which are enough for the users to swallow. Bringing in MS Query will add another headache.

I tried R1C1 style but the function won't take it by giving me "1004" and debug highlights "With ActiveSheet.Range(b_Rng)". It looks like b_Rng cannot be found. Of course not...it's a blank sheet.

I was in a hurry so I had to get it done so I used the most inefficient way of doing it. It worked but I still like the way that did not work better.

Thank you all.


Sub GetData()
Application.ScreenUpdating = False
Application.DisplayAlerts = 0
Dim obook As Workbook
On Error Resume Next
Sheets("RawData").Delete
On Error GoTo 0
Worksheets.Add(after:=Sheets("ParmTab")).Name = "RawData"
Set obook = Workbooks.Open("C:\jqzhang\kmk\Oncology\roambi\tmp\AFRB_TERR_DATA.xlsx")
ActiveWorkbook.Sheets("AFRB_TERR_DATA").Cells(1, 1).CurrentRegion.Copy
ActiveWindow.WindowState = xlMinimized
ActiveSheet.Paste
obook.Close SaveChanges:=False
ActiveWindow.WindowState = xlMaximized
Cells(1, 1).Select
Application.ScreenUpdating = True
End Sub

Sub DDECP()
Call GetData
totrows = Sheets("RawData").Cells(1, 1).CurrentRegion.Rows.Count
Sheets("RawData").Cells(1, 1).CurrentRegion.Offset(1).Resize(totrows - 1).Copy Sheets("report").Cells(3, 1)
End Sub

 

[SkipVought]

I thought that MS Q was for pretty large files..."

WRONG!

MS Q looke like a pretty SIMPLE solution IMMSHO.