ClimoC
Well-known Member
- Joined
- Aug 21, 2009
- Messages
- 584
Hello again all...
This formula works perfectly well in excel, but when the following vba code tries to put the code in (with added declarations for which sheet the index and match are meant to be using for calculations), I get "object doesn't support this property or method"
I can't figure out what is going on!!!
In the following code, I have two examples. The second instance is how the first one looked before. (swapped between using $E$6 style ref's to R6C5 style ref's). Thing is, neither one works and it never gets passed the formula (which is what is highlighted upon debugging)
Can anyone see what my problem is?
Cheers
C
This formula works perfectly well in excel, but when the following vba code tries to put the code in (with added declarations for which sheet the index and match are meant to be using for calculations), I get "object doesn't support this property or method"
I can't figure out what is going on!!!
In the following code, I have two examples. The second instance is how the first one looked before. (swapped between using $E$6 style ref's to R6C5 style ref's). Thing is, neither one works and it never gets passed the formula (which is what is highlighted upon debugging)
Can anyone see what my problem is?
Cheers
C
Rich (BB code):
Sub boomerang()
Application.ScreenUpdating = False
Dim wb1 As Workbook
Dim master As Workbook
Dim sourcer As Range
Dim destr1, destr2, destr3, destr4, destr5, destr6, destr7, destr8, destr9, destr10 As Range
Dim c As Range
Dim r As Range
Dim ssheet As Worksheet
Set wb1 = ThisWorkbook
wb1.Activate
Set ssheet = ActiveSheet
Set master = Workbooks("test.xls")
If Err.Number > 0 Then Set master = Workbooks.Open(Filename:="C:\Documents and Settings\ClimoC\Desktop\finished and finalised copies\test.xls")
If Not master Is Nothing Then master.Worksheets("Schedule").Activate Else MsgBox "File not found", vbInformation
master.Activate
Sheets("Schedule").Activate
Application.Calculation = xlCalculationManual
Set destr1 = Range("AA6:AA500")
For Each Cell In destr1
Cell.FormulaR1C1 = _
"=IF(ISERROR(MATCH(1,(" & wb1 & ".xls!R6C5:R100C5=R6C9)*(" & wb1 & ".xls!R6C9:R100C9=R6C14)*(" & wb1 & ".xls!R6C6:R100C6=R6C10),0)),"",INDEX(" & wb1 & ".xls!R6C14:R100C15,MATCH(1,(" & wb1 & ".xls!R6C5:R100C5=R6C9)*(" & wb1 & ".xls!R6C9:R100C9=R6C14)*(" & wb1 & ".xls!R6C6:R100C6=R6C10),0)))"
Next
master.Activate
Sheets("Schedule").Activate
Set destr2 = Range("AB6:AB500")
For Each Cell In destr2
Cell.FormulaR1C1 = _
"=IF(ISERROR(MATCH(1,('[" & wb1 & ".xls]'$E$6:$E$100=I6)*('[" & wb1 & ".xls]'$I$6:$I$100=N6)*('[" & wb1 & ".xls]'$F$6:$F$100=J19),0)),"",INDEX('[" & wb1 & ".xls]'$P$6:$P$100,MATCH(1,('[" & wb1 & ".xls]'$E$6:$E$100=I6)*('[" & wb1 & ".xls]'$I$6:$I$100=N6)*('[" & wb1 & ".xls]'$F$6:$F$100=J19),0)))"
Next
master.Activate
Sheets("Schedule").Activate
