Change formula to VBA

[leviathan185]

Hi I need to do 1 of 2 things.

1. change the following to VBA instead of a formula

=IF(ROW()<=$Q$1,INDEX('\\Titan\Incident Management\Projector\Reporting\[All_Open_and_Pending_Faults.xls]Sheet1'!C:C,ROW()),INDEX('\\Titan\Incident Management\Projector\Reporting\[All_Open_and_Pending_Faults_FM2.xls]Sheet1'!C:C,ROW()-$Q$1+1))

or alter the following so it will look for the cell value rather than the formula in the cell

Please Login or Register  to view this content.

My problem is that now that I can join 2 reports together i cannot run the VBA script i have found to filter/delete data that I dont want as it seems to be checking the formula and not the value presented.

i would prefer if i could chage the formula to VBA so that users of this workbook do delet formulas by mistake.

Thanks

[ravishankar]

Hi
the formula turned to VBA. Test it and let me know

Please Login or Register  to view this content.

Ravi

[leviathan185]

Is there anywhere i need to put this. when i add it above the code for the command button on the same sheet i want it in it does nothing? is there anything else i need to do?

[ravishankar]

Hi
Open vba editor by pressing ALT F11 and paste the codes in the right hand frame. On running macro, it finds no of rows with data in col a and compares with Q1 and lists the index function in col R. It has worked with a dummy data I tried. It says complete on completion of the macro execution.
Ravi

[royUK]

leviathan185

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[leviathan185]

Hi
Open vba editor by pressing ALT F11 and paste the codes in the right hand frame. On running macro, it finds no of rows with data in col a and compares with Q1 and lists the index function in col R. It has worked with a dummy data I tried. It says complete on completion of the macro execution.
Ravi

Apologies Ravi, I think I have have explained it wrong. i Currently have a workbook that has formulas in it to pull data from 2 difference external workbooks and join them together 1 under the other dynamicly. My Problem is now that I need to delete certain data (full rows) from that depending on what is in certain cells. When I add the command button and run it with the code it does not seem to do what it is supposed to do. i think this is because it can only see the formulas and not the cell values.

I have attached the file so that you can see what i mean.

Thank you

[ravishankar]

HI
Your formula pulls col A to M and all rows from report1 (say 50 rows) and 51 rows onwards from report 2. If you describe what you want the formula/ macro to do, Perhaps we can come up with a solution.
Ravi

[romperstomper]

I'm puzzled by your need for this - Autofilters do not look in the formulas in the column, they look at the returned values. So your original code should work as long as you are looking for the correct values (and assuming your formulas have retrieved the data).

[leviathan185]

HI
Your formula pulls col A to M and all rows from report1 (say 50 rows) and 51 rows onwards from report 2. If you describe what you want the formula/ macro to do, Perhaps we can come up with a solution.
Ravi

The overall result i am looking for is to pull all the data from report 1 and report 2 and join them 1 under the other and then I want to delete rows of data depending on what is in certain cells.

For instance there are some customers that are in the report that i dont need in this report so i want to be able to automaticly delete those rows with that particular customer.

Then I need to be able to delete rows of data depending on the user that owns the fault.

I am looking to do all this with a vba script so that other eople using this report cannot accidentially delete the formulas and it is all hidden away.

I hope this makes sence.

[ravishankar]

Hi
The macro in the attahced file (save it in the folder where FM1 & FM2 are present) copies data from FM! & FM2 and pastes to sheet1 and asks for which customer to delete. if the name entered is in col M, that row is deleted.
Ravi

[leviathan185]

Hi
The macro in the attahced file (save it in the folder where FM1 & FM2 are present) copies data from FM! & FM2 and pastes to sheet1 and asks for which customer to delete. if the name entered is in col M, that row is deleted.
Ravi

Thanks for your persistance Ravi, but I am still having issues with this. i have put the file you uploaded in the same folder as the other 2 report and opened it, run the macro and it gives an error:

The file couold not be accessed. Try one of the following:

- Make sure the specified folder exists
- Make sure the folder that contains the file is not read only
- Make sure the filename does not contain any of the following characters: < > ? [ ] : template.xls or *
- Make sure the file/path name does not contain more than 218 characters.

I have checked all of the above and cannot see any reason why this would be doing this? Any ideas?

[ravishankar]

HI
the filenames used in the macro are "All_Open_and_Pending_Faults_FM1.xls" and "All_Open_and_Pending_Faults_FM2.xls" Rename your files if it is not the same or change the filename in the macro codes to what you have ( Alt f11)
Ravi