VBA - Copying and pasting certain output of a function to worksheet

[miked35]

Hi,

I am using a Bloomberg function that gives its output on 2 lines (rows). The function is looping over a large number of cells so what I would like to happen is for the function to run on cell 'k' then to take paste the first line of output into another worksheet. Before moving to the next k I would like the contents of cell k + 1, (the cell directly below the cell using the formula) to be cleared. Here is my code, please note I have reduced the loop until I find the appropriate solution.




Sub bdX_R1()
Dim k As Long
Dim arrNames As Variant
Dim l As Long
Dim DestSheet1 As Worksheet
Set DestSheet1 = Worksheets("Sheet2")




arrNames = Range("A2:A18136").Value

For k = 2 To 18
Cells(k + 1, "F").Formula = "=bds(" & Chr(34) & arrNames(k, 1) & Chr(34) & ", " & _
Chr(34) & "BOND_CHAIN" & Chr(34) & ", " & Chr(34) & "dir = h" & Chr(34) & ")"
If Cells(k + 1, "F").Value = "#N/A N/A" Then
Cells(k + 1, "F").ClearContents
ElseIf Cells(k + 1, "F").Value = "N/A Invalid Security" Then
Cells(k + 1, "F").ClearContents
Else: Cells(k + 1, "F").Value.Copy Destination:=DestSheet1.Cells(k, "A")
Cells(k + 1, "B").Value.Copy Destination:=DestSheet1.Cells(k, "B")
Cells(k + 2, "F").ClearContents
End If

Next k
End Sub

 

[SkipVought]

hi,

I am using a Bloomberg function that gives its output on 2 lines (rows). The function is looping over a large number of cells so what I would like to happen is for the function to run on cell 'k' then to take paste the first line of output into another worksheet

Where is your function.

You posted a Sub, not a function.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[miked35]

Hi Skip,

What I mean by function is the built in excel function "BDS." It is an Excel function provided by Bloomberg. I am not writing a function just using this built in one.

Thanks

Mike

 

[SkipVought]

A UDF cannot return a value to any other cell but the cell in which the function resides. So a UDF cannot "then to take paste the first line of output into another worksheet."

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

But your sub could COPY the cell in which the UDF resides and PasteSpecial xlPasteValues to the other sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[miked35]

After a little bit of debugging I realize that my problem is that the data is "being requested" while the loop moves to the next k. I need the formula to populate the cells before moving to the next k. The output is x columns and 2 rows which is why I would like to remove the second row before looping to the next k. Is there an method for ensuring this happens?

 

[SkipVought]

Please keep ALL your RELATED questions in one thread, rather than what you have done.

How is this IMPORT taking place?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[miked35]

Okay will do, and the import is taking place on the line

Cells(k + 1, "F").Formula = "=bds(" & Chr(34) & arrNames(k, 1) & Chr(34) & ", " & _ Chr(34) & "BOND_CHAIN" & Chr(34) & ", " & Chr(34) & "dir = h" & Chr(34) & ")"

The only issue is that the data is not imported to the current cell before the if statement begins.

 

[SkipVought]

WHY are you adding this function via VBA code?

WHY can it not be done directly on your sheet in your table WITHOUT VBA?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[miked35]

Skip, I do agree and would have preferred to use it directly in the worksheet. However, I have around 15000 different equities that I am using, and the formula gives 2 rows and many columns of output so I cant just drag the formula down the spreadsheet. I have not found a way of suppressing the second row of output in the cell formula.

 

[SkipVought]

I do not understand.

On the one hand you insert the function via code.

On the other hand you insert the funtion ONE TIME by hand then Copy & Paste thru whatever rows & columns as required.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[miked35]

When copying and pasting the second row of output from say cell k, overlaps the first row of output for cell k+1.

 

[SkipVought]

???

A spreadsheet function cannot return a value to any other cell but the cell in which it resides???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[miked35]

If you have access to a bloomberg terminal try the following in a cell,

=bds("AIR EQUITY", "BOND_CHAIN")

the output will contain 2 rows of data, 4 columns.

 

[SkipVought]

I have access to no such terminal.

So you are saying that if this formula is in F1, that you will end up with data in all these cells -- F1:I2 after you hit ENTER?


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Don't you need to 1) select the 8 cells, 2) enter the formula, 3) hit shift+ctr+enter as an ARRAY FORMULA?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[miked35]

Skip, regarding your first post, yes exactly. Regarding your second, I do not select any cells, I even tried adding the field in the formula to set the rows = 1 and the columns flexible but when the formula runs it seems to override this. I will search on array fomulas as I am unfamilar with the method you describe. Appreciate the time.

Mike

 

[Deniall]

If this BDS thinggummy is an array function, you can extract a single number from the array it returns by using the INDEX function. Thus:
=INDEX(BDS(…,…)1,1)

 

[Deniall]

... but preferably with a comma after the first closing bracket.