Reorded Macro Formula

[sully2]

Hi, and thanks in advance, I have recorded a macro, when I ran it, it came up with a "1004 Runtime Error". when I debugged it it showed the formula I had created contained the error, can anyone help here, I'm not really efficient with code, the formula is as follows;

in Cell M4 - =IFERROR(IF(N4>0,N4,IF(L4=45,'Build Sheet'!$J$7,IF(L4=60,'Build Sheet'!$J$8,IF(L4=90,'Build Sheet'!$J$9,IF(L4=120,'Build Sheet'!$J$10,IF(L4="V & Test 45",'Build Sheet'!$J$14,IF(L4="V & Test 60",'Build Sheet'!$J$15,IF(L4="Test",'Build Sheet'!$J$11,IF(L4="Test 45",'Build Sheet'!$J$12,IF(L4="Test 60",'Build Sheet'!$J$13,IF(L4="",""))))))))))),"")

Macro Recorded this;

Range("M4").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IF(RC[1]>0,RC[1],IF(RC[-1]=45,Buildsheet!R7C10,IF(RC[-1]=60,Buildsheet!R8C10,IF(RC[-1]=90,Buildsheet!R9C10,IF(RC[-1]=120,Buildsheet!R10C10,IF(RC[-1]=""V & Test 45"",Buildsheet!R14C10,IF(RC[-1]=""V & Test 60"",Buildsheet!R15C10,IF(RC[-1]=""Test"",Buildsheet!R11C10,IF(RC[-1]=""Test 45"",Buildsheet!R12C10,IF(RC[-1]=""Test 60"",Buildsheet!R13C10,IF(RC[-1]=""""," & _
"))))))),"""")"

Cheers!

 

[Peter_SSs]

I think the formula string has just got a bit long for the vba to handle.

See if this work-around works for you.

Const f As String = "=IFERROR(IF(RC[1]>0,RC[1],IF(RC[-1]=45,#R7C10,IF(RC[-1]=60,#R8C10,IF(RC[-1]=90,#R9C10,IF(RC[-1]=120,#R10C10,IF(RC[-1]" _
                    & "=""V & Test 45"",#R14C10,IF(RC[-1]=""V & Test 60"",#R15C10,IF(RC[-1]=""Test"",#R11C10,IF(RC[-1]=""Test 45"",#R12C10," _
                    & "IF(RC[-1]=""Test 60"",#R13C10,IF(RC[-1]="""",""""))))))))))),"""")"

Range("M4").FormulaR1C1 = Replace(f, "#", "Buildsheet!", 1, -1, 1)

 

[sully2]

Thanks, the Macro runs in it's entirety, however now I have problems, the formula doesn't calculate, or at least the Cell doesn't populate as it should

 

[Peter_SSs]

Thanks, the Macro runs in it's entirety, however now I have problems, the formula doesn't calculate, or at least the Cell doesn't populate as it should

There was some confusion in your initial post about the sheet name - 'Build Sheet' in your stated formula and 'Buildsheet' in your recorded macro - but once I changed the sheet name to 'Build Sheet' then the formula inserted by my macro is returning identical results to the formula I copied from your post.

 

[sully2]

Thanks for your help here Peter, I have re-recorded the macro using your formula, for some reason when I run the macro it opens a dialog box "Update Values: Buildsheet" like a "Save As" dialog box. I have run the re-recorded macro without the code you developed for me, and the macro runs perfectly, however when I insert your code, that's when I run into problems, I'm a little out of my depth here so any help would be great.

Cheers!

 

[Peter_SSs]

I have re-recorded the macro using your formula,

I'm not sure exactly what you mean by that.

Never-the-less the following symptoms will occur if the sheet name being inserted into the formula by the macro does not match any of the sheet names in your workbook. As I mentioned before there was confusion about your actual sheet name. If the macro is inserting 'Buildsheet' into the formula but your actual sheet name is 'Build Sheet' or 'Buildsheet ' etc then you will get this error.

.. when I run the macro it opens a dialog box "Update Values: Buildsheet" like a "Save As" dialog box. I

The upshot is: Check your sheet names carefully both on both the actual sheet tab itself and in the macro and make sure they are identical.

Also, if the sheet name actually has any spaces in it then the sheet name in the formula needs to be surrounded by single quote marks, as shown in the first formula in post #1. If that is the case, or to have the code able to automatically handle such a case my code would change to this. Note the single quote marks around the # symbol and also the inclusion of ! in the Const line & the removal of the ! from the Replace function code. This code structure would still work if the sheet name is a single word.

Const f As String = "=IFERROR(IF(RC[1]>0,RC[1],IF(RC[-1]=45,'#'!R7C10,IF(RC[-1]=60,'#'!R8C10,IF(RC[-1]=90,'#'!R9C10,IF(RC[-1]=120,'#'!R10C10,IF(RC[-1]" _
                    & "=""V & Test 45"",'#'!R14C10,IF(RC[-1]=""V & Test 60"",'#'!R15C10,IF(RC[-1]=""Test"",'#'!R11C10,IF(RC[-1]=""Test 45"",'#'!R12C10," _
                    & "IF(RC[-1]=""Test 60"",'#'!R13C10,IF(RC[-1]="""",""""))))))))))),"""")"

Range("M4").FormulaR1C1 = Replace(f, "#", "Build Sheet", 1, -1, 1)