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:

Please Login or Register  to view this content.

[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:

Please Login or Register  to view this content.

[bakerman2]

To be on the safe side.

Please Login or Register  to view this content.

instead of

Please Login or Register  to view this content.

This

Please Login or Register  to view this content.

can also be written as

Please Login or Register  to view this content.

[M7A0S8T8E5R]

Thank you for the help TMS, it worked great!

[TMS]

You're welcome. Thanks for the rep.

bakerman2 suggested this:

Please Login or Register  to view this content.

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.