Good morning.
Full Explanation:
I am trying to create a header based on User Input, but inline with where the actual header is.
Ex: If User Input is Electricity, then macro searches for Electricity, then jumps down to the predetermined last row, based on a different column and outputs the User Input as a Value.
I was testing a few potential solutions, but no such luck. I believe the issue lies with the final line.
In the test data, the Search parameters find the User Input in Cell O2, and the lastrow would be 26.
Here is the snag: There is not contiguous data from O2 to O26, so a lastrow on Column O will place the User Input in the wrong location. Hence the need to base the lastrow on a different column. There will not be contiguous data for any other columns, except Column B.
If I am making this too hard on myself, please let me know.
Full Explanation:
I am trying to create a header based on User Input, but inline with where the actual header is.
Ex: If User Input is Electricity, then macro searches for Electricity, then jumps down to the predetermined last row, based on a different column and outputs the User Input as a Value.
Code:
Dim Util1 As Variant
Dim Util2 As Variant
Dim lastRow As Long
lastRow = Range("B" & Rows.Count).End(xlUp).Row
Dim AC As Integer
AC = ActiveCell.Column
Util1 = InputBox("What is the first Utility that needs Redistribution? {Must match Header on Bill Summary}")
Util2 = InputBox("What is the second Utility that needs Redistribution? {Must match Header on Bill Summary}")
Range("J27").Value = Util2
Cells.Find(What:=Util1, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=True).Activate
Range(AC & lastRow).Value = Util1
I was testing a few potential solutions, but no such luck. I believe the issue lies with the final line.
In the test data, the Search parameters find the User Input in Cell O2, and the lastrow would be 26.
Here is the snag: There is not contiguous data from O2 to O26, so a lastrow on Column O will place the User Input in the wrong location. Hence the need to base the lastrow on a different column. There will not be contiguous data for any other columns, except Column B.
If I am making this too hard on myself, please let me know.