根据其他单元格中的值在Excel中复制单元格 [英] copying cells in Excel based on values in other cells

问题描述

基于A2和A3相等且B2和B3也相等的事实，我想知道如何编写宏以将L3的值复制到L2.在列A和B的后续行不相等的情况下，宏无需向上推数据.在某些情况下，可能会有多达10个后续行，其中A&B列的值相等，并且最旧的月份值需要向上滚动到A&的第一行.B比赛.

I would like to know how to write a Macro to copy the value of L3 up to L2 based on the fact that A2 and A3 are equal and B2 and B3 are also equal. Where subsequent rows of columns A and B are not equal the macro does not need to push data up. In some instances there may be as many as 10 subsequent rows where A & B column vales are equal and the oldest month value would need to roll right up to the top row where A & B match.

整个工作表约有150行，其中包含许多City和Company值.我目前正在手动执行此操作，这很耗时.电子表格的目的是根据从基于SQL的CRM包中提取的数据提供销售访问摘要.第4行没有该上下文的有意义的数据，但只要将数据累加到第2行，只要访问量的所有月份都显示在A&列的第一行，它就不会受到损害.B比赛.

The entire sheet has about 150 rows with many City and Company values. I am currently performing this operation manually and it is time consuming. The purpose of the spreadsheet is to provide a sales visit summary based on data that is extracted out of our SQL based CRM package. Row 4 has no meaningful data is that context but it does not hurt if the data is rolled up Row 2 as long as all months with visits are represented on the top row where columns A & B match.

我猜测宏需要遍历JAN(列E)并完成该列，然后在那之后依次遍历G到P.仅需要处理E到P列.我认为，如果数据在单元格中串联或被覆盖，将不会有任何负面影响.完成宏后，位于特定站点的给定公司的第一行应包含其被访问的所有月份的值.

I'm guessing that the macro needs to loop through JAN (Column E) and complete that column and then loop through columns G through P sequentially after that. Only Columns E through P need to be manipulated. I don't think it will have any negative consequences if the data is concatenated in the cells or just overwritten. Once the Macro is complete the top row for a given Company at a certain site should contain values in all of the months where it was visited.

我的编程知识很有限，但是如果我得到了很好的样本，我通常可以对工程师代码进行足够的反向工程来对其进行修改.希望我的要求清楚.

My programming knowledge is limited but I can usually reverse engineer code enough to modify it if I get a good sample. I hope my request is clear.

推荐答案

基于您的第一个初始问题如果B2 = B3和A2 = A3，如何将L3复制到L2" ，此基本宏将使您开始遵循这些原则:

Based on your first initial question "how to copy L3 to L2 if B2=B3 and A2=A3", this basic macro will get you started along those lines:

Option Explicit

Sub CopyUP()
Dim LR As Long, Rw As Long, col As Long

LR = Range("A" & Rows.Count).End(xlUp).Row  'last row with data

For Rw = LR To 2 Step -1                    'from the bottom up, compare
    If Range("A" & Rw) = Range("A" & Rw - 1) And _
       Range("B" & Rw) = Range("B" & Rw - 1) Then

        For col = 5 To 16       'columns E:P
            If Cells(Rw, col) <> "" Then Cells(Rw - 1, col) = Cells(Rw, col)
        Next col

    End If
Next Rw

End Sub

这篇关于根据其他单元格中的值在Excel中复制单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！