如何通过VBA在具有特殊字符

如何通过VBA在具有特殊字符

本文介绍了如何通过VBA在具有特殊字符(如“-")的单元格中插入公式或"$"?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我在互联网上搜索,没有任何帮助...我只是希望能够使用我的VBA代码在单元格中编写此公式:

I searched on internet, without any help coming out of that...I simply would like to be able to have my VBA code to write this formula in a cell :

= IF(C4 =-";-";汽车!C4 * C4 *数据!$ C $ 8)

您猜到了,有一个名为汽车"的页面和一个名为数据"的页面,我可以在其中选择所需的信息.

As you guessed, there is a page called "Cars" and one called "Data" where I pick the informations needed.

当然,因为它是VBA代码,所以C4将是2个变量,一个用于C,另一个用于将演化的4个变量...其实,我尝试过这个:

Of course, as it is a VBA code, the C4 will be 2 variables, one for the C and one for the 4 that will evolve...Actually, I tried this :

Worksheets("Calculation").Range(Column& PosStartCalc + 1).Formula ="="&"IF("&列& PosStartCalc&"="&"-"&;"&-"&;"&汽车!"&列& PosStart&;"*"&列& PosStartCalc&"*"&数据!"&"C"&"8"&)"

(变量Column包含列字母,变量PosStartCalc包含行号)

(The variable Column contains the column letter and the variable PosStartCalc contains the row number)

这会伤害我的眼睛,显然也伤害了VBA的眼睛,因为它给出了错误运行时错误'13':Type Mismatch"谁能告诉我该怎么做?

This hurts my eyes and apparently VBA's ones too as it gives the error "Run-Time error '13': Type Mismatch'Could anyone tell me how to do that?

提前谢谢!

推荐答案

尝试以下操作,假设column变量是字符串,而row是长变量.我可能并没有正确使用所有变量,但是您可以在这里得到我想要做的事情.

Try the following, assuming the column variable is a string and row a long variable. I might not have all the variables right, but you'll be able to get what I meant to do here.

Sub test()

Dim Col As String: Col = "C"
Dim Rw As Long: Rw = 4

With ThisWorkbook.Sheets("Calculation")
    Debug.Print "=IF(" & Col & Rw & "=""-"",""-"",Cars!" & Col & Rw & "*" & Col & Rw & "*Data!$C$8)"
    .Cells(Rw + 1, Col).Formula = "=IF(" & Col & Rw & "=""-"",""-"",Cars!" & Col & Rw & "*" & Col & Rw & "*Data!$C$8)"
End With

End Sub

因此,您可能容易忘记的是在VBA编程公式中使用作为参数定界符.当您将其放在表格上时,Excel会自动将其替换为适合您所在地区的定界符.

So what you might forget easily is to use the , as parameter delimiter in a VBA programmed formula. When you put this on your sheet Excel will automatically replace that with the appropriate delimiter for your region.

另一件事要牢记;每当您要在此类函数中使用字符串值时,请不要忘记将其用双引号引起来!

Another thing to keep in mind; whenever you about to use a string value in such an function, don't forget to wrap it in double quotes!

别忘了删除 Debug.print .... 行.它只是在那里显示输出:)

Don't forget to remove the Debug.print .... line. It was merely there to show the output :)

这篇关于如何通过VBA在具有特殊字符(如“-")的单元格中插入公式或"$"?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-21 14:38