(Excel VBA)如何在找到单元格之前将多列和这些列的cell.value插入excel工作表中? [英] (Excel VBA) How to insert multi columns and cell.value for those columns into excel sheet before found cell?

问题描述

1.背景与目的
我有一个带有多列表的Excel工作表. 我想通过宏插入一些列(黄色):

1. Background & purpose
I have a Excel sheet with table of multi-columns. I want to insert some columns(in yellow) via macro:

• "PIC"之前的总成本"，其公式为:Total Cost = Man-hour * C (其中C是常数)
• 审阅日期"，审阅者"，月份"之前的年份"以及基于"Inspected.Date"确定会计年度(年份")的公式.
  例如:Cell of Year = YEAR("Inspected.Date")+IF(MONTH("Inspected.Date")>=$D$1,1,0)

• "Total Cost" before "PIC" with formula: Total Cost = Man-hour * C (where C is constant)
• "Reviewed.Date", "Reviewer", "Year" before "Month" and formula to determine fiscal year ("Year") based on "Inspected.Date".
  Ex: Cell of Year = YEAR("Inspected.Date")+IF(MONTH("Inspected.Date")>=$D$1,1,0)

更多细节请参考这张图片.

Please refer to this picture for more detail.

2.问题与问题
我可以遍历所有列，并检查Column.header ="PIC"或"Month"，然后插入新列.但是我不知道如何用公式插入列标题"和该列中所有单元格的值"，因为在插入新列之后，某些列的位置将发生变化. 那么，我该如何使用宏呢? 任何帮助将不胜感激. 非常感谢您的关注.

2. Problem & questions
I can loop through all columns and check if Column.header = "PIC" or "Month", then insert new columns. But I don't know how to insert "Column header" and "value of all cells in that column" with formula because after inserting new column, position of some columns will be changed. So, how can I do this with macro? Any helps would be highly appreciated. Thank you so much for your attention.

3.这是我当前的VBA

Sub InsertNewColumn()
   'Edit SPO Documents.
   'Consider worksheet"Sheet1"

   With Worksheets("Sheet1")

       '1. Find out last columns & last row in target sheet.
        LastCol = .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, _
                  SearchDirection:=xlPrevious).Column
        LastRow = .Cells.Find(What:="*", SearchOrder:=xlByRows, _
                  SearchDirection:=xlPrevious).Row        
       '2.Loop through all columns and check column header. 
       '  If column header is "PIC" or "Month", then insert new column

        For i = LastCol To 1 Step -1            
            On Error Resume Next            
            CellValue = .Cells(1, i)   'Get Column header   

            'Insert column "Total Cost"
            If CellValue = "PIC" Then
                .Cells(1,i).EntireColumn.Insert Shift:=xlToLeft
                .Cells(1,i).Value2 = "Total Cost" 'Add column header            
            End If

            'Insert column "Year"
            If CellValue = "Month" Then
                .Cells(1,i).EntireColumn.Insert Shift:=xlToLeft
                .Cells(1,i).Value2 = "Year" 'Add column header

                'Add formula to year
                With Sheets("Sheet1").Columns(i)
                    For j = LastRow To 2 Step -1                       
                        'Sheets("Sheet1").Cells(j, i).Formula = "=YEAR("Inspected.Date")+IF(MONTH("Inspected.Date")>=$D$1,1,0)"
                        'I want to add formula to determine fiscal year for cell of Year based on "Inspected.Date"
                        'but I dont know how to do
                    Next j               
                End With
            End If  
        Next i   
    End With
    Application.ScreenUpdating = True   
End Sub

推荐答案

您不需要For ... Next循环，就象查找和插入列(如果尚不存在)的可靠方法一样.

You don't need a For ... Next loop so much as a reliable method of locating and inserting columns if they do not already exist.

dim c1 as variant, c2 as variant
with worksheets("sheet1")
    c1 = application.match("pic", .rows(1), 0)
    c2 = application.match("total cost", .rows(1), 0)
    if iserror(c2) and not iserror(c1) then
        .cells(1, c1).entirecolumn.insert
        .cells(1, c1) = "total cost"
    end if

    c1 = application.match("month", .rows(1), 0)
    c2 = application.match("reviewed.date", .rows(1), 0)
    if iserror(c2) and not iserror(c1) then
        c2 = application.match("inspected.date", .rows(1), 0)
        .cells(1, c1).resize(1, 3).entirecolumn.insert
        .cells(1, c1).resize(1, 3) = array("reviewed.date", "reviewer", "year")
        .range(.cells(2, c1+2), .cells(.rows.count, c2).end(xlup).offset(0, 4)).formula = _
            "=year(" & .cells(2, c2).address(0, 0) & ")+(month(" & .cells(2, c2).address(0, 0) & ")>=$d$1)"
    end if
end with

请注意，我已将您的公式修改为更简单的形式.

Note that I've modified your formula to the simpler form.

= YEAR("Inspected.Date")+(MONTH("Inspected.Date")>=$D$1)

这篇关于(Excel VBA)如何在找到单元格之前将多列和这些列的cell.value插入excel工作表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！