Select Cell in Column based on Search Parameters

Vanish29

New Member
Joined
Apr 28, 2016
Messages
22
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.

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.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
I'm not sure if I can supply a solution but could you post a screen shot of what your data looks like? Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html Alternately, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.
 
Upvote 0
https://www.dropbox.com/s/8w5qzklfu9g2ick/DATA.xls?dl=0

I am wanting to create a new Header based on the User Input. {The creation of the new header would just be Activecell.Value = Util1.}
For Util1, if the user enters Water, I want the macro to locate Water, which in this case will be in Cell N1, and then using a pre-determined last row, based on Column B, to Output Util1's value in the next row down. In this case, this would be N26.
All of these locations will change each time, hence the user input and search functions.
There is only one worksheet, so that is not a concern.

Full Project:
I would like to 1) have the macro determine the column and add the appropriate header. Then 2) I would like the macro to add a Column to the left of the User Input.
3) Have the User Input how the charge was determined (In this case, Water Cons), then have the User Input which Unit needs to be excluded and have the macro Sum the determined factor (in this case, Water Cons)
4) Have the User Input the remaining charge, which would be output to the cell below the new header (In this case, N27) and then calculate new charges based on determined factor.

If this is easier done in segmented steps, then repeated, such as Water first then Sewer, then that works too.
 
Upvote 0
It is best to do everything at once. I understand that you want to be prompted to enter a value such as "Water". Then you want to find that string in row 1, go 2 rows below the last row in that column based on the last value in column B and enter the response, in this case "Water". Is this correct? I'm having trouble determining exactly what you want to do from that point on. Could you upload a file that includes a second sheet with a manual mockup of what your expected results will look like after the macro is run? Explain in detail exactly what you want to do referring to specific cells, rows and columns.
 
Upvote 0
Final Product, Full Data Set: https://www.dropbox.com/s/z7jrvj21k7p9cz9/DATA Final Product.xls?dl=0
Final Product, Single Data Set: https://www.dropbox.com/s/swsvxyr34svcj0u/DATA Final Product Single Set.xls?dl=0

(Sorry for delay.)
There are more User defined cells.
There are too many cells to reference in this text box, but the general concept is for a single set of charges, i.e. Water, I want to have the User define the charge set "Water", then fill out the Total Expense, and the Total Consumption. Then the macro will create the rate and sum the total charges to find the remaining expense that needs to be redistributed.
Once determined, then the macro will ask the User to define how the charges are calculated, i.e. Water is consumption based, Trash is # of Units, and then redistribute charges to the remaining units.
 
Last edited:
Upvote 0
You said:
I would like the macro to add a Column to the left of the User Input.
I don't see that new column in the files you attached. I also don't understand how you determine the following:
3) Have the User Input how the charge was determined (In this case, Water Cons), then have the User Input which Unit needs to be excluded and have the macro Sum the determined factor (in this case, Water Cons)
4) Have the User Input the remaining charge, which would be output to the cell below the new header (In this case, N27) and then calculate new charges based on determined factor.
I would need you to explain step by step using an example from your data referring to specific cells, rows and columns. Explain how all the calculation are made. By "User Input" do you mean a prompt to enter information or will the user put the data manually into the appropriate cell? Also attach a manually created sheet that shows the end result.
 
Upvote 0
Step 1) Format Report
Code:
'Step 1 - Format the Bill Summary
    Cells.Find(What:="Non-Billable Units").Activate
    ActiveCell.Offset(-2, 0).Select
    Range(ActiveCell, Range("A65536").End(xlUp)).Select
    Selection.Delete Shift:=xlUp
    Cells.Select
    With Selection
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
    End With
    Rows("4:6").Select
    Selection.Delete Shift:=xlUp
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp    
With ActiveWindow
        .SplitColumn = 2
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
    Columns("E:E").Select
    Selection.NumberFormat = "m/d/yyyy"
    Cells.Select
    Cells.EntireColumn.AutoFit
2) Determine which charges needs to be redistributed -> User defined (Util1, Util2)
3) Determine which units need be removed from the Calculations -> User defined. If then loop, If there are units that need to be removed, find Unit based on user input (In this case, Unit 1 {Row 3}, Unit 2 {Row 4} & Unit 21 {Row 23}) and highlight row with RGB 255, 192 0. Close loop.
4) Find Util1 value in first row, then add two column to the right and make value of first cell in 2nd Column REDIST. (In this instance, Columns O & P and Cell P1)
5) Find Util1 value in first row, then paste Util1 value in same column, but 1 row down after last filled row. (For this specific file, this would be cell N26)
6) Under the new Util1 value (Cell N26), User defined value of Expense1 (Cell N27)
7) Next Row, User defined value of Calc1 (Cell N28). This will be an custom Form with 4 options, with a text component, which will determine the next step.
8) Next Row, If Option 1 -> value = Round(Expense1/Calc1,6); If Option 2 -> value = Round(Expense1/Calc1,2); If Option 3 -> value = (Expense1/Calc1); If Option 4 -> value = Round(Expense1/Calc1,2). This represents the amount of the rate or Rate1
9) Next Row, value = Sum of Column to predefined last row, so as not to include amounts that were just calculated (In this instance, =SUM(N3:N24) )
10) Next Row, value = Expense1 - row above current cell (In this instance, =N27-N30). This represents the amount that needs to be redistributed or Redist1
11) In first blank column, (Column O) value will be based on which option chosen for Step 8. If Option 1 -> value = Round(Rate1* Consumption,2) (In this case, =ROUND(F3*$N$29,2) ); If Option 2 -> value = Round(Rate1* 1,2); If Option 3 -> value = Round(Rate1*SQFT,2); If Option 4 -> value = Round(Rate1*Mult,2). This will be populated down to last filled row in column.
12) In Consumption Column (In this instance Column F), sum total column, without the removed units (Units 1 & 2, Rows 3 &4, respectively) in next empty row (F25 in this instance).
13) In 2nd Column or Redist Column (In this instance Column P), determine percentage that each unit receives of the redistribution. (In this instance, Cell P5 would have this formula =ROUND(SUM(ROUND(F5/$F$25,4)*$N$31),2) ). This will be populated down to the last filled row in column, skipping any units that are removed from the redistribution.
14) In Redist Column, Sum total column and place value R0C2 from Redist1 (Cell P31 in this instance)


