Posts by micha3l87

The file size exceeds the limit. I'll see what I can do to make a mock example in a different sheet

[USER="31712"]Carim[/USER]

To complicate things a bit more, If I took the average of all the last entries and -2500

=AVERAGE(D5:D30,G5:G30,L5:L31,O5:O31)-2500
Results: 10182 in Cell L4

Can I use offset to replace the low end number in the formula

=SUMPRODUCT(MAX((ROW(Data!$C$11000:OFFSET(Data!$C$10000,List!$L$2,0)))*(Data!$C$11000:OFFSET(Data!$C$11000,List!$L$2,0)=$J5)*(Data!$D$11000:OFFSET(Data!$C$2,List!$L$2,0)=AA$5)))

So use offset on both sides of the formula basically, one the left side its taking a average and subtracting 2500 rows from that for the low end search range, and on the high end search range its just finding the last row on the data tab

[USER="31712"]Carim[/USER]

I'm looking at your formula in cell A5.

I have a couple of questions. It's finding the last row on the data tab - 1. So if the data tab has 119 rows and A5 says 118. But the formula in cell D7 still works even if its looking for the value the data tab on row 119. Confused on how that is working. I'm glad it is! just trying to understand!

With this solution the only value I'll have to change is in A5?
Change the C2-C10000 to match my ranges in the future?

and no I don't think they'll be a need for any macro this is way easier to update one cell then all them!

Hey everyone!

I originally received help a long time ago here when I had a project!
Working on that same project, buy my experience in excel has grown some and I've learned a little VBA and want to improve the formula I've been relying on to keep my status board updated in excel.

The formula looks at the A/C Tail number. For Example in Cell D2, the formula is looking at A/C 92 in Cell B2, and Engine Position 1 (AL5), then looks on the Data sheet, to see what line that matches to and returns the row number. With the row number I can use index to get the letter code related to that engine and aircraft.

The problem with this formula is i constantly have to update the ranges for how many rows it scans over for data. As we receive more samples and the data page starts to become larger the computer cannot handle a unlimited range of scanning the rows each time.

My goal is to start VBA code that updates when the workbook is saved. I barley understand how this formula works so I was hoping to get some help with the VBA code, at the very least pointed in the direction I need to work towards

One more question about this chart.

I'm trying to change the axis's and it completely ruins the whole chart. I'm thinking it has more to do with how I have the data laid out for the chart to use?

Thank you, Got them added!

Hey Everyone!

I have a concentration reading chart for some soapy water that has to be measured and fall within a certain range.

I have the chart pretty much made, but I cant seem to get the baseline to show me 17%.
The manufacturer recommended concentration is between 17-20% and the previous chart I made in 2015 I somehow figured out how to add yellow lines to the graph to indicate this. I included a scanned copy on tab 2 for reference.

Thanks for any help! and any recommendations to make this better I'll gladly accept this isn't my area of expertise clearly since the last time this chart was made was almost 5 years ago.

I typed up some code I was hoping would solve my pain but it doesn't work.

I have pivot tables that use data from a table but sometimes people leave blanks in the date field and when the book gets refreshed every pivot table in the book throws up errors about overlapping because of the blank space in the date. The grouping gets undone and I suddenly have data as far back as 2016 appearing in the tables. Each time i have to manually go in and change the grouping to each table and reset everything, thats after I look up online how to do the grouping to begin with because I'll forget and for a month we will be good and then suddenly someone overlooks a date input.

Sub SetMonth()
Dim PT As PivotTable
Dim WS As Worksheet
Dim PF As PivotField
Set PF = PT.PivotFields("Date")


    For Each WS In ThisWorkbook.Worksheets
    
        For Each PT In WS.PivotTables
            PF.LabelRange.Group Start:=7306, End:=73051, Periods:=Array(False, False, False,False, True, False, True)
            PT.RefreshTable
        Next PT
        
    Next WS
End Sub

Hey everyone,

How do I copy and paste one row at a time from range A:U on '730291' to the 'test' tab starting at the top or row 2. and pasting it into D:Y next empty row in Y on the 'test' tab.

Typically when I've ever needed to copy and paste a range its been all at once type thing, but this idea I had I need to be able press the button and have the macro automatically move the next row from the 730291 tab over

any help would be appreciated!

Hey everyone,

I wrote this sub this morning to set a new Min / Max Value based off the Users Range they Input in a couple of cells to update the formula

A lot of trial and error got me a functioning sub, I also wanted to update the formula on a different sheet "Daily Input" the code seems to process but nothing changes on the Daily Input sheet, just the status board.

Now that I'm typing this I'm wondering if its because the code was written into the sheet and not a module?

