Excel VBA合并/组合具有相同名称的列 [英] Excel VBA merge/combine columns with same name

问题描述

我的数据集就是这样

我想要的就是这样（LAST COLUMN）
< img src =https://i.stack.imgur.com/zQagY.jpgalt =enter image description here>

我有很多列同名的Is_paid，Job。
我想要的是创建一个新的列Tot，它以特殊的方式组合了所有这些Is_Paid和Job，如

1. 将所有Is_Paid列合并到Is_Paid_total中



2. 将所有作业列合并到Job_total




3. 代码格式为（不正确）

     Private Sub CommandButton1_Click（）
    
    Dim MyWorksheetLastColumn As Byte 
    
    MyWorksheetLastColumn = Worksheets（1）.Cells（1，Columns.Count）.End（xlToLeft）.Column 
   工作表（1）.Cells（1，MyWorksheetLastColumn + 1）.Value =Tot_Employment
    
    
    
    Dim rngTemp As Range 
    
   设置rngTemp = Cells.Find（*，SearchOrder： = xlByRows，SearchDirection：= xlPrevious）
    
   用rngTemp 
    
    
   对于每个cel In Range（Cells（1，1），rngTemp）
    
   '****************合并步骤********************** 
    
   如果cel.column（Is_Paid_total）=NonPaid，那么
    
    Tot = Is_Paid 
    
    else Tot = Job_total 
     




End If
next Cel
End With
End Sub

步骤3将在for循环

我不知道如何合并/结合得到Is_Paid_Total和Job_Total。
另外我知道我写的if语句是错误的。请帮我解决这个问题。

解决方案

我有一个不同的公式，但是想法几乎是一样的：

您的最后一列应该有这个公式

  IF（ISERROR（MATCH（NonPaid ，$ A2：$ G2,0）），OFFSET（INDIRECT（ADDRESS（ROW（$ D2），MATCH（ 付费，$ A2：$ G2,0））），0,1）， NonPaid） 
  

你需要调整它来满足你的列，在我的例子中，我的最后一列数据是G该公式在列H上。

我想你也可以使用它在vba中使用range（）。formula == ......
然后使用所有范围的填充

My project work has an issue similar to the one described below.

My dataset is like this

What I want is like this (LAST COLUMN)

What I have is many columns of same name like "Is_paid", "Job". What I want is to create a new column "Tot", Which combines all these "Is_Paid" and "Job" in a special manner like,

1. Combine all "Is_Paid" column into "Is_Paid_total"

2. Combine all "Job" column into Job_total

3. And the code format is (Not correct)

   Private Sub CommandButton1_Click()
   
   Dim MyWorksheetLastColumn As Byte
   
   MyWorksheetLastColumn = Worksheets(1).Cells(1, Columns.Count).End(xlToLeft).Column
   Worksheets(1).Cells(1, MyWorksheetLastColumn + 1).Value = "Tot_Employment"
   
   
   
   Dim rngTemp As Range
   
   Set rngTemp=Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious)
   
   With rngTemp
   
   
   For Each cel In Range(Cells(1, 1), rngTemp)
   
   '****************MERGING STEPS**********************  
   
   If cel.column(Is_Paid_total)="NonPaid" then
   
    Tot=Is_Paid
   
    else Tot=Job_total
   

End If next Cel End With End Sub

Step 3 will be in a for loop

I dont know how to merge/combine to get Is_Paid_Total and Job_Total. Also I know the if statement I have written is wrong. Please help me to tackle this problem.

解决方案

i have a different formula but the idea is almost the same:

your last column should have this formula

IF(ISERROR(MATCH("NonPaid",$A2:$G2,0)),OFFSET(INDIRECT(ADDRESS(ROW($D2),MATCH("Paid",$A2:$G2,0))),0,1),"NonPaid")

you need to adapt it to meet your columns, in my exampl my last column with data is G so that formula is on column H.

i think you can also use it in vba using the range().formula = "=......" and then using the filldown for all your range

这篇关于Excel VBA合并/组合具有相同名称的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！