# Posts by Max1616

Re: Indexing Top Values without Pivot

Hello again,

For the Group formula, if you were to add "West", you would need to add the bolded part to the old formula:

=IF(AND(ROW(Q2)/6>20,MOD(ROW(Q2),6)=2),"West",IF(AND(ROW(Q2)/6>10,MOD(ROW(Q2),6)=2),"South",IF(AND(ROW(Q2)/6<=10,MOD(ROW(Q2),6)=2),"North","")))

And if we had an "East":=IF(AND(ROW(Q2)/6>30,MOD(ROW(Q2),6)=2),"East",IF(AND(ROW(Q2)/6>20,MOD(ROW(Q2),6)=2),"West",IF(AND(ROW(Q2)/6>10,MOD(ROW(Q2),6)=2),"South",IF(AND(ROW(Q2)/6<=10,MOD(ROW(Q2),6)=2),"North",""))))

Now for the Name formula we need to add another condition for if the Group says "West":

=IF(Q2="North",INDEX(B:B,MATCH(LARGE(IF(A:A=Q2,C:C,0),(ROW(Q2)+4)/6),IF(A:A=Q2,C:C,0),0)),IF(Q2="South",INDEX(B:B,MATCH(LARGE(IF(A:A=Q2,C:C,0),((ROW(Q2)+4)-60)/6),IF(A:A=Q2,C:C,0),0)),IF(Q2="West",INDEX(B:B,MATCH(LARGE(IF(A:A=Q2,C:C,0),((ROW(Q2)+4)-120)/6),IF(A:A=Q2,C:C,0),0)),"")))

Main thing here is telling the formula what nth largest number to pull for west: LARGE(IF(A:A=Q2,C:C,0),((ROW(Q2)+4)-120)/6). Since West starts in row 122, 1st largest= (122+4-120)/6=1, 2nd largest = (128+4-120)/6=2, etc...

Now if we were to add east:
=IF(Q2="North",INDEX(B:B,MATCH(LARGE(IF(A:A=Q2,C:C,0),(ROW(Q2)+4)/6),IF(A:A=Q2,C:C,0),0)),IF(Q2="South",INDEX(B:B,MATCH(LARGE(IF(A:A=Q2,C:C,0),((ROW(Q2)+4)-60)/6),IF(A:A=Q2,C:C,0),0)),IF(Q2="West",INDEX(B:B,MATCH(LARGE(IF(A:A=Q2,C:C,0),((ROW(Q2)+4)-120)/6),IF(A:A=Q2,C:C,0),0)),IF(Q2="East",INDEX(B:B,MATCH(LARGE(IF(A:A=Q2,C:C,0),((ROW(Q2)+4)-180)/6),IF(A:A=Q2,C:C,0),0)),""))))

Notice "120" changed to "180" because East would start in row 182, and (182+4-180)/6 = 1.

To Recap all of that:
For east and west:
Group (regular formula): Place this formula in cell Q2:
=IF(AND(ROW(Q2)/6>30,MOD(ROW(Q2),6)=2),"East",IF(AND(ROW(Q2)/6>20,MOD(ROW(Q2),6)=2),"West",IF(AND(ROW(Q2)/6>10,MOD(ROW(Q2),6)=2),"South",IF(AND(ROW(Q2)/6<=10,MOD(ROW(Q2),6)=2),"North",""))))

Name (*array Formula): Place this array formula in cell R2:
=IF(Q2="North",INDEX(B:B,MATCH(LARGE(IF(A:A=Q2,C:C,0),(ROW(Q2)+4)/6),IF(A:A=Q2,C:C,0),0)),IF(Q2="South",INDEX(B:B,MATCH(LARGE(IF(A:A=Q2,C:C,0),((ROW(Q2)+4)-60)/6),IF(A:A=Q2,C:C,0),0)),IF(Q2="West",INDEX(B:B,MATCH(LARGE(IF(A:A=Q2,C:C,0),((ROW(Q2)+4)-120)/6),IF(A:A=Q2,C:C,0),0)),IF(Q2="East",INDEX(B:B,MATCH(LARGE(IF(A:A=Q2,C:C,0),((ROW(Q2)+4)-180)/6),IF(A:A=Q2,C:C,0),0)),""))))
*Make sure to double-click into the cell and press cntrl+shift+enter. If done correctly brackets will appear around the formula.

Sales (regular formula): Place this formula in cell S2:
=IF(SUMIFS(C:C,A:A,Q2,B:B,R2)=0,"",SUMIFS(C:C,A:A,Q2,B:B,R2))

I hope this helps you manipulate the formulas in the future and answers any questions you have! If you need anything else please do not hesitate to ask!

Sincerely,
Max

• ## Search through all sheets in a workbook looking for a date input into an input box