Other then that issue, I'd like some feedback on suggestions or improvements to the code, I'd say I'm in the beginning stages of learning how to use VBA so all feedback is welcomed!

I thought about creating a userform to display the information or get the new range inputs using a msgbox

The Code is looking at the Min-Max Values in G and F
and replacing those values in the formula below

=SUMPRODUCT(MAX((ROW(Data!$C$2:$C$3000))*(Data!$C$2:$C$3000=$B5)*(Data!$D$2:$D$3000=$AD$5)))

I tried this code, and it was returning the correct Row without the need to adjust the ranges, but the Index was not returning the correct value when using this Code. Even though both the sumporducts returned the same Row Number, and the same index formula would return different results.

=SUMPRODUCT(MAX((ROW(INDIRECT("Data!C1:C"&ROW(INDEX(Data!C:C,COUNTA(Data!C:C))))))*(INDIRECT("Data!C1:C"&ROW(INDEX(Data!C:C,COUNTA(Data!C:C))))=$B5)*(INDIRECT("Data!D1:D"&ROW(INDEX(Data!C:C,COUNTA(Data!C:C))))=AD$5)))

was returning incorrect data when using the Indirect version.

=INDEX(Data!$I:$I,'Status Board'!D5)

Thank you!

Sub SetMax()


Dim wsStatusBoard As Worksheet: Set wsStatusBoard = Sheets("Status Board")
Dim wsDailyInput As Worksheet: Set wsDailyInput = Sheets("Daily Input")
Application.ScreenUpdating = False


        wsStatusBoard.Range("D5:D28,H5:H28,N5:N31,P5:P31,V10:V22").Replace _
        What:=Range("G44").Value, Replacement:=Range("G47").Value, _
        Lookat:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True
        wsStatusBoard.Range("G44").Value = Range("G47").Value
        wsStatusBoard.Range("G47").Value = "Enter New Max Value Here"
        


''This is the part that does not seem to work
        wsDailyInput.Range("AC6:AC150").Replace _
        What:=wsStatusBoard.Range("G44").Value, Replacement:=wsStatusBoard.Range("G47").Value, _
        Lookat:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True


Sub SetMin()
Dim wsStatusBoard As Worksheet: Set wsStatusBoard = Sheets("Status Board")
Dim wsDailyInput As Worksheet: Set wsSDailyInput = Sheets("Daily Input")
Application.ScreenUpdating = False




If wsStatusBoard.Range("F47").Value < "32" Then
MsgBox "Min Row Value Cannot Be < 32"
Else
        wsStatusBoard.Range("D5:D28,H5:H28,N5:N31,P5:P31,V10:V22").Replace _
        What:=Range("F44").Value, Replacement:=Range("F47").Value, _
        Lookat:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True
        wsStatusBoard.Range("F44").Value = Range("F47").Value
        wsStatusBoard.Range("F47").Value = "Enter New Min Value Here"
End If
      
End Sub

Worked Perfectly!

Thank you so much!

How would you guys suggest handling this.
When this code was written all the aircraft we were using had 2 engines and now we have aircraft that are single engines as well but the code auto inputs 2 rows 1 for each engine position even though that particular aircraft only has 1 engine.

Unfortunately i inherited this excel document and my VBA skills are far less then the person before me.
the code has made life pretty simple and if I just have to keep deleting the 2nd row then I guess I can live with that but I imagine theres a easy solution, at least I hope.

I was thinking of adding a vlookup table on the LIST tab with Aircraft models and a number for how many engines those model aircraft have so when the code ran it would look to see how many rows to create?
I think im on the right track but I'm not sure how to do it.

Any help would be appreciated!

Thank you!

