CODEWITHSUNDEEP
excelvba
Kyle Massimilian
Kyle Massimilian

Reputation: 11

How do I set a range across columns rather than down rows?

I am trying to create a formula to use in a larger macro.

I want to drag the EOMONTH formula across a row from the first date to the last filled column.

Sub DragFormula()
    'Declare variables
    Dim ws As Worksheet
    Dim lastCol As Long
    Dim formulaRange As Range

    'Set the worksheet to use
    Set ws = ThisWorkbook.Sheets("Sheet1")

    'Find the last filled cell in row 1
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

    'Set range to apply the formula to
    Set formulaRange = ws.Range("B1:B" & lastCol)

    'Enter the formula
    formulaRange.Formula = "=EOMONTH(A1,1)"
End Sub

The formula is copied down column B instead of across. I know I need to change the notation in this line Set formulaRange = ws.Range("B1:B" & lastCol).

When I modify the line to Set formulaRange = ws.Range("B1:" & lastCol) I get:

Run-time error '1004': Method 'Range' of object '_Worksheet' failed

How do I set a range across columns rather than down rows?

Upvotes: 0

Views: 65

Answers (1)

Cameron Critchlow
Cameron Critchlow

Reputation: 1827

Is this what you're trying to do?

THIS:
enter image description here TO THIS:
enter image description here

Option Explicit

Sub DragFormula()

    'Declare variables
    Dim ws As Worksheet
    Dim lastCol As Long
    Dim formulaRange As Range

    'Set the worksheet to use
    Set ws = ThisWorkbook.Sheets("Sheet1")

    'Find the last filled cell in row 1
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column - 1

    'Set range to apply the formula to
    Set formulaRange = ws.Range("B1").Resize(1, lastCol)

    'Enter the formula
    formulaRange.Formula = "=EOMONTH(A1,1)"
    
End Sub

Upvotes: 3

Related Questions