Repeat for Each Utility.


I am considering creating a userform for this, so all the User Input can be done all at once, allowing the calculations to take place faster
 
Upvote 0
I think that I have a solution up to step #5 . After that, I have some more questions. However, before we proceed, I think you have to decide whether or not you want to input the data with a userform or not. I don't want to waste your time or mine working on a solution that you may or may not use. If you want to use a userform, then create the form and upload an updated file with a description of what you want the form to do. If you want to proceed without the form, please let me know and I will ask for further clarification starting with step #6 .
 
Upvote 0
Hey Mumps,
I did decide to go with a UserForm. I was used to VBA, so I allowed myself to get shut up into my medium without looking for an easier way.
Here is a link to the UserForm.
https://www.dropbox.com/s/nzjo389dve8axef/USER FORM.xlsm?dl=0


With the UserForm, the steps have been rearranged, and I have limited it to only processing a single utility set.
Step1) User will fill out Utility to be redistributed with ComboBox {Util1}.
Step2) User will fill out Total Expense in TextBox {Expense1}
Step3) User will mark which units need to Redistributed with ListBox Multiselect {ListBox2}
Step4) User will check which Statistics need to be used and then fill out Total Statistic (If Consumption is selected, then User will also need to select which Consumption with ComboBox {ComboBox1}


Redistribute_Click ()
Step5) Find Util1 Value in first row (N1), then paste Util1 Value in same column, but +1 Row after last date filled row (N26) [Last Data Filled Row needs to reference Column B {Lastrow}]
Step6) Next Row (N27), Value = Expense1
Step7) Next Row (N28), Value = Calc1
Step8) Next Row (N29), If Checkbox1. Value = True, then Value = Round(Expense1/Calc1,6); If Checkbox2.Value = True, then Value = Round(Expense1/Calc1,2); If Checkbox3.Value = True, then Value = (Expense1/Calc1); If Checkbox4. Value = True, then Value = Round(Expense1/Calc1,2) {Rate1}
Step9) Next Row (N30), Sum of the column, down to Lastrow [To exclude the amounts just calculated] (=Sum(N3:N24)) {Stat1}
Step10) Next Row, (N31), Value = Expense1 - Cell above (N30) {Redist1}
Step11) Find Values of ListBox2 and change color of rows to RGB 255, 192, 0 (Row 3 & Row 4)
Step12) Find ComboBox1 Value in first row (F1), then sum total column in lastrow (F25), removing the cells that are linked to the Values of ListBox2 (D3 & D4)
Step13) Find Util1 Value in first row (N1), then insert 2 Columns to the right (Columns O & P)
Step14) In 1st new column (Column O), in first row with this charge (O3), If Checkbox1.Value = True, then Value = Round(Rate1*Consumption,2); If Checkbox2.Value = True, then Value = Round(Rate1*1,2); If Checkbox3.Value = True, then Value = Round(Rate1*SQFT,2); If Checkbox4.Value = True, then Value = Round(Rate1*Multiplier,2). Populate this down to last filled row in Column to the left (Column N)[=Round(F3*$N$29,2)]
Step16) At top of 2nd Column (Column P), Value = "Redist"
Step17) In new column (Column P), in first row with this charge, excluding the units from ListBox2 (P3) Value = Round(SUM(ROUND(Unit's own stat*Stat1,4)*Rate1),2) Populate the down to last filled row in Column to the left (Column O) [=ROUND(SUM(ROUND(F5/$F$25,4)*$N$31),2)]
Step18) In current column column (Column P), Sum column, down to Lastrow. Place value in cell R0C2 from Redist1 (Redist1 = N31, Value would be placed in P31)
 
Upvote 0
I'm sorry but there is so much information that I don't know how to proceed. The listbox and combobox don't contain any values. You have many terms in your description that I don't understand what they are -- Calc1, Rate1, Stat1, Redist1, SQFT, Multiplier. This statement totally confuses me:
Find Util1 Value in first row (N1), then paste Util1 Value in same column, but +1 Row after last date filled row (N26) [Last Data Filled Row needs to reference Column B {Lastrow}]
N1 doesn't contain any data. N26 contains "62120". The last row in column B contains "Total". You have many merged cells which create havoc for Excel macros. My head is swimming right now. I think I'm in over my head!!!! Perhaps someone else on the Forum will be able to help you. :(
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top