Excel - VB - 重新排序/重命名列 [英] Excel - VB - Re-ordering /Renaming columns

问题描述

除了重命名列之外，我还试图在Excel中使用VB创建一个宏来重新排序/重命名列。也可以更改列标题的标题。

例如：'partner_accountname' - >'帐户名'
'partner_no' - >'partner_number
...等等

我一直在使用以下代码重新排列列（可以正常工作），但我不知道如何重新排列命名列标题：

---

  Sub MoveColumns（）
 
'MoveColumns宏
'
'开发商：Winko Erades van den Berg 
'电子邮件：[e-mail] 
'开发：03-10-2011 
'修改：03-10-2011 
'版本：1.0 
'
'说明：根据列标题重新排列Excel中的列
 
 Dim iRow As Long 
 Dim iCol As Long 
 
'常量值
 data_sheet1 = InputBox（指定Sheet的名称t帽子需要重新组织：）'创建输入框以要求用户需要重新组织哪个工作表
 target_sheet =最终报告'指定要存储结果的工作表
 iRow = Sheets（data_sheet1） .UsedRange.Rows.Count'确定使用了多少行
 
'创建一个新工作表来存储结果
 Worksheets.Add.Name =最终报告
 
'开始组织列
对于iCol = 1 To Sheets（data_sheet1）.UsedRange.Columns.Count 
 
'将TargetCol设置为零，以防止覆盖现有目标列
 TargetCol = 0 
 
'读取原始工作表的标题以确定列顺序
如果表格（data_sheet1）.Cells（1，iCol）.Value =billing_country然后TargetCol = 7 
如果表格（data_sheet1）.Cells（1，iCol）.Value =partner_accountname然后TargetCol = 2 
如果表格（data_sheet1）.Cells（1，iCol）.Value =partner_number然后TargetCol = 3 
如果表格（data_sheet1）.Cells（1， iCol）.Value =pbl_due_date然后TargetCol = 4 
如果表（data_sheet1）.Cells（1，iCol）.Value =total_amountThen TargetCol = 5 
如果表（data_sheet1）.Cells 1，iCol）.Value =pb_payment_currency然后TargetCol = 6 
如果表（data_sheet1）.Cells（1，iCol）.Value =sort_code然后TargetCol = 1 
如果表（data_sheet1）。单元格（1，iCol）.Value =cda_numberThen TargetCol = 8 
 
'如果确定了一个TargetColumn（根据标题信息），然后将列复制到正确的位置
如果TargetCol 0然后
'选择列并复制
表格（data_sheet1）.Range（表格（data_sheet1）.Cells（1，iCol），表格（data_sheet1）.Cells（iRow，iCol））。 Destination：= Sheets（target_sheet）.Cells（1，TargetCol）
 End If 
 
 Next iCol'移动到下一列，直到所有列被读取
 
结束Sub 
  

---

你可以帮我吗？ p>

谢谢，
Ciaran

解决方案

我发现转换表格上的数据表使它更好地编程和更少的凌乱。如果您将数据转换为名称为TableName的表，并希望将某个列＃（对于此示例为2）重命名为列标题，那么这将是代码。

  Dim columnNumber As Long 
 Dim myTab As ListObject 
设置myTab = ActiveSheet.ListObjects（TableName）
 columnNumber = 2 
 myTab.HeaderRowRange（1，columnNumber）=列标题
  

要为多列运行运行它通过for循环：

  Dim columnNumber As Long 
 Dim myTab As ListObject 
 Set myTab = ActiveSheet.ListObjects（TableName）
 
对于x = 1到10'对于列1到10 
 columnNumber = x 
 myTab.HeaderRowRange（1，columnNumber）=列标题
下一个x 
  

I am trying to create a Macro using VB in Excel to re-order /rename columns in a spreadsheet.

As well as re-ordering the columns, I also wish to change the titles of the column headers.

Eg: 'partner_accountname' -> 'Account Name' 'partner_no' -> 'partner_number ...and so on

I have been using the following Code to re-order the columns (which works fine) but I do not know how to re-name the column headers:

---

Sub MoveColumns()

' MoveColumns Macro
'
' Developer: Winko Erades van den Berg
' E-mail : [e-mail]
' Developed: 03-10-2011
' Modified: 03-10-2011
' Version: 1.0
'
' Description: Rearrange columns in Excel based on column header

Dim iRow As Long
Dim iCol As Long

'Constant values
data_sheet1 = InputBox("Specify the name of the Sheet that needs to be reorganised:") 'Create Input Box to ask the user which sheet needs to be reorganised
target_sheet = "Final Report" 'Specify the sheet to store the results
iRow = Sheets(data_sheet1).UsedRange.Rows.Count 'Determine how many rows are in use

'Create a new sheet to store the results
Worksheets.Add.Name = "Final Report"

'Start organizing columns
For iCol = 1 To Sheets(data_sheet1).UsedRange.Columns.Count

    'Sets the TargetCol to zero in order to prevent overwriting existing targetcolumns
    TargetCol = 0

    'Read the header of the original sheet to determine the column order
    If Sheets(data_sheet1).Cells(1, iCol).Value = "billing_country" Then TargetCol = 7
    If Sheets(data_sheet1).Cells(1, iCol).Value = "partner_accountname" Then TargetCol = 2
    If Sheets(data_sheet1).Cells(1, iCol).Value = "partner_number" Then TargetCol = 3
    If Sheets(data_sheet1).Cells(1, iCol).Value = "pbl_due_date" Then TargetCol = 4
    If Sheets(data_sheet1).Cells(1, iCol).Value = "total_amount" Then TargetCol = 5
    If Sheets(data_sheet1).Cells(1, iCol).Value = "pb_payment_currency" Then TargetCol = 6
    If Sheets(data_sheet1).Cells(1, iCol).Value = "sort_code" Then TargetCol = 1
    If Sheets(data_sheet1).Cells(1, iCol).Value = "cda_number" Then TargetCol = 8

    'If a TargetColumn was determined (based upon the header information) then copy the column to the right spot
    If TargetCol <> 0 Then
        'Select the column and copy it
        Sheets(data_sheet1).Range(Sheets(data_sheet1).Cells(1, iCol), Sheets(data_sheet1).Cells(iRow, iCol)).Copy Destination:=Sheets(target_sheet).Cells(1, TargetCol)
    End If

Next iCol 'Move to the next column until all columns are read

End Sub

---

Can you help me with this?

Thanks, Ciaran

解决方案

I have found that converting the data on the sheet to a table makes it a lot better to program for and a lot less messy. If you convert your data to a table with the name 'TableName' and wish to rename a certain column # (2 for this example) to 'Column Title' this would be the code.

Dim columnNumber As Long
Dim myTab As ListObject
Set myTab = ActiveSheet.ListObjects("TableName")
columnNumber = 2
myTab.HeaderRowRange(1, columnNumber) = "Column Title"

To run this for multiple columns just run it through a for loop:

Dim columnNumber As Long
Dim myTab As ListObject
Set myTab = ActiveSheet.ListObjects("TableName")

For x = 1 To 10  ' For Columns 1 through 10
    columnNumber = x
    myTab.HeaderRowRange(1, columnNumber) = "Column Title"
Next x

这篇关于Excel - VB - 重新排序/重命名列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！