可以使用VBA将非结构化引用转换为结构化引用吗？ （Excel 2010） [英] Can You Turn Unstructured References to Structured Using VBA? (Excel 2010)

问题描述

我有一个excel表（一个实际的表，即Insert> Table），它使用所有公式中的结构化引用。示例： = [@ [预计总计2015天]] - COUNTA（All.Departments [@ [1]：[40]]）

我遇到的问题是我要从左到右排列几个列。 Excel不允许你使用表，只有范围（至少从我在主题中找到），所以似乎我唯一的选择是取消列表（将其转换为范围），然后将其转换回。然而，在将其转换回表格之后，我的公式失去了结构化引用。例如： = Sheet2！$ V2-COUNTA（Sheet2！$ X2：$ BK2）

有没有办法将这些公式转换回结构化？或者甚至更好，一种排序从左到右的方式，而不必来回转换我的表？

解决方案

我想到了将我的桌子转换到一个范围的方法。如果有人好奇，我决定发布我的解决方案。

现在设置为复制整个表，将其作为转置值粘贴到目标工作表中，然后在做一个循环，将每个列的日期从最旧到最新，然后将这些排序的日期和它们作为转置的值粘贴到原始表格中。

我自己的解决方案= D

  Private Sub Workbook_BeforeSave（ByVal SaveAsUI As Boolean，Cancel As Boolean）
 
 Application.ScreenUpdating = False 
 
 Dim SourceWS As Worksheet 
 Dim DestWS As Worksheet 
 Dim TblRange As Range 
 Dim DataRange As Range 
 Dim LastRow As Long 
 
设置SourceWS =工作表（仅供人力资源使用）
设置DestWS =工作表（转置的表）
 
 DestWS.Visible = xlSheetVisible 
 DestWS.Cells .ClearContents 
 
 SourceWS.Activate 
 
 LastRow = SourceWS.Range（A1）。CurrentRegion.Rows.Count 
设置TblRange = SourceWS.Range（A1 ：BM& LastRow）
 
 TblRange.Copy 
 DestWS.Range（A1）。PasteSpecial xlPasteValues，xlPasteSpecialOperationNone，True，True 
 Application.CutCopyMode = False 
 
设置DataRange = SourceWS.Range（O2：BB& LastRow）
 DataRange.ClearContents 
 
 DestWS.Activate 
 Dim x As Long，LastCol As Long 
 LastCol = SourceWS.Range（A1）。CurrentRegion.Rows。计数
对于x = 2到LastCol 
范围（单元格（15，x），单元格（54，x））排序Key1：=单元格（15，x），Order1：= xlAscending，标题： = xlGuess 
下一个x 
 
 DestWS.Range（Cells（15，2），Cells（54，LastCol））。复制
 
 SourceWS.Range ）.PasteSpecial xlPasteValues，lPasteSpecialOperationNone，True，True 
 Application.CutCopyMode = False 
 
 DestWS.Visible = xlSheetHidden 
 
 SourceWS.Activate 
 SourceWS。 Range（A1）。选择
 
设置SourceWS = Nothing 
设置DestWS = Nothing 
设置TblRange = Nothing 
设置DataRange = Nothing 
 
 Application.ScreenUpdating = True 
 
 End Sub 
  

I have an excel table (an actual table ie Insert > Table) that uses structured references in all of the formulas. Example: =[@[Predicted Total 2015 Days]]-COUNTA(All.Departments[@[1]:[40]])

The problem I'm having is I want to sort several of the columns from Left to Right. Excel doesn't let you do this with tables, only ranges (at least from what I've found on the subject) So it seems that my only option is to unlist the table (convert it to a range) and then convert it back. However after converting it back to a table my formulas lose their structured references Example : =Sheet2!$V2-COUNTA(Sheet2!$X2:$BK2)

Is there any way to convert these formulas back to structured? Or even better yet, a way to sort left to right without having to convert my table back and forth?

解决方案

I figured out a way around converting my table to a range. In case anyone was curious I decided to post my solution.

It's now set up to copy the entire table, Paste it as transposed values into a destination worksheet, where it then does a loop sorting each column's dates from oldest to newest, and then it takes those sorted dates and pastes them as transposed values back into the original table.

Yay to figuring out my own solution! =D

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Application.ScreenUpdating = False

Dim SourceWS As Worksheet
Dim DestWS As Worksheet
Dim TblRange As Range
Dim DataRange As Range
Dim LastRow As Long

Set SourceWS = Worksheets("For HR Use ONLY")
Set DestWS = Worksheets("Transposed Table")

DestWS.Visible = xlSheetVisible
DestWS.Cells.ClearContents

SourceWS.Activate

LastRow = SourceWS.Range("A1").CurrentRegion.Rows.Count
Set TblRange = SourceWS.Range("A1:BM" & LastRow)

TblRange.Copy
DestWS.Range("A1").PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, True, True
Application.CutCopyMode = False

Set DataRange = SourceWS.Range("O2:BB" & LastRow)
DataRange.ClearContents

DestWS.Activate
Dim x As Long, LastCol As Long
LastCol = SourceWS.Range("A1").CurrentRegion.Rows.Count
For x = 2 To LastCol
Range(Cells(15, x), Cells(54, x)).Sort Key1:=Cells(15, x), Order1:=xlAscending, Header:=xlGuess
Next x

DestWS.Range(Cells(15, 2), Cells(54, LastCol)).Copy

SourceWS.Range("O2").PasteSpecial xlPasteValues, lPasteSpecialOperationNone, True, True
Application.CutCopyMode = False

DestWS.Visible = xlSheetHidden

SourceWS.Activate
SourceWS.Range("A1").Select

Set SourceWS = Nothing
Set DestWS = Nothing
Set TblRange = Nothing
Set DataRange = Nothing

Application.ScreenUpdating = True

End Sub

这篇关于可以使用VBA将非结构化引用转换为结构化引用吗？ （Excel 2010）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！