Run Goalseek Macro On Filtered Data

Sep 26, 2007

I have written a macro that uses the goalseek function over an entire column. It works and is a happy macro. the only problem with it that I see is that it currently cannot work through a filtered list. Say, for instance, you have a column(a) that adds column(b) + column(c). Lets say that there's 10,000 rows and of those, 8,000 have a value over 10. You apply a filter and view the other 2,000 rows with values less than 10. If you run the macro in its current state you'd apply the results to all 10,000 lines - but I wish to do it only over the remaining 2,000.

I'm certain that some variation of .SpecialCells(xlCellTypeVisible) will be used but I cannot determine where. Here's my code.

Sub TryMe()
'set the goalseek area
answer = MsgBox("Please note that this macro only works on consecutive cells." & _
vbNewLine & "It will NOT work on a filtered list." & vbNewLine & vbNewLine & _
"Continue?", vbYesNo)
If answer = vbNo Then
Exit Sub
End If

restart:
With Application
On Error Resume Next
Application.DisplayAlerts = False ..............

View 5 Replies


ADVERTISEMENT

Macro To Goalseek After Certain Conditions Are Met

Jun 21, 2008

I'm trying to create a macro to Goalseek after certain conditions are met and then loop until other conditions are met. In my worksheet, the reference cells for the macro are as follows:

a. Range (U94:DL94) as Cash Available
b. Range (U95:DL95) as Distribution
c. Range (U96:DL96) as Monthly Cashflow
d. Range (U98:DL98) as the IRR calculation. IRR Calculation (If AT98 is active cell) = (1+IRR($U$96:AT96,0.01))^12-1. If AU98 is active cell, then IRR Calculation = (1+IRR($U$96:AU96,0.01))^12-1), etc.

I need the macro to do the following:

1) If value in range Cash Available > 0 and value in range IRR < 0.08, then goalseek
(set cell = (range IRR), Goalseek = 0.08, Changing cell = (range Distribution)

2) If Goalseek result in range Distribution < value in range Cash Available, set cell equal to value in range Cash Available.

3) I need GoalSeek to loop until value in range Distribution > 0 and whatever cell in range IRR = 0.08.

4) After first cell in range IRR = 0.08, I need the macro to start again on the same named ranges but for different rows lower down on the spreadsheet and do the same loop, but this time for range IRR(2) = 0.15.

View 2 Replies View Related

Macro To Copy And Paste Auto Filtered Data To Existing Worksheet Below Previous Data

Oct 18, 2013

I have been working on a macro that compares a existing list of data to an updated list of data and then either moves any data not on the new list over to a completed tab (followed by deleting the record on the existing sheet), and then adds any items not on the existing sheet, but which appear on the new list, to the existing list.

I have come across a stumbling block, i have managed to identify on the existing list the rows of data that have been removed from the new list and therefore need to be moved over to the completed tab, but when i select the data it selects the header row aswell (which will always remain the same row). Obviously this then pastes the header row aswell, and also i can't seem to get it to paste in the new sheet to the next available row (i.e this will be used daily and i don't won't to overwrite the infor already in the completed tab). the next issue i have is then when i go back to existing sheet to delete the data i just copied across, as the header was initially select this also gets deleted.

The code below, is the complete code, including filtering, copying some forumals etc. The area i am getting stuck on is highlighted in red:

Sub Update()
Dim bottomrow As Long
Dim My_Range As Range
bottomrow = Cells(Rows.Count, "C").End(xlUp).Row
Set My_Range = Range("A1:Y" & bottomrow)

[Code] .....

View 6 Replies View Related

Macro Fails When Data Is Filtered?

Mar 14, 2014

I have a macro that sends a sheet via email when the engineer has completed it and clicks a button. I added a filter to some of the results to remove blank lines and now the macro will not work. The problem is that if I remove the filter so that the macro does work, I end up with blank lines again.

View 4 Replies View Related

Run A Macro That Only Runs On The Filtered Data

Jan 11, 2007

I have a spreadsheet that is made up of single line items with sub-totals at various points going down the sheet (ie the spreadsheet is split into sections)

Each section is about 100 rows long and 40 columns wide, 10 of which are sub-totals, all using the same formula from that i enter in column A.

As i add new sections i have recorded a simple macro that copies the active cell (ie A15 for example). It then pastes into D15, F15, H15, N15, X15 and then returns to A15.
I have linked this up to ctrl X for ease of running.

