Posts by Max1616

  • Indexing Top Values without Pivot

    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.


    Place this code after the code that updates your text boxes:


    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

  • Indexing Top Values without Pivot

    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

  • Indexing Top Values without Pivot

    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

  • Vlookup Function in VBA

    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.

  • Running a Calculation with a user forum

    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