excel - 跨多行Excel VBA的乘法
问题描述
在 VBA 编程方面,我有点菜鸟。我有两个基本的乘法公式,我想将它们应用于表中的所有行。我如何动态地对此进行编码,以便它为表中的每一行复制下来(行数每次都可能改变)?而不是只是按顺序逐行递增地复制此代码......
Dim FxRate As Variant
FxRate = InputBox("Input FX Rate:")
Range("O2").Select
ActiveCell.Value = Range("N2") * Range("L2")
Range("P2").Select
ActiveCell.Value = Range("O2") / FxRate
解决方案
计算并写入单元格
这是五个版本中的中间解决方案,它们都做同样的事情。它们按可读性降序排列(有些人可能认为第二种解决方案最易读,但第一种肯定是最易于维护的。)。第一个是显示使用常量时如何快速找到例如
Res2
(第二个结果列)并将其更改为例如Z
(想象有数百行。)。简而言之,代码首先打开一个
InputBox
用户需要输入数值的位置 (FxRate
)。然后它计算 column 中的 Last Row(最后一个非空白单元格的行)N
。然后它遍历行 fromFirstRow
toLastRow
和 for 每一行将列中的值的乘积写入N
columnL
,O
然后将 column 中的(新)值除以 并将O
除法FxRate
的结果写入 columnP
。最后,使用 aMsgBox
通知用户它已经完成(几乎)。
编码
Option Explicit
' Change the name of the procedure ("spreadData") to something more meaningful.
Sub spreadData()
' Constants
' Use common sense to decide which column to use to calculate the last row.
Const LastRowCol As Variant = "N" ' e.g. 1 or "A"
Const FirstRow As Long = 2
' Find/Replace the following 4 constants names with descriptive ones
' like you did with "FxRate".
Const Col1 As Variant = "N"
Const Col2 As Variant = "L"
Const Res1 As Variant = "O"
Const Res2 As Variant = "P"
' Let the user input a value.
Dim FxRate As Variant
FxRate = InputBox("Input FX Rate:")
' Calculate the Last Row containing data in Last Row Column.
Dim LastRow As Long
LastRow = Cells(Rows.Count, LastRowCol).End(xlUp).Row
' Calculate and write the results to cells.
Dim i As Long
For i = FirstRow To LastRow
Cells(i, Res1).Value = Cells(i, Col1).Value * Cells(i, Col2).Value
Cells(i, Res2).Value = Cells(i, Res1).Value / FxRate
Next i
' Inform user.
MsgBox "Data written.", vbInformation, "Success"
End Sub
Sub spreadDataNoConstants()
' Let the user input a value.
Dim FxRate As Variant
FxRate = InputBox("Input FX Rate:")
' Calculate the Last Row containing data in Last Row Column.
Dim LastRow As Long
LastRow = Cells(Rows.Count, "N").End(xlUp).Row
' Calculate and write the results to cells.
Dim i As Long
For i = 2 To LastRow
Cells(i, "O").Value = Cells(i, "N").Value * Cells(i, "L").Value
Cells(i, "P").Value = Cells(i, "O").Value / FxRate
Next i
' Inform user.
MsgBox "Data written.", vbInformation, "Success"
End Sub
Sub spreadDataNoComments()
Dim FxRate As Variant
FxRate = InputBox("Input FX Rate:")
Dim LastRow As Long
LastRow = Cells(Rows.Count, "N").End(xlUp).Row
Dim i As Long
For i = 2 To LastRow
Cells(i, "O").Value = Cells(i, "N").Value * Cells(i, "L").Value
Cells(i, "P").Value = Cells(i, "O").Value / FxRate
Next i
MsgBox "Data written.", vbInformation, "Success"
End Sub
Sub spreadDataNoSections()
Dim FxRate As Variant
FxRate = InputBox("Input FX Rate:")
Dim LastRow As Long
LastRow = Cells(Rows.Count, "N").End(xlUp).Row
Dim i As Long
For i = 2 To LastRow
Cells(i, "O").Value = Cells(i, "N").Value * Cells(i, "L").Value
Cells(i, "P").Value = Cells(i, "O").Value / FxRate
Next i
MsgBox "Data written.", vbInformation, "Success"
End Sub
Sub spreadDataNoIndentation()
Dim FxRate As Variant
FxRate = InputBox("Input FX Rate:")
Dim LastRow As Long
LastRow = Cells(Rows.Count, "N").End(xlUp).Row
Dim i As Long
For i = 2 To LastRow
Cells(i, "O").Value = Cells(i, "N").Value * Cells(i, "L").Value
Cells(i, "P").Value = Cells(i, "O").Value / FxRate
Next i
MsgBox "Data written.", vbInformation, "Success"
End Sub
推荐阅读
- deep-learning - 我正在实现一个 GAN 网络,但我对如何将它们组合成一个实体感到困惑?
- square-connect - 是否可以使用 Square 将应用开发者帐户的客户与商家帐户共享
- excel - 在 Excel 中创建具有 3 个标准的随机组
- encoding - 01 代表 1 和 10 代表 0 的二进制编码的名称是什么
- javascript - 支持高分辨率显示,同时允许缩放
- qt - QT QTableWidget,如何在项目不可编辑时选择文本
- c++ - 在 C++ 中使用字符串成员作为索引更新自定义结构的最佳方法
- android - 不显示视频,只有在 react-native-video 中播放真正的 android 设备时才会发出声音
- elasticsearch - Elasticsearch:在将 ES 从 5.6.16 迁移到 6.8 期间,upserts 不再支持版本问题
- azure - 我可以增加每个 Azure EventGrid 域的主题配额吗?