Private Sub populateEnginePosition(acAmuIn As String)


    Dim wsDailyInput As Worksheet: Set wsDailyInput = Sheets("Daily Input")
    Dim wsList As Worksheet: Set wsList = Sheets("List")
    Dim wsData As Worksheet: Set wsData = Sheets("data")
    
    Dim acAmuInput() As String
    Dim acType As String
    Dim amuName As String
    Dim listItems As String
    
    Dim acNumber As Integer
    Dim enginePosition As Integer
    
    Dim lastRow As Long
    Dim currTime As Date
    currTime = Now - TimeSerial(0, 13, 0)
 
    
    acNumber = acAmuIn
    enginePosition = 1
    
    lastRow = wsDailyInput.Range("a1048576").End(xlUp).Row
    If lastRow < 6 Then
        lastRow = 6
    Else
        lastRow = lastRow + 1
    End If
    
    '' Get aircraft type
    On Error Resume Next
    amuName = WorksheetFunction.VLookup(acNumber, wsList.Range("I2:K52"), 2, False)
    acType = WorksheetFunction.VLookup(acNumber, wsList.Range("I2:K52"), 3, False)
    If Err.Number <> 0 Then
        MsgBox "Aircraft not listed in lookup-table"
    End If


    For enginePosition = 1 To 2
        '' Populate Daily input worksheet
        With wsDailyInput
            .Range("a" & lastRow).Value = amuName
            .Range("b" & lastRow).Value = acType
            .Range("c" & lastRow).Value = acNumber
            .Range("d" & lastRow).Value = enginePosition
            .Range("e" & lastRow).Value = Cells(2, 5).Value
            ''Computer Clock is 13 mins fast
            .Range("f" & lastRow).Value = Format(Now() - TimeValue("00:13:00"), "hh:mm")


            
            
            With .Range("i" & lastRow).Validation
                .Delete
                .Add Type:=xlValidateList, Formula1:="=LabCodes"
                .IgnoreBlank = True
                .InCellDropdown = True


            End With
            .Range("y" & lastRow).Formula = "=IF(E" & lastRow & "=0,0,F" & lastRow & "-E" & lastRow & ")"
            .Range("z" & lastRow).Formula = "=IF(F" & lastRow & "=0,0,G" & lastRow & "-F" & lastRow & ")"
            .Range("aa" & lastRow).Formula = "=IF(E" & lastRow & "=0,0,Y" & lastRow & "+Z" & lastRow & ")"


        End With
'        Call fillBorders(lastRow)
        Call formatCells(lastRow)
        
        lastRow = lastRow + 1
    Next enginePosition
End Sub

Fixed my problem.

On the table I was searching for specific employee names, I changed the way the data was inputted and now its in the same format as I'd like to search with the drop down box

Hey everyone!

I'm just getting into using access and I've come to my first road block with the combo box query search.

I have a table with [FirstName][LastName][EmpID] then I set a query with

Full_Info: [Rank] & " " & [EMP_L_Name] & ", " & Left([EMP_F_Name],1) & " " & [Emp_ID]

When I made my custom query using the combo box search just for last names, it worked. The problem is having people with the same name so I set the query to use the query Emp_Extended for the drop down
I'm sure the problem is something related to the bound column and its just for asthetics that I set it up this way.
I could have just set the query to [LastName][FirstName] [EmpID] and had 3 columns in the drop down list.

Hopefully my question makes sense?
I wasn't entirely sure what to search for to find the answer to the problem im having, if you could point me in a direction that would be great!

Thank you!

Thank you!

Thats a great idea, I've never done that!
That actually might be useful in a lot of areas of the workbook

