VBA Code to Use Column Dim in Excel Formula

[M7A0S8T8E5R]

Good Afternoon,

I am working on a macro that will take a large amount of data, pivot it by source code. What I am wanting to build is a formula that will use my below endColumn dim in the formula as the number of columns in this report could be variable as we clean up source codes.

The below is the dim and the formula I am trying to use it in. I use offset to get to the cell I need to, that works fine. I want to amend the formula to accept the endColumn dim so the formulas will be variable.

When I run this code I get a Application defined or object-defined error.

Any help would be much appreciated!


Dim endRow As Long
Dim rangeOne As Range, rangeTwo As Range
Dim endColumn As Long

endRow = Sheets("CombinedPivot").Cells(Rows.Count, 1).End(xlUp).Row
endColumn = Sheets("CombinedPivot").Range("A1").End(xlToRight).Column



Selection.End(xlToRight).Offset(0, 1).Value = "Max of Source Code 1"
Range("A1").Select
Selection.End(xlToRight).Offset(1, -3).Value = "=IFERROR(LARGE(B2,&endColumn&,1),""N/A"")"

[TMS]

Try:

Selection.End(xlToRight).Offset(1, -3).Value = "=IFERROR(LARGE(B2," & endColumn & ",1),""N/A"")"

[M7A0S8T8E5R]

Good Morning and thank you for your response.

Apologies on the delay of my response, was tied up yesterday and getting back to this project now.

I made the change and I get the same application/object defined error.

[TMS]

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

[M7A0S8T8E5R]

Sample Data.xlsx

I have attached sample data, and the formula's I am using. This report will gain/lose source codes over time, which is why I want to set the formula to be variable, dependent on how wide the spreadsheet is.

I hope this helps to get this figured out. Below I included the code I am using, the bolded line is the line I updated with your suggestion.

Appreciate the help, and will do better to provide more information moving forward.

Thank you.



Sub FINALIZE_SOURCE_DATA_BY_PART()
Dim endRow As Long
Dim rangeOne As Range, rangeTwo As Range
Dim endColumn As Long

endRow = Sheets("CombinedPivot").Cells(Rows.Count, 1).End(xlUp).Row
endColumn = Sheets("CombinedPivot").Range("A1").End(xlToRight).Column


Selection.End(xlToRight).Offset(0, 1).Value = "Count of Source Code 1"
Selection.End(xlToRight).Offset(0, 1).Value = "Source Code 1"
Selection.End(xlToRight).Offset(0, 1).Value = "Count of Source Code 2"
Selection.End(xlToRight).Offset(0, 1).Value = "Source Code 2"
Range("A1").Select
Selection.End(xlToRight).Offset(1, -3).Value = "=IFERROR(LARGE(B2," & endColumn & ",1),""N/A"")"
Selection.End(xlToRight).Offset(1, -2).Value = "=XLOOKUP(JC2,B2:JA2,$B$1:$JA$1,""N/A"")"
Selection.End(xlToRight).Offset(1, -1).Value = "=IFERROR(LARGE(B2:JA2,2),""N/A"")"
Selection.End(xlToRight).Offset(1, 0).Value = "=XLOOKUP(JE2,B2:JA2,$B$1:$JA$1,""N/A"")"

End Sub

[TMS]

Try this:

Option Explicit

Sub FINALIZE_SOURCE_DATA_BY_PART_TMS()

Dim lEndRow As Long, lEndColumn As Long, sEndColumn As String

Application.ScreenUpdating = False

With Sheets("CombinedPivot")
    lEndRow = .Cells(Rows.Count, 1).End(xlUp).Row
    lEndColumn = .Range("A1").End(xlToRight).Column
    ' check if macro has been run before
    If .Cells(1, lEndColumn).Value = "Source Code 2" Then
        ' and adjust the last column number if it has
        lEndColumn = lEndColumn - 4
    End If
    ' determine the last column letter(s)
    sEndColumn = Split(Cells(lEndRow, lEndColumn).Address, "$")(1)

    With .Cells(1, lEndColumn)
        ' drop the headings into row 1
        .Offset(0, 1).Value = "Count of Source Code 1"
        .Offset(0, 2).Value = "Source Code 1"
        .Offset(0, 3).Value = "Count of Source Code 2"
        .Offset(0, 4).Value = "Source Code 2"
        ' drop the formulae into *ALL* the rows
        .Offset(1, 1).Resize(lEndRow - 1).Formula = "=IFERROR(LARGE(B2:" & sEndColumn & "2,1),""N/A"")"
        .Offset(1, 3).Resize(lEndRow - 1).Formula = "=IFERROR(LARGE(B2:" & sEndColumn & "2,2),""N/A"")"
        .Offset(1, 2).Resize(lEndRow - 1).Formula2 = "=XLOOKUP(JB2,B2:" & sEndColumn & "2,$B$1:" & sEndColumn & "$1,""N/A"")"
        .Offset(1, 4).Resize(lEndRow - 1).Formula2 = "=XLOOKUP(JD2,B2:" & sEndColumn & "2,$B$1:" & sEndColumn & "$1,""N/A"")"
    End With
End With

Application.ScreenUpdating = True

End Sub

[M7A0S8T8E5R]

Thank you for the help TMS, it worked great!

[bakerman2]

To be on the safe side.

lEndColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column

instead of

lEndColumn = .Range("A1").End(xlToRight).Column

This

        .Offset(0, 1).Value = "Count of Source Code 1"
        .Offset(0, 2).Value = "Source Code 1"
        .Offset(0, 3).Value = "Count of Source Code 2"
        .Offset(0, 4).Value = "Source Code 2"

can also be written as

.Offset(0, 1).Resize(, 4).Value = Array("Count of Source Code 1", "Source Code 1", "Count of Source Code 2", "Source Code 2")

[TMS]

You're welcome. Thanks for the rep.

bakerman2 suggested this:

lEndColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column

That is also what I would normally use and it is more reliable if there might be gaps in the data in row 1. If there are no gaps, the result is the same.

The second suggestion is a nice touch.


Also, you may not be aware that you can thank those who have helped you by clicking the small star icon (Next to Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[bakerman2]

You're welcome and thanks for Rep+.

[TMS]

Thanks for the rep.