Problem in VBA code

[asad]

Hello Guys,

I have the following code.

Private Sub CommandButton1_Click()
' Macro recorded 25/05/2011 by Ali
'
Dim myPath As String
    Dim myFormulaPart1 As String
    Dim myFormulaPart2 As String
    Dim myFormulaPart3 As String
Dim xx As Range
Set xx = Range(Range("F98"), Range("F98").End(xlDown))
xxx = xx.Value
temp = xxx(UBound(xxx), 1)
For i = UBound(xxx) - 1 To 1 Step -1
  xxx(i + 1, 1) = xxx(i, 1)
Next i
xxx(1, 1) = temp
'xx.Offset(, 1) = xxx 'debug line.
xx.Value = xxx
    myPath = "O:\Operations Supervisor\`Rosters 2012\[Leave  allocation 2012 Master.xlsm]Leave Approved"
    
    myFormulaPart1 = "=IF(RC[-6]<>""A/L"","""",""A/L ""&TEXT(MIN(IF('" & myPath & "'!R15C9:R215C9=RC[-2],X_X_X)),""dd/mm/yyyy""))"
    myFormulaPart2 = "IF('" & myPath & "'!$I$4:$FQ$4>=$X$1,IF('" & myPath & "'!$I$15:$FQ$215="""",Y_Y_Y))))"
    myFormulaPart3 = "'" & myPath & "'!$I$4:$FQ$4-7"
[X1] = [X2].Value
[D8:F35].Copy [D9:F36]
[D36:F36].Copy [D8:F8]
[D40:F67].Copy [D41:F68]
[D68:F68].Copy [D40:F40]
[E72:F75].Copy [E73:F76]
[E76:F76].Copy [E72:F72]
[E80:F83].Copy [E81:F84]
[E84:F84].Copy [E80:F80]
[E88:F95].Copy [E89:F96]
[E96:F96].Copy [E88:F88]
[E129:F152].Copy [E130:F153]
[E153:F153].Copy [E129:F129]
[D157:F177].Copy [D158:F178]
[D178:F178].Copy [D157:F157]
[E182:F185].Copy [E183:F186]
[E186:F186].Copy [E182:F182]
[E190:F193].Copy [E191:F194]
[E194:F194].Copy [E190:F190]
[Z8:AB95,Z129:AB200,A8:B200,H8:H95,H100:H122,H129:H200,D36:F36,D68:F68,E76:F76,E84:F84,E96:F96,E153:F153,D178:F178,E186:F186,E194:F194].ClearContents
[Z7:AB7].Copy [Z7:AB95]
[Z7:AB7].Copy [Z129:AB200]
[Z100:Z122].Copy
Range("F100:F122").PasteSpecial xlPasteValues
[B][COLOR=#FF0000]Range("Z100").Formula = "=Lookup(2/1($F$100:$F$122<>""Vacant""),$F$100:$F$122)"[/COLOR][/B]
Range("Z101:Z122").Formula = "=if(F101=""Vacant"",""Vacant"",F100)"
Range("D98:D119").Formula = "=INDEX(CM$98:CN$119,MATCH(F98,CM$98:CM$119,0),2)"
[A7:B7].AutoFill Destination:=[A7:B200], Type:=xlFillDefault
With Range("G8")
        .FormulaArray = myFormulaPart1
        .Replace "X_X_X))", myFormulaPart2
        .Replace "Y_Y_Y", myFormulaPart3
    End With
    
    Range("G8").Copy
    Range("G9:G35,G40:G67,G72:G75,G80:G83,G88:G95,G129:G152,G157:G177,G182:G185,G190:G193").PasteSpecial xlPasteFormulas
    
    Range("H8:H35,H40:H67,H72:H75,H80:H83,H88:H95,H122:H152,H157:H177,H182:H185,H190:H193").Formula = "=IF(G8="""","""",INDEX($F$98:$F$119,MATCH(C8,$H$98:$H$119,0)))"
    
[Y:CE].EntireColumn.Hidden = True
[E:E].EntireColumn.Hidden = True
[A:B].EntireColumn.Hidden = True
[H100] = "Find Work"
[H100].Copy [H101:H122]
[H3].Select
ActiveWorkbook.SaveAs Filename:="O:\Operations Supervisor\`Rosters 2012\FSCY " & Format(Range("X1").Value, "yy-mm-dd") & ".xlsm"
End Sub

When I run this macro, it stops and the line that I have highlighted in shown in Red and Bold gets highlighted in yellow.
I am not able to figure out what the problem is.

Can someone please help me?

Asad

 

[sous2817]

The problem seems to be your formula and not VBA. Have you tried to enter the formula in manually and see if it works? When I try I get an error and this is the proposed formula:

=LOOKUP(2/1*($F$100:$F$122<>"Vacant"),$F$100:$F$122)

 

[Michael M]

Try running the formula by itself.
It doesn't seem to fit the criteria for a Lookup formula, especially the part

(2/1

 

[asad]

:D

You are absolutely right. I didn't look at my formula in the code. I must have typed it wrong, and then didn't even look at it. Thanks a lot for your quick help.
The formula should be

[B][COLOR=#ff0000]"=Lookup(2,1/($F$100:$F$122<>""Vacant""),$F$100:$F$122)"[/COLOR][/B]

Asad

 

[Michael M]

glad you got it sorted....