The code I highlighted in Red was changes I tried to make that broke the code.
Before I changed it was .Range("B5"B31")

in Cell A1 on both sheets I've input B5:B31, wasn't sure if the issue was because this code runs on one sheet but looks at a range on a different sheet

The goal was to attempt to prevent someone from having to go into the vba code and change the range if that range on that sheet changes. Recently someone moved the range over from A5:A31 because it looked "Nicer", while it did look better adjusted to the right it broke the code that no one knew how to fix.

Private Function validateAircraft(acAmuIn As String) As Boolean


    Dim wsStatusBoard As Worksheet: Set wsStatusBoard = Sheets("Status Board")
    Dim wsList As Worksheet: Set wsList = Sheets("List")
    
    Dim cRng As Range
    Dim rRng As Range
    Dim lastRow As Long
    Dim acNumber As Integer
    Dim acType As Variant
    Dim acAmuInput() As String
    Dim amuName As String
    Dim myTableArray As Range


    [COLOR=#FF0000]Dim MyValue As Variant[/COLOR]
    [COLOR=#FF0000]MyValue = Range("A1").Value[/COLOR]
    acNumber = acAmuIn
  
    
    '' check to see if the a/c is in the 525
   [COLOR=#FF0000] For Each rRng In wsStatusBoard.Range("MyValue")[/COLOR]
        If rRng.Value = acNumber Then
            '' found in 525, now check to see if there is an engine number entered
            For Each cRng In wsStatusBoard.Range(Cells(rRng.Row, 2).Address, Cells(rRng.Row, 5).Address)
                If Not IsEmpty(cRng.Value) Then
                    '' ac belongs to 525
                    validateAircraft = True
                    Exit Function
                Else
                    MsgBox "Engine number not listed for select aircraft in Status Board"
                    validateAircraft = False
                    Exit Function
                End If
            Next cRng
        End If
    Next rRng

I found a temp a workaround I put the original index formula on the daily input sheet and I told the vba code to get that Cells value.
I will attach the workbook this weekend, but honestly I think I can live with this, I set the background white and the text white so you don't even know its there.

I'm really struggling with these VBA excel formulas

.Range("L" & LaswRow.Formula = "=If(Y & LastRow & ">=Time(1,15,0),1,"""")"

=IF (Y6>Time(1,15,0),1,"")

Attempted to use the Macro recorder to get an idea but not much help

Thank you!

.Range("k" & LastRow).Formula = "=IF(INDEX(Data!$I:$I,$AC" & LastRow & ")=""A"","""",INDEX(Data!$I:$I,AC" & LastRow & ))"

It accepted the VBA formula with the end "))" correcting to ))"
Code executes, but its not working more troubleshooting,

Any suggestions on where to look for the troubleshooting?

Do I need to set data as a worksheet in vba?
So

Dim wsData as Worksheet

I'm good at making small changes to VBA code but this is next level for me.

I keep getting errors when I try to run the macro with the new addtion I added to the K Col.
'.Range("k" & lastrow.Formula = "=IF(INDEX(Data!$I:$I,$AC15)="A","",INDEX(Data!$I:$I,AC15))"

The base formula
=IF(INDEX(Data!$I:$I,$AC15)="A","",INDEX(Data!$I:$I,AC15))
I know I need the replace 15 with & LastRow but I believe the IF formula with the "" are causing the errors in the code.

Can anyone please help

Private Sub populateEnginePosition(acAmuIn As String)


    Dim wsDailyInput As Worksheet
    Dim wsList As Worksheet
    
    Dim acAmuInput() As String
    Dim acType As String
    Dim amuName As String
    Dim listItems As String
    
    Dim acNumber As Integer
    Dim enginePosition As Integer
    
    Dim lastRow As Long
    Dim currTime As Date
    currTime = Now - TimeSerial(0, 13, 0)
 
    Set wsDailyInput = Sheets("Daily Input")
    Set wsList = Sheets("List")
        
    acNumber = acAmuIn
    enginePosition = 1
    
    lastRow = wsDailyInput.Range("a1048576").End(xlUp).Row
    If lastRow < 6 Then
        lastRow = 6
    Else
        lastRow = lastRow + 1
    End If
    
    '' Get aircraft type
    On Error Resume Next
    amuName = WorksheetFunction.VLookup(acNumber, wsList.Range("h2:j62"), 2, False)
    acType = WorksheetFunction.VLookup(acNumber, wsList.Range("h2:j62"), 3, False)
    If Err.Number <> 0 Then
        MsgBox "Aircraft not listed in lookup-table"
    End If


    For enginePosition = 1 To 2
        '' Populate Daily input worksheet
        With wsDailyInput
            .Range("a" & lastRow).Value = amuName
            .Range("b" & lastRow).Value = acType
            .Range("c" & lastRow).Value = acNumber
            .Range("d" & lastRow).Value = enginePosition
            .Range("e" & lastRow).Value = Cells(2, 5).Value
            .Range("f" & lastRow).Value = Format(Now() - TimeValue("00:13:00"), "hh:mm")
            'Halp
            '.Range("k" & lastrow.Formula = "=IF(INDEX(Data!$I:$I,$AC15)="A","",INDEX(Data!$I:$I,AC15))"
            
            With .Range("i" & lastRow).Validation
                .Delete
                .Add Type:=xlValidateList, Formula1:="=LabCodes"
                .IgnoreBlank = True
                .InCellDropdown = True
            End With
            
            .Range("y" & lastRow).Formula = "=IF(E" & lastRow & "=0,0,F" & lastRow & "-E" & lastRow & ")"
            .Range("z" & lastRow).Formula = "=IF(F" & lastRow & "=0,0,G" & lastRow & "-F" & lastRow & ")"
            .Range("aa" & lastRow).Formula = "=IF(E" & lastRow & "=0,0,Y" & lastRow & "+Z" & lastRow & ")"


        End With
'        Call fillBorders(lastRow)
        Call formatCells(lastRow)
        
        lastRow = lastRow + 1
    Next enginePosition
End Sub

hey everyone,

I've got 2 subs I use to basically hide everything and go into full screen mode and another button to undo all the hiding.

Sub ShowAll()
Sheet 1.Activate
Application.DisplayFullScreen = False: DisplayFormula Bar = True: onKey "{ESC}"
ActiveWindow.DisplayWorkbookTabs= True: Displayheadings = True: DisplayGridlines = True
ActiveSheet.Scrollarea = ""
call UnprotectAll
End Sub


Sub Unprotectall()
dim wsheet as worksheet
for each wsheet in worksheets
wsheet.unprotect
next wsheet
end sub

everything has worked great, problem is we've added a navigational button that takes you to other sheets and displays the information on those sheets and sets custom scroll areas / zoomz / locked cells and protects the sheet.

so the macro I have only reverses some of the things that get hidden because the Active Window command, hopefully that makes sense, I wanted to copy and paste the code but the excel program is on a different computer.

Any help would be much apperciated!