Autofill formulas in Range

[Dr Fumanchu]

Hello,

New to VBA and having difficulties with the following routine.

I extract a csv file from string that I generate in a cell in G15 (sheet "Macro"). I need to clear all contents in a sheet in this workkbook called "data 1". I then need to copy the contents of the target sheet into this sheet. Where the data ends (after column "AO") I then need to create some formulas in the next four columns and pull those formulas down to as far as there is data in column A.

The code here has a couple of issues. Mainly this reference:

ThisWorkbook.Sheets("Data1").lastrow = Range("A" & Rows.Count).End(xlUp).Row



Any help/guidance apprecaited.

'''''''''''''''''''''''''''''''''''''''''''''''

Sub Import_Rds()
Dim lastrow As Long
Dim RdsFile As String

Application.ScreenUpdating = False
Application.CutCopyMode = False

'************************DATA1 RDS grab******************************

‘Retrieves source file to open for directory stored in G15 on “Macro” sheet

RdsFile = Range("G15").Value
Workbooks.Open Filename:=RdsFile

ThisWorkbook.Sheets("Data1").Cells.ClearContents
ActiveWorkbook.ActiveSheet.Range("A1:AO10000").Copy
Close
ThisWorkbook.Sheets("Data1").Range("A1:AO10000").PasteSpecial xlPasteValues

'Destination sheet - Add Bloomberg formulas and fill down to the formulas to range in column A

ThisWorkbook.Sheets("Data1").lastrow = Range("A" & Rows.Count).End(xlUp).Row
Range("AP2").Formula = "=IF(O2=""Equity"",BDP(K2,""CRNCY""),"" - "" "
Range("AP2").AutoFill Destination:=Range("AP2:AP" & lastrow), Type:=xlFillDefault

ThisWorkbook.Sheets("Data1").lastrow = Range("A" & Rows.Count).End(xlUp).Row
Range("AQ2").Formula = "=IF(O2=""Equity"",BDP(K2,""rel_index""),"" - "" "
Range("AQ2").AutoFill Destination:=Range("AQ2:AQ" & lastrow), Type:=xlFillDefault

ThisWorkbook.Sheets("Data1").lastrowRange("A" & Rows.Count).End(xlUp).Row
Range("AR2").Formula = "=IF(O2=""Equity"",BDP(K2,""GICS_industry_name""),"" - "" "
Range("AR2").AutoFill Destination:=Range("AR2:AR" & lastrow), Type:=xlFillDefault

ThisWorkbook.Sheets("Data1").lastrow.Range("A" & Rows.Count).End(xlUp).Row
Range("AS2").Formula = "=AD2"
Range("AS2").AutoFill Destination:=Range("AS2:AR" & lastrow), Type:=xlFillDefault

End sub


Example Formulas

' =IF($O2="Equity",BDP(K2,"CRNCY"),"-"
'=IF($O2="Equity",BDP($K2,"rel_index"),"-")
'=IF($O2="Equity",BDP($K2,"GICS_industry_name"),"-")
'=AD2

[davesexcel]

That one line should be

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[Dr Fumanchu]

Many thanks, the code works. now its we're having issues on Range("AP2").Formula = "=IF(O2=""Equity"",BDP(K2,""CRNCY""),"" - "" "
Also, is my handling of Thisworkbook and Activeworkbook correct?

Appreciate the help

[davesexcel]

What would the formula look like normally?

If you are only on one workbook, "ThisWorkbook" or "Activeworkbook" would be assumed and would normally just be required if you are manipulating a different workbook.

[Dr Fumanchu]

What would the formula look like normally?

If you are only on one workbook, "ThisWorkbook" or "Activeworkbook" would be assumed and would normally just be required if you are manipulating a different workbook.

Hi,

Would look like:
=IF($O2="Equity",BDP(K2,"CRNCY"),"-"

If o2 has equity in it then call bdp (api) referencing the data in K2.

[davesexcel]

I am not sure what bdp is, possibly a function?