Filter code skipping a line

**jad70** · 04-07-2009, 03:46 AM

Hi All,

Apologies for the vague title, but I currently have a problem where a routine is called from a cmb, but it misses the first line. If I go into the VBA project and run it from there the line is not skipped. I will include the code from the cmb.
cmb code:

Private Sub CommandButton2_Click()
    Application.ScreenUpdating = False
    If Sheet1.Visible = True Then Sheet1.Visible = False
    Sheet1.Activate
    Call Update_All   'this calls the update_all macro that updates all the figures on all tasks worksheet
    Unload UserForm3
    Sheet2.Activate
    Call Protect_Sheet1
    Application.ScreenUpdating = True
End Sub

Update_All code

Sub Update_All()  'this code just call all the above code for either the manual input on the Information page, or the OK
                  'button on the Select Nation userform.
                  
Sheet1.Activate
With ActiveSheet
 Call FHtoCal
 Call FCtoCal
 Call EHtoCal
 Call APUCYtoCal
 Call APUHrstoCal
End With
End Sub

and the FH to Cal code whree the problem lies.

Sub FHtoCal() 'The following code filters the All Tasks sheet for FH,FC,EH,APUCY,APUHrs, 2nd FC and 2nd FH.
                'It then runs the calculation to divide the applicable figure from the Information sheet into the count,
                'which will give a calender time.  This time is rounded down.

Dim lrow As Long, rngtarget As Range
With ActiveSheet
    Application.ScreenUpdating = False
    lrow = .Cells(.Rows.Count, 1).End(xlUp).Row
    Set rngtarget = .Range("A3:I" & lrow) 'Set begin row here
    With rngtarget
        .AutoFilter Field:=6, Criteria1:="FH" 'criteria
    End With
    rngtarget.Columns("H").SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=ROUNDDOWN(((RC[-1]/'Information'!R10C2)/3),0)*3"
    rngtarget.AutoFilter
    Application.ScreenUpdating = True
End With
End Sub

The problem is that the figure on row 3 that should be updated with the above formula is not, but as I said if I run it from the project it works.

Any ideas??

JD

**royUK** · 04-07-2009, 04:28 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Your code starts by checking if Sheet1 is visible & hides it if it is. It then tries to activate the hiddn sheet.

**jad70** · 04-07-2009, 04:54 AM

Roy,

Solved the problem by re-ordering the Update_All code. For some reason the last one one, APUHrstocal was reformatting the cells with its calculation. very wierd. This is now run first and the prolem has dissappeared.

The reason behind hiding the sheet hiding is that I could not stop excel going to sheet 1 when the code was being run and then coming back to sheet 2. I want sheet 2 to remain as the active sheet, but the code to still run on sheet 1. I guess my lack of knowledge with selecting sheets has forced me down this route. As for the users, it does not matter if they already have sheet 1 open, but as it is it closes it for them.

JD