将一个excel表中的行数与另一个excel表中的行数进行匹配 [英] Matching number of rows in one excel table with the number of rows in another

问题描述

在Excel中，我使用一些列进行计算，这些列将使用其他列中的信息从其他文件复制而来.但是，此外部数据可以具有不同数量的行，因此，我试图找出一些vba代码，这些代码将拉低计算或拉高计算以扩展或压缩行以匹配导入的数据.

In Excel I am using some columns for calculations that will use information from some other columns who's information is copied from another file. This external data can have a varying number of rows, however, so I am trying to figure out some vba code that will pull down the calculations or pull them up to expand or compress the rows to match the imported data.

例如，A是从其他位置引入的列，而B是计算列.

So for example, A is a column that is brought in from elsewhere and B is the calculation column.

A   B
1   A2*2
2   A3*2
3   A4*2
4   A5*2

现在，假设我在具有八行的列中进行复制.我想下拉B列，使其也有8行.任何形式的指导将不胜感激.

Now suppose I copy in a column that has eight rows. I want to pull down column B so that it has eight rows as well. Any form guidance wouuld be appreciated.

推荐答案

听起来您可以通过自动填充来手动完成此操作，但是如果您希望使用宏，则可以这样开始:

It sounds like you can possibly do this manually with autofill, but if you wish to use a macro something like this could get you started:

Sub Macro1()

    ' put your function here
    Range("B1").FormulaR1C1 = "=RC[-1]*2"

    Dim dest As Integer
    dest = Range("A1").End(xlDown).Row

    Range("B1").AutoFill Destination:=Range("B1:B" & dest), Type:= _
        xlFillDefault

End Sub

这将自动填充B列中的函数，以使A列中包含尽可能多的数据.现在，您声明需要扩展或压缩行以匹配导入的数据." 为此，请清除宏开头的B列.

This will autofill a function in column B to as much data is contained in column A. Now you stated that you need to "expand or compress the rows to match the imported data." You can do this by clearing the B column at the beginning of the macro.

希望这会有所帮助，如果这令人困惑或对您的问题没有帮助，请告诉我.

Hopefully this helps, if this is confusing or does not help your problem let me know.

这篇关于将一个excel表中的行数与另一个excel表中的行数进行匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！