Re: Search through all sheets in a workbook looking for a date input into an input bo

Hi mycomputerguy,

I understand what you are asking now. The best way I can think to do this is to first show your userform as modeless with the macro that calls your userform. Instead of using this:

Code
``Userform1.show``

Use this:

Code
``UserForm1.Show vbModeless``

This will allow us to actually select a cell while in the userform. (You can't select specific cells when you call a userform using only "show").

From here it's relatively simple what needs to happen. We need to select the sheet and cell in question. I'm not sure what the control's names are in your userform, so for simplicity I will use Textbox1 and Textbox2 for appt date and appt time respectively, and for your Update button I will use CommandButton1.

General Idea here: I am taking the date in textbox1 and recreating how the sheet's naming convention works. ("." not "/"). Once I have this I select that sheet, then find the row number of the time (Using the match function). Once I have the time's row number I can simply select that cell.

I hope this helps! You will have to update your control names but it should be pretty easy for you!

Sincerely,
Max

Re: Indexing Top Values without Pivot

Hello onexc,

You would need to change the MOD function here. The MOD function gives us the remainder when we divide two numbers. Before it was saying if the remainder of Row(f6)/6=0 then show "North" or "South", if not then show "". So in our case 6/6 has no remainder so it would show "North"/"South", but 7/6 has a remainder of 1 so the formula would show "".

If we move our formula to Q2 we need to update the remainder to 2. Because 2/6 has a remainder of 2, and so does 8/6, and 14/6, etc. Long story short, here is the formula you would need in Q2:
=IF(AND(ROW(Q2)/6>10,MOD(ROW(Q2),6)=2),"South",IF(AND(ROW(Q2)/6<=10,MOD(ROW(Q2),6)=2),"North",""))

On a similar note, we will need to update the Name formula as well to this:
=IF(Q2="North",INDEX(B:B,MATCH(LARGE(IF(A:A=Q2,C:C,0),(ROW(Q2)+4)/6),IF(A:A=Q2,C:C,0),0)),IF(Q2="South",INDEX(B:B,MATCH(LARGE(IF(A:A=Q2,C:C,0),((ROW(Q2)+4)-60)/6),IF(A:A=Q2,C:C,0),0)),""))

This is actually pulling the nth largest number from our data set.
(ROW(Q2)+4)/6)
Before we had Row(F6)/6 = 1, Row(F12)/6=2 etc. Since our rows are spaced 6 cells apart we need to keep the 6 and change our row numbers to still be divisible by 6. Since our new row starts in row 2, we can just add 4 to the row: (ROW(Q2)+4)/6)

In Summary, here are your new formulas:
Group (regular formula): Place this formula in cell Q2:
=IF(AND(ROW(Q2)/6>10,MOD(ROW(Q2),6)=2),"South",IF(AND(ROW(Q2)/6<=10,MOD(ROW(Q2),6)=2),"North",""))

Name (*array Formula): Place this array formula in cell R2:
=IF(Q2="North",INDEX(B:B,MATCH(LARGE(IF(A:A=Q2,C:C,0),(ROW(Q2)+4)/6),IF(A:A=Q2,C:C,0),0)),IF(Q2="South",INDEX(B:B,MATCH(LARGE(IF(A:A=Q2,C:C,0),((ROW(Q2)+4)-60)/6),IF(A:A=Q2,C:C,0),0)),""))
*Make sure to double-click into the cell and press cntrl+shift+enter. If done correctly brackets will appear around the formula.

Sales (regular formula): Place this formula in cell S2:
=IF(SUMIFS(C:C,A:A,Q2,B:B,G6)=0,"",SUMIFS(C:C,A:A,Q2,B:B,G6))

Sincerely,
Max

Re: Indexing Top Values without Pivot

Man oh man, this one was tricky without using VBA. But it was fun to try and figure it out. Here is what I got for you:

Assumption: F6 is where the first formula goes. This is important. If the formula starts in a different cell then the formula itself needs to change.
*Note: This uses array formulas indicated by {}. In order to use an array formula, enter the formula in the cells without the "{}", double-click into the cell, then press Cntrl+Shift+Enter.

Group (regular formula): Place this formula in cell F6:
=IF(AND(ROW(F6)/6>10,MOD(ROW(F6),6)=0),"South",IF(AND(ROW(F6)/6<=10,MOD(ROW(F6),6)=0),"North",""))

Name (*array Formula): Place this array formula in cell G6:
=IF(F6="North",INDEX(B:B,MATCH(LARGE(IF(A:A=F6,C:C,0),ROW(F6)/6),IF(A:A=F6,C:C,0),0)),IF(F6="South",INDEX(B:B,MATCH(LARGE(IF(A:A=F6,C:C,0),(ROW(F6)-60)/6),IF(A:A=F6,C:C,0),0)),""))
*Make sure to double-click into the cell and press cntrl+shift+enter. If done correctly brackets will appear around the formula.

