CODEWITHSUNDEEP
excelvbadebuggingreferenceformula
Jan
Jan

Reputation: 13

Formula does not work in VBA

In the Macro I recorded, I copied a formula from another workbook into the new one because I want the macro to insert this formula in the cell. While the formula worked at that moment, I cannot use the Marco because it's causing a bug.

I suppose the problem might be the reference (settings.xlsm) to another worksheet within the formula. Can anybody tell me how to solve that problem? The code is as follows:

Sub Macro4()
'
' Macro4 Macro
'

'
    Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("I3").Select
ActiveCell.FormulaR1C1 = _
    "=IF(AND([@Staffnumber]=R[-1]C[-7],OR(AND([@start1]<=settings.xlsm!R4C2,MONTH([@start1]<MONTH(settings.xlsm!R4C2))),[@start1]>=settings.xlsm!R4C2,MONTH([@end1]>MONTH(settings.xlsm!R4C2)))),DATE(YEAR(R[-1]C[-5]),MONTH(R[-1]C[-5]),DAY(R[-1]C[-5]+1)),IF(AND([@Staffnumber]<>R[-1]C[-7],OR(MONTH([@start1])<MONTH(settings.xlsm!R4C2),YEAR([@start1])<YEAR(settings.xlsm!R4C2))" & _
    "F(AND([@Staffnumber]<>R[-1]C[-7],OR(MONTH([@end1])>MONTH(settings.xlsm!R4C2),YEAR([@end1])>YEAR(settings.xlsm!R4C2))),DATE(YEAR(R[-1]C[-5]),MONTH(R[-1]C[-5]),1),DATE(YEAR(RC[-5]),MONTH(RC[-5]),1))))"
Columns("I:I").Select
Selection.NumberFormat = "ddmmmyyyy ""00:00"""
Range("J11").Select
End Sub

Thank you for your help!

Upvotes: 0

Views: 1099

Answers (1)

Archias
Archias

Reputation: 383

Dave is right. Each workbook reference in the formula is not written properly.

A blip from a reference from microsoft on how to reference external workbooks from a workbook formula. http://office.microsoft.com/en-us/excel-help/create-an-external-reference-link-to-a-cell-range-in-another-workbook-HP010102338.aspx

================

EXTERNAL REFERENCE

=SUM([Budget.xlsx]Annual!C10:C25)

When the source is not open, the external reference includes the entire path.

EXTERNAL REFERENCE

=SUM('C:\Reports[Budget.xlsx]Annual'!C10:C25)

================
Notice the format: "=FunctionName('Path'[Workbook]Worksheet'!Cell/Range)"
Note: the apostrophes, parenthesis, brackets and exclamation point are very important.

This doesn't actually reference anything: "settings.xlsm!R4C2"

Assuming "settings.xlsm" is open when running this macro then this following formula would work if it were referencing a worksheet named "Sheet1" in "settings.xlsm"

"=([settings.xlsm]Sheet1!R4C2)"

I suggest doing this:
Your issue is obviously with the formula. I suggest you break it down piece by piece. Make sure each element of the formula has a valid evaluation outside of the formula. Then slowly build the formula again adding each element one at a time.

Ex:

1. [@Staffnumber]=R[-1]C[-7]
2. [@start1]<=settings.xlsm!R4C2
3. [@start1]<MONTH(settings.xlsm!R4C2)

etc.,

Then slowly build each subformula that you will eventually use within the final formula

1. [@Staffnumber]=R[-1]C[-7]
2. @start1]<=settings.xlsm!R4C2
3. MONTH([@start1]<MONTH(settings.xlsm!R4C2)
4. AND([@start1]<=settings.xlsm!R4C2,MONTH([@start1]<MONTH(settings.xlsm!R4C2))
5. AND([@Staffnumber]=R[-1]C[-7],OR(AND([@start1]<=settings.xlsm!R4C2,MONTH([@start1]<MONTH(settings.xlsm!R4C2))),[@start1]>=settings.xlsm!R4C2,MONTH([@end1]>MONTH(settings.xlsm!R4C2))))

Upvotes: 1

Related Questions