VBS插入公式 - 遇到麻烦 [英] VBS to Insert Formula - having trouble

问题描述

Hi Folks -

我在将公式插入Excel时遇到问题。

我需要采用这个公式：

" = IF（AND（N22620 =" MYC"，X22620 =值（2017），C22620< 4），L22620，IF（AND（N22620 ="Oct LBE"，X22620 = VALUE（2017），C22620 <4），L22620，IF（AND（N22620 ="Feb LBE"， X22620 = VALUE（2017），C22620< 4），L22620，IF（AND（N22620 = QUOT; MRP"，X22620 = VALUE（2017），C22620< 4），L22620,0））））" 
 

但是用变量替换呼叫号码。我尝试了这个，但它给了我错误，"预期的结束声明。"

 
 STRNG =" 2017"设置FoundCell = objWorkbook1.Worksheets（" Sheet1"）。范围（"Y2：Y"& LastCell1.Row）.Find（STRNG）
     
 objWorkbook1.Worksheets（" Sheet1"）。范围（""& FoundCell.Row）.Value =" = IF（AND（N"& FoundCell.Row&" = "MYC"，X&& FoundCell.Row&" = VALUE（2017），C& FoundCell.Row&"< 4），L& FoundCell.Row&"， IF（AND（N&& FoundCell.Row&" =" Oct LBE"，X&& FoundCell.Row&" = VALUE（2017），C& FoundCell.Row&" < 4），L"& FoundCell.Row&"，IF（AND（N&& FoundCell.Row&" =" Feb LBE"，&& FoundCell.Row&" = VALUE（2017），C& FoundCell.Row&"< 4），L"& FoundCell.Row&"，IF（AND（N&& FoundCell.Row&" = "MRP"，X"& FoundCell.Row&" = VALUE（2017），C& FoundCell.Row&"< 4），L& FoundCell.Row&"， 0））））" 
 
 

谢谢！

解决方案

你需要加倍文本值MYC，Oct LBE等的引号，所以：""MYC""其余的类似。

条件非常相似，因此公式可以简化：

   范围（"S"& FoundCell.Row）.FormulaR1C1 = _

        "= IF（AND（OR（RC14 = {""MYC"，""，"Oct LBE"，""，"LBE"，""，"MRP"，"} ），RC24 = VALUE（2017），RC3< 4），RC12,0）"

如果您想用STRNG替换2017

&NBSP;&NBSP;&NBSP;范围（"S"& FoundCell.Row）.FormulaR1C1 = _

        "= IF（AND（OR（RC14 = {""MYC"，""，"Oct LBE"，""，"LBE"，""，"MRP"，"} ），RC24 = VALUE（& _

            STRNG&"），RC3< 4），RC12,0）"

Hi Folks -

I'm having trouble inserting a formula into excel.

I need to take this formula:

"=IF(AND(N22620="MYC",X22620=VALUE(2017),C22620<4),L22620,IF(AND(N22620="Oct LBE",X22620=VALUE(2017),C22620<4),L22620,IF(AND(N22620="Feb LBE",X22620=VALUE(2017),C22620<4),L22620,IF(AND(N22620="MRP",X22620=VALUE(2017),C22620<4),L22620,0))))"

But replace the call numbers with a variable. I tried this but it's giving me the error, "Expected end of statement."

STRNG = "2017"
Set FoundCell = objWorkbook1.Worksheets("Sheet1").Range("Y2:Y" & LastCell1.Row).Find(STRNG)
    
objWorkbook1.Worksheets("Sheet1").Range("S" & FoundCell.Row).Value = "=IF(AND(N" & FoundCell.Row & "="MYC",X" & FoundCell.Row & "=VALUE(2017),C" & FoundCell.Row & "<4),L" & FoundCell.Row & ",IF(AND(N" & FoundCell.Row & "="Oct LBE",X" & FoundCell.Row & "=VALUE(2017),C" & FoundCell.Row & "<4),L" & FoundCell.Row & ",IF(AND(N" & FoundCell.Row & "="Feb LBE",X" & FoundCell.Row & "=VALUE(2017),C" & FoundCell.Row & "<4),L" & FoundCell.Row & ",IF(AND(N" & FoundCell.Row & "="MRP",X" & FoundCell.Row & "=VALUE(2017),C" & FoundCell.Row & "<4),L" & FoundCell.Row & ",0))))"

Thanks!

解决方案

You need to double the quotes around the text values MYC, Oct LBE etc., so: ""MYC"" and similar for the rest.

The conditions are very similar, so the formula can be simplified:

    Range("S" & FoundCell.Row).FormulaR1C1 = _
        "=IF(AND(OR(RC14={""MYC"",""Oct LBE"",""Feb LBE"",""MRP""}),RC24=VALUE(2017),RC3<4),RC12,0)"

If you want to replace 2017 with STRNG

    Range("S" & FoundCell.Row).FormulaR1C1 = _
        "=IF(AND(OR(RC14={""MYC"",""Oct LBE"",""Feb LBE"",""MRP""}),RC24=VALUE(" & _
            STRNG & "),RC3<4),RC12,0)"

这篇关于VBS插入公式 - 遇到麻烦的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！