Change formula to VBA

**leviathan185** · 07-22-2009, 11:38 PM

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

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
With Columns("M")
    .AutoFilter field:=1, Criteria1:="CLUSTER 3"
    .Resize(Rows.Count - 1).Offset(1).EntireRow.Delete
End With
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True
End Sub

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** · 07-23-2009, 12:33 AM

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

Sub Levi()
Dim x As Long, a As Long
x = Cells(Rows.Count, 1).End(xlUp).Row
Path = "'\\Titan\Incident Management\Projector\Reporting\[All_Open_and_Pending_Faults.xls]Sheet1'!C2:C52"
For a = 2 To x
If a <= Cells(1, 17) Then
Cells(a, 18) = "=Index(" & Path & "," & a & ")"
Else
Cells(a, 18) = "=Index(" & Path & "," & a - Cells(a, 17) + 1 & ")"
End If
Next a
MsgBox "complete"
End Sub

Ravi

**leviathan185** · 07-23-2009, 12:52 AM

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** · 07-23-2009, 01:21 AM

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

**leviathan185** · 07-23-2009, 05:22 PM

Originally Posted by 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

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** · 07-24-2009, 05:00 AM

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

**royUK** · 07-23-2009, 02:07 AM

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

**romperstomper** · 07-24-2009, 06:40 AM

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