# Posts by micha3l87

• ## Replace Index and Sumproduct Formula with VBA code

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

• ## Replace Index and Sumproduct Formula with VBA code

[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

• ## Replace Index and Sumproduct Formula with VBA code

[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!

• ## Replace Index and Sumproduct Formula with VBA code

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

• ## Add Lines to 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?

• ## Add Lines to Chart

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.

• ## Update all PivotTables

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.

• ## Copy one row at a time

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!

• ## Find & Replace Sub

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

Code

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.

Code

was returning incorrect data when using the Indirect version.

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

Thank you!

• ## auto populate 1 row, not 2 based on a lookup

Worked Perfectly!

Thank you so much!

• ## auto populate 1 row, not 2 based on a lookup

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!

• ## Combo Box Query Search

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

• ## Combo Box Query Search

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

Code
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!

• ## Search Range for Value (1004 Object Failed)

Thank you!

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

• ## Search Range for Value (1004 Object Failed)

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.

• ## Value.Formula Error

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

Code
.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

• ## Value.Formula Error

Thank you!

Code
.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

Code
Dim wsData as Worksheet
• ## Value.Formula Error

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.

• ## ActiveWindow to Entire Workbook

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.

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!