I have around 500 section totals to "ctrl X", per spreadsheet and 10 spreadsheets to do in total. I have firstly filtered all the line items (rows) out to leave me just the sub-totals to [ctrl x] [arrow down] [ctrl x] [arrow down] [ctrl x] etc etc etc....
Is there anyway to automate this process, i have tried recording another macro of me [ctrl x] [arrow down] about 30 times, but when i run it, it pastes on the hidden rows, not just on what you see on the screen. Is there a bit of code i can enter so it only sees what is visible.??

View 9 Replies View Related

Copy Filtered Data To New Worksheet Using Excel Macro?

Aug 3, 2013

my requirement is as follows. I have a school data sheet(data sheet) and i have to filter data based on each school id.I need to pass school id in a different excel sheet(input sheet). So if the school id matches then i have to copy the entire data for tht school and move to to a new sheet.

Input sheet
schoolid
211
321

Data Sheet

Emp No Surname last name schoolid
1 marian chin 211
3 luke mar 211
5 uyure tgt d 321
7 fdsfd fdsfsd 333

so whn the macro is run it should filter for 211 and 321 and move these three rows from data sheet to new output sheet.

View 1 Replies View Related

Run GoalSeek Based On Cell Value

Jan 16, 2008

I'm trying to make a macro that will allow me to run multiple Goalseek in the click of a button. This I've been able to do but now I've included one more tweaking which is that I want the variable to change depending on the "method" (ie EBITDA or FMV). Yet I get an error message on the goalseek formula and I don't know what to do and how to solve it. See below for the "macro"

Sub Target_IRR()
If Range("C73") = "EBITDA" Then
For i = 94 To 96
Cells(i, 3).GoalSeek Goal:=Range("F93"), ChangingCell:=Cells(i, 4)
If Cells(i, 4) < 1 Then Cells(i, 4) = 1
Next i
ElseIf Range("C73") = "FMV" Then
For j = 94 To 96
Cells(j, 3).GoalSeek Goal:=Range("F93"), ChangingCell:=Cells(j, 5)
If Cells(j, 5) < 1 Then Cells(j, 5) = 1
Next j
Else: MsgBox ("No Variable")
End If
End Sub

View 3 Replies View Related

A Code To Run GoalSeek For A Range Of Cells Simultaneously

May 18, 2007

I am posting this thread as a follow up to the previously posted thread entitled "IF Function and circular Reference". The reason I am changing the title because the topic we ended up talking about was not reflecting the previous title. My problem is that I got a range of independant cells (M7:M70) and the same number of dependant cells (BA7:BA70) as can be seen in the attached file. What I am trying to do is to make solver to find the value in the independant cell (e.g. M7) that makes the value in the adjacent (i.e. same row) dependant cell (e.g. BA7) equals 0. I can use solver for one row at a time but not for the whole range of cells. The question now is how can I use solver to do this task for the whole range M7:M70 simultaneously?

View 5 Replies View Related

How To Copy Filtered Data Into Filtered Data

Aug 19, 2013

Is it possible to copy data that has been filtered on one tab into filtered data on another tab? I've attached an example, i'm trying to copy the values from column C on the 'From' tab to column D on the 'To' tab. I think the data is always going to be an exact fit in terms of the number of cells copied from and to.

View 1 Replies View Related

Macro To Delete Every Other Row Thats Filtered?

Oct 3, 2008

Can't quite figure out how to do this. I'm looking to create a macro that will delete every other row of a filtered set of data. for example, if these are the filtered rows:

row 21
row 22 delete
row 50
row 51 delete
row 58
row 59 delete.

... and so forth down to the bottom of the filter. I cant seem to record the macro using a relative reference with the filtered data.

View 3 Replies View Related

Macro That Goes Through A Filtered List

Sep 16, 2009

I'm trying to write a macro that goes through every value in a filtered list.

Specifically, I'd like the macro to select the first value in the filter. Perform the operation. Select the second value in the filter. Perform the operation. Select the third value in the filter. Perform the operation. Etc...

View 9 Replies View Related

Excel 2007 :: Using Formula On Filtered Data - How To Select Only Visible Data

Jun 1, 2011

I have a 5000 line table I am filtering by a few columns, and I'd like to calculate an exponential trendline value.

=INDEX(LINEST(LN(R1059:R1167),W1059:W1167),1)

But I actually don't want all the values from R1059 to R1167 - I want to select only the displayed values (R1059, R1068, R1077, etc). Is there a way to select only display values to use in a formula? The problem is it would be a lot of manual work to select them all - there are 50 or so instances I would have to select 13 manual values.

I am using Excel 2007 on XP.