Sales (regular formula): Place this formula in cell H6:
=IF(SUMIFS(C:C,A:A,F6,B:B,G6)=0,"",SUMIFS(C:C,A:A,F6,B:B,G6))

I hope this helps. It really challenged me to get this to work. There may be a better way with only using formulas but I cannot think of one.

Sincerely,
Max

edit1: forgot to mention that the Array formula might take a second or two to load once you drag and drop the formula.

• ## Automatically move row to different spreadsheet with data validation

Re: Automatically move row to different spreadsheet with data validation

No worries, let me explain how my code works.

First I am assigning your active sheet to a variable:

Code
``````Dim ws1 As Worksheet
Set ws1 = ActiveSheet``````

So anytime I write "ws1" I am really writing your active sheets name. (ws1.Activate is the same as Sheets("Finished Treating"). Activate).

Second, I am assigning the text in your drop down to a variable as well. So if your drop down (which lives in range A1) says "Treating" Then the variable shtname will also say "Treating":

Code
``````Dim shtname As String
shtname = ws1.Range("A1")``````

Thus, if you change your dropdown to the sheet you would like to move the data to, then the variable in the code will change as well.

Lastly I am assigning a new sheet variable that is named whatever is in your drop down in range A1:

Code
``````Dim ws2 As Worksheet
Set ws2 = Sheets(shtname)``````

You will notice the variable "shtname" is listed here. Again, shtname = Range(A1) so that means that Sheets(shtname) is the same as saying Sheets("Treating") if "Treating" is in A1. If "Demand " is in A1 then shtname = "Demand" and Sheets(shtname) = Sheets("Demand"). Essentially what this boils down to is whatever is in your drop down (in A1) is what will be referenced for your sheet name:

If A1 = "Treating" then
shtname="Treating"
ws2 = Sheets(shtname) = Sheets("Treating")

Since the sheet name you want to transfer data to is now dynamic based off of the value of your drop down in A1, then we no longer need two if statements to move our data around. We can now reference "ws2", which is the same as saying Sheets(shtname) which is the same as saying Sheets(A1). You see me do this here:

Code
``nxtrow = ws2.Range("G" & Rows.Count).End(xlUp).Row + 1 'if A1 is "Treating", This is the same as saying nxtrow = Sheets("Treating").Range("G" & Rows.Count).End(xlUp).Row + 1``

and here:

Code
``Target.EntireRow.Copy Destination:=ws2.Range("A" & nxtrow) ' 'if A1 is "Treating", This is the same as saying Target.Entireow.Copy Destination:=Sheets("Treating").Range("A"&nxtrow)``

Does this all make sense? I know learning variables can be a bit confusing at first. The main thing you need to worry about is changing this line of code:

Code
``shtname = ws1.Range("A1") 'This would be the location of your dropdown``

and update "A1" with the location of your drop down.

For example, if your drop down is in the same row as your target and in column J then you can use this:

Code
``shtname = ws1.cells(target.row,10) 'This would be the location of your dropdown``

Let me know if there is any further explaining I can do. I hope this helps!

Sincerely,
Max

• ## Automatically move row to different spreadsheet with data validation

Re: Automatically move row to different spreadsheet with data validation

Thanks enewton, that makes more sense. Let's say your sheetname drop down lives in cell ("A1"). If you have "Treating" selected in A1, This will move the row from your current spreadsheet to the spreadsheet "Treating":

You will need to update the range of your drop down to whatever it actually is in your spreadsheet. If your dropdown is a control let me know what kind of control (Form Control or ActiveX Control) and we can discuss a solution to that.

Sincerely,
Max

• ## Automatically move row to different spreadsheet with data validation

Re: Automatically move row to different spreadsheet with data validation

Hello,

I am confused about what you are trying to do. When you say "But I can't figure out how to do multiple ones on same spreadsheet" Are you saying you are having problems moving multiple rows to one sheet? Or are you say you are having problems moving one row to multiple sheets?

To move multiple lines you can use something like this:

Code
``````Private Sub Worksheet_Change(ByVal Target As Range)
Range(Rows(Target.Row), Rows(Target.Row - 1)).Copy Destination:=Sheets("Finished Treating").Range("A" & nxtrow)
End Sub``````

If you want to move your row from Treating to Demands you can use your same code with the sheet name changed:

Code
``````Private Sub Worksheet_Change(ByVal Target As Range)
nxtrow = Sheets("Demands").Range("G" & Rows.Count).End(xlUp).Row + 1
Target.EntireRow.Copy Destination:=Sheets("Demands").Range("A" & nxtrow)
End Sub``````

