CODEWITHSUNDEEP
excelexcel-formulavba
HobbyHacker
HobbyHacker

Reputation: 11

Inserting Excel formula that refers to alternative columns

I am trying to insert the following formula ='External Costs B0'!F73 in the same row of a sheet in consecutive columns, however with the cell reference "F73" in worksheet "External Costs" referring to alternate columns e.g F73, H73, J73, L73 etc.

Here is a screen shot of the spreadsheet with formula commented

Screen Capture of formula sheet

This is the code I have tried but I am struggling to figure how to get the alternative column reference working.

Dim CostColumns As Long

'Select cell to start inserting the formula from

Range("E26").Select

'Start from column F (6) in the "External Costs B0" sheet and step to every alternate column

For CostColumns = 6 To 600 Step 2
    ActiveCell.Formula = "= ""'External Costs B0'!"" & Rows(73)Columns(CostColumns)"

Move to the next cell to insert the formula in and advance the column reference by 2 columns

    ActiveCell.Offset(0, 1).Select
Next CostColumns

The net result is the same error I have seen in many posts:

Application-defined or object-defined error.

Here are many other syntax's for the formula insert I have tried with no success. Any help is greatly appreciated. The below refers to row 40 instead of row 73 in the External Costs B0 sheet as per the example above.

Range("E26").Select
 For CostColumns = 6 To 66 Step 2
    'ActiveCell.FormulaR1C1 = "= worksheets("""External Costs B0""").Cells(40,6).Value"
    'ActiveCell.FormulaR1C1 = "='External Costs B0'!R[14]C[CostColumns]"
    'Range("E26:AK26").FormulaR1C1 = "='External Costs B0'!R[14]C[CostColumns]"
    'ActiveCell.FormulaR1C1 = "= worksheets('External Costs B0')!" & " Rows(40)Columns(CostColumns)"
    'Range("E26:AK26").FormulaR1C1 = "='External Costs B0'!R[14]C[+2]"
    'Range("E26:AK26").FormulaR1C1 = "=Wksht.Cells(40,CostColumns) &"
    'Range("E26:AK26").Formula = "=worksheets('External Costs B0'!)" & ".Cells(40,6)"
    'ActiveCell.Formula = "= worksheets('External Costs B0'!).Cells(40,6).Value"
     ActiveCell.Offset(0, 1).Select
Next CostColumns

Upvotes: 0

Views: 451

Answers (3)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19857

This code will place your formula in cells Sheet1!A1:AD1.

The formula in A1 will be ='External Costs B0'!$A$73.
In B1 it will be ='External Costs B0'!$C$73 and so on up to ='External Costs B0'!$BG$73 in cell AD1. 

Sub PasteFormula()

    Dim CostColumns As Long
    Dim y As Long

    'Starting column for External Costs reference
    CostColumns = 1

    With ThisWorkbook.Worksheets("Sheet1")
        For y = 1 To 30
            .Cells(1, y).FormulaR1C1 = "='External Costs B0'!R73C" & CostColumns
            CostColumns = CostColumns + 2
        Next y
    End With

End Sub
  • To update the code change Sheet1 to whichever sheet you need to formula to appear in.
  • Change CostColumn=1 to the correct column number you want the formula to refer to.
  • Change y = 1 To 30 to the correct columns you want the formula to appear in.

The code uses R1C1 syntax as it's easier to update a formula if you only need to deal with row & column numbers R73C2 is row 73, column 2 for example.

Upvotes: 0

Jochen
Jochen

Reputation: 1254

Its way easier, if you use FormulaR1C1 and no selects like in the following sub: (please change R, RowOffset and FormulaUntilColumn to your needs)

Sub Formulas()
Dim I As Integer
Const R As Long = 9     'row
Const RowOffset As Integer = -8
Const ForumulaUntilColumn As Long = 7
    For I = 1 To ForumulaUntilColumn
        Cells(R, I).FormulaR1C1 = "=R[" & RowOffset & "]C[" & I - 1 & "]"
    Next I
End Sub

P.S.: add workbook and table name as in any other formula between '=' and 'R['

Upvotes: 0

user4066647
user4066647

Reputation:

Use this:

ActiveCell.Formula = "='External Costs B0'!" & Cells(73, CostColumns).Address(0, 0)

The problem with that is that Rows(73) and Columns(CostColumns) both return a Range object which you can't concatenate to a string.

Upvotes: 1

Related Questions