View 8 Replies View Related

Macro To Copy Filtered Values

Oct 28, 2011

I have a large worksheet, with approx 15 columns of data, which is a straight data dump from a very old sales reporting system - so the whole thing is a mess.

As such, I auto filter the report, and select certain criteria from various columns, which leaves me with just the data I wish to see on screen.

What I then need to do is copy across any visible values (after this filtering) from Column C (missing out row 1, which is headings), into the same row in Column E.

At the moment, this is a manual process, because if you highlight the entire selection of codes in column C, then paste in Column E, due to the filtering, the paste puts the values in all the wrong places.

Is there a way around this - or a macro which will copy the values to the same row but in Column E?

View 4 Replies View Related

Deleting Filtered Fields Via Macro

Nov 6, 2012

I have an issue with trying to write a macro to apply a filter and then delete all lines.

Within the macro I copy and paste one sheet to a new one, and Paste Special Values over all formulas, leaving me with a column that has some figures and some "#N/A".

i.e.

13.75
12.00
#N/A
14.00

So I need to do something in the macro that then applies a filter on this column and deletes off all the #n/a results.

This table has a number of columns not just this one so it is the full rows that need removing.

I see on other posts that using a range.specialcells clause could work, but i'm not too sure exactly on how to apply this.

View 1 Replies View Related

VBA Macro To Copy Filtered Range

Apr 22, 2009

This code was provided this forum. It is so close to what I need. The only difference is that I need the copied lines inserted.

Actually, I needed Destination to pick EndT2 (on Sheet2), then select one row down from it and then insert what was copied... -R-

Sub CopyFilteredRangeNoHeaders()
Dim rTable As Range

Set rTable = Sheet1. AutoFilter.Range

Set rTable = rTable.Resize(rTable.Rows.Count - 1)
'Move new range down to start at the fisrt data row.
Set rTable = rTable.Offset(1)
rTable.Copy Destination:=Range("EndT2")

View 9 Replies View Related

Sum Up Filtered Data Only?

Jan 2, 2014

I have been trying to find a way to sum up/only show the filtered data in a sheet.

i want to beable to filter the data by year and then only show sum up the data shown not
the entire column, is there a formula that will let me do this?

View 3 Replies View Related

Get Average Of Filtered Data?

Apr 7, 2014

I have a pivot table with multiple supervisors and emloyees, I need to be able to find the average of a supervisors department and compare it with an employee with the employee seeing everyone else's data.

I don't have access to the original data that drives this pivot table. Is there some way I can use a calculated field to contain the average performance of all the employees under this particular supervisor?

View 3 Replies View Related

Filtered Data Into New Worksheet

Mar 23, 2014

In Worksheet A I have hundreds of Rows... Column 2 has a Type in it and not all rows will have a type and then there are various data elements in column 3, 4, 5 and 6

What I would like to do is in a worksheet B have a formula on each of the cells on a row that would look to worksheet A and would find the first row to match the type value in column 2. I would then want to lookup the value of column 3, and 4, and 5 and 6. Then in worksheet B, the next row the formulas in the cells that would also match type and would continue with the NEXT row in worksheet A that matched the type and again retrieve the appropriate cells. It would continue stepping through the rows of worksheet A until I had all of the now displayed in worksheet B

Basically I want to built a subset of worksheet A into worksheet B using the value of the Type column in worksheet A.

I suspect I am going to need to use multiple forumulas nested together in some way to achieve this.

View 3 Replies View Related

Filter Top 10 Of Filtered Data

Apr 21, 2013

Is it possible to filter a top * for already filtered data. E.g, i have sales people each having their total sales per month , what im trying to achieve is when i filter for only 1 person i want to get the top 10 sales months for that person.

View 2 Replies View Related

Printing Filtered Data

May 23, 2008

I'm attaching a sample workbook which contains two sheets. The 1st sheet is titled "2008" which contains sample data in various columns & rows. The 2nd sheet is titled "print data", which at this time just has a similar, but less titles than on the sheet "2008". What I'm trying to accomplish (if possible) is when data is filtered on the "2008" worksheet, that the data will appear just as it is viewed on the "Print Data" worksheet. In a sense I want it to mirror the data on "2008". The "Print Data" sheet contains a lot less columns of information, which typically is not needed for written summary reports. I'm trying to minimize the amount of paper when printing, etc. I'm just not sure how I can accomplish this task without some type of macro or vba code.

View 12 Replies View Related

Find First Row Of Filtered Data

Jun 4, 2009

I need to place the cursor into the first row of a filtered list, column A.