If you can provide a little more context I can help you further.

Sincerely,
Max

• ## Search through all sheets in a workbook looking for a date input into an input box

Re: Search through all sheets in a workbook looking for a date input into an input bo

Hello!

Sorry for the delay in getting back to you. I normally don't check this unless I'm at work (It's what I do to kill time if I'm bored).

I'm going to try and tackle these one at a time, and you can decide which you would like to use. Here is the slightly modified version of my code from before. This copies formats of the currently selected sheet and creates the new sheet at the end of the workbook:

Now here is the code to fill in every sheet for the remainder of the month:

Keep in mind, if a sheet for one of the dates after your indicated date is already in existence, the code will error out. (Can't make name a sheet that already exists).
I'm not sure if I understand what you are asking for in your last post about the list box. Maybe submit a new post? Or provide examples of how you would like the info to look/function.

Sincerely,
Max

• ## Search through all sheets in a workbook looking for a date input into an input box

Re: Search through all sheets in a workbook looking for a date input into an input bo

Hello!

Here is the sub for your first question (I remade the code from scratch):

For your second question, I can add onto this macro to make a sheet for all remaining days in the month of the date specified. Is that what you want?

Sincerely,
Max

Re: Vlookup Function in VBA

Two things I'm noticing here. 1 is your lookup value is not lining up with the value in your list. (45625 string <> 45625). the second is the object text_brand does not exist. This code is working fine for me in your example:

Code
``````Private Sub cmd_OK_Click()
Dim reg As Long
Dim rng As Range

reg = Me.cmb_reg.Value
Set rng = Sheets("tbl_fleet").Range("a1:j213")
MsgBox Application.WorksheetFunction.VLookup(reg, rng, 3, False)
End Sub``````

I changed the dimensions of the variable "reg" from string to long (number).

You can change the msgbox to actually assign the value to an object that exists.

Hope this helps.

Re: Running a Calculation with a user forum

Hello TJ,

I'm not sure what you want to do with the calculation, so in the macro I wrote I am placing the results in the text box (over the number the user inputted):

Code
``````Private Sub CommandButton1_Click()
Me.TextBox1 = TextBox1 / 8
End Sub``````

Hopefully this gives you an idea on what to do. Let me know if you have any questions or need any more help!

Sincerely,
Max

• ## Cut and Copy Row from one sheet to another when field is marked complete

Re: Cut and Copy Row from one sheet to another when field is marked complete

No problem at all, try this out:

• ## Cut and Copy Row from one sheet to another when field is marked complete

Re: Cut and Copy Row from one sheet to another when field is marked complete

I see the issue. it was moving the entries to the bottom of your list since its reading the last value as the end of your table. (The table functionality is causing this code to break: lrow2 = ws2.Cells(Rows.Count, 2).End(xlUp).Row + 1). Try this code instead:

Also, remove the data at the bottom of your completed items table.

Sincerely,
Max

• ## Cut and Copy Row from one sheet to another when field is marked complete

Re: Cut and Copy Row from one sheet to another when field is marked complete

Run this and let me know what the message boxs say:

• ## Cut and Copy Row from one sheet to another when field is marked complete

Re: Cut and Copy Row from one sheet to another when field is marked complete

Did you place that code in module 1? If so that's incorrect.

Double click on Sheet2(Tony Gr...) and paste the code in the window that comes up from that. This code needs to be placed at the sheet level module. The title of the window where you need to place the code will say
"Health & Safety Action List.1.xlsm - Sheet2 (Code)"

• ## Cut and Copy Row from one sheet to another when field is marked complete

Re: Cut and Copy Row from one sheet to another when field is marked complete

Hello!

Place this code in the sheet module for each person:

That should do the trick! Let me know if it doesn't work or if you have any questions.
Sincerely,
Max

• ## VBA Filldown Column A & B content untill the last row in Column C

Re: VBA Filldown Column A &amp; B content untill the last row in Column C

Try this:

Code
``````Sub AmexRepeat()
Dim lrow As Long
lrow = Cells(Rows.Count, 3).End(xlUp).Row
Range("A2:B" & Cells(Rows.Count, 2).End(xlUp).Row).AutoFill Destination:=Range("A2:B" & lrow), Type:=xlFillCopy
End Sub``````

I'm using autofill just like you attempted. But the big difference to note here is the "Type" parameter. The "Type" parameter defaults to trying to figure out your pattern and applying it to the range (xlFillDefault). By using the type parameter we can specify to excel how we want the data to autofill. In your case we want to use "Type:=xlFillCopy" Which copies the series over and over again without adding any extra calculations.

I hope this makes sense!

Sincerely,
Max