For example, I have a list from row 6 through row 4500, columns A through AB
I've written the macro to set the autofilter on and apply my selection criteria.
At this point, in this example, the first row of filtered data (below the header row) is in row 3083. I need to move the cursor to cell A3083 and then type in a formula.

View 2 Replies View Related

Average In Filtered Data

Oct 29, 2008

I have a worksheet with exported data from a database query. I used this formula to compute the average of a column without taking the ZERO value:

View 5 Replies View Related

Only Copying Filtered Data

May 3, 2013

I'm struggling with a function I wrote that I intended to filter out the blanks in a column, then only copy the results that aren't blanks to another sheet. The code below copies all data which I don't want. I know I need to be more specific in what range to copy, but I've tried various things but get compile errors.
Code:
Sub createMaterialRequisitionButton()
Worksheets("Material List").Range("$A$19:$E$500").AutoFilter Field:=1, Criteria1:=""

'COPY QUANTITY TO MATERIAL REQUISITION
Worksheets("Material List").Range("A19:A500").Copy
Worksheets("Material Requisition").Range("$A$12").PasteSpecial Paste:=xlPasteValues
End Sub

View 3 Replies View Related

SUMIF On Filtered Data

Aug 18, 2006

I need assistance to create a formula that combines SUMIF and SUBTOTAL. I have created a SUMIF function for a long list of data for approximately 45 staff based on a type of errors.What I would like to do is use the filter by staff id. For example, when I use the filter to choose John, the SUMIF function does not calculate only for John but it still shows for the entire staff. Is there any way I could combine SUMIF and SUBTOTAL so that when I choose a certain staff from that long list, it will calculate accordingly.I have attached a simplified list of the spreadheet. What I need is when I filter by staff ID, the summary for error type and summary for errors by step to change automotically.

View 3 Replies View Related

Group Filtered Data ..

Dec 15, 2006

I created a complex spreadsheet with AutoFilter. For each transaction recorded, the info consist of 4 rows per transaction. When I click the the filtering drop down, the result return with the single line. Is there a way that I can have the results return in group 4 rows? I merge the 4 cells in the first column but it doesn't work the way I want it to.

View 4 Replies View Related

Copying Filtered Data

May 11, 2007

I would like to change the code below so instead of copying the whole column it only copies the items visible (i.e. they have been filtered in a couple of different columns).

For Each rngData In Range("E4:CR258").Columns
rngData.AutoFilter Field:=1, Criteria1:="<>"
rngData.Copy
rngData.EntireColumn.Cells(263, 1).PasteSpecial xlPasteValues
rngData.AutoFilter
Next

View 3 Replies View Related

Copy Filtered Data

May 23, 2007

I have the code below which filters and copies columns. My issues is that this filters and copies all data. I would like to filter all this data from another column before running the macro. And for this code to only then filter and copy the already 'manually' filtered data. Does anyone know how I might go about doing this?

Sub sortdescript2()
Dim rngData As Range
For Each rngData In Range("E4:CR258").Columns
rngData.AutoFilter Field:=1, Criteria1:="<>"
rngData.Copy
rngData.EntireColumn.Cells(263, 1).PasteSpecial xlPasteValues
rngData.AutoFilter
Next

View 4 Replies View Related

Average Filtered Data?

Aug 1, 2007

Is it possible to AVERAGE() the numbers that remain from a filter, and average only those numbers? Can the AVERAGE() change with the criteria that are filtered?

View 2 Replies View Related

Macro To Filter And Delete Row Not Deleting Visible Filtered Rows

Jun 6, 2013

I'm working through a filter macro to delete unecessary rows of data from my dataset.

- I have a Dynamic Range for my dataset called "CanadaData"
- I'm trying to delete rows from the 5th column of my dataset for cells containing "DIRECTSHIP"

The macro filters the range fine, but when if comes to deleting the row, the macro stops.

Sub CanadaWarehouseFilter()
x = Range("E" & Rows.Count).End(xlUp).Row
If Application.WorksheetFunction.CountIf(Range("E22:E" & x), "DIRECTSHIP") > 0 Then

With Range("CanadaData")

[Code] ......

View 2 Replies View Related

Copy And Paste Filtered Data?

Jul 6, 2014

I have copied Row no. 2,4,6 fro this GREEN table and want to paste same date in J and K column (in same row numbers)then how can I do this ?

It should Show like this if I
1 First I filter only Yellow cells
2 then I copy that Filtered cells
3 after that copying that filter cells I did Paste in same rows in J and K column

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved