主页 / user-11266530

ffc2004's questions

Martin Hope
ffc2004
Asked: 2025-04-30 17:20:37 +0800 CST
  • 6

我有下面的代码，我设法将它们拼凑在一起，将一张表中的行转置到另一张表中我需要的一行上 - 我现在遇到的问题是，当它从表中复制时，它也会复制表格式。

我不确定如何粘贴所有值而不是直接复制/粘贴。

Sub transpLeagueTable()
Dim MyTime, MyDate, MyStr
targetrow = 6
For i = 8 To ActiveSheet.Range("A" & Rows.Count).End(xlUp).row
    ActiveSheet.Range("A" & i & ":A" & i & ",C" & i & ":J" & i).Copy Worksheets("League Table CSV").Cells(targetrow, 2 + (i - 8) * 9)
Next i
Columns("A:A").EntireColumn.Hidden = False
Application.GoTo Sheets("League Table CSV").Range("A6"), True

Columns("A:A").EntireColumn.Hidden = True

Application.CutCopyMode = False
Application.GoTo Sheets("League Table CSV").Range("B6"), True

End Sub

我认为它是 .Copy 部分，但我不确定如何去改变它。

excel
Martin Hope
ffc2004
Asked: 2025-04-03 17:16:36 +0800 CST
  • 6

我已将 Power Query 设置为引用单元格值作为 URL，有没有办法也可以引用自定义列中的单元格值？

let
    URLPart = Excel.CurrentWorkbook(){[Name="URLParameter"]}[Content]{0}[Column1],
    Source = Web.BrowserContents(URLPart),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "DIV.fixtures-table.table-scroll > TABLE > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6), DIV.fixtures-table.table-scroll > TABLE > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(8)"}, {"Column2", "DIV.fixtures-table.table-scroll > TABLE > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(5), DIV.fixtures-table.table-scroll > TABLE > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(7)"}, {"Column3", "DIV.fixtures-table.table-scroll > TABLE > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(5) + TH[colspan=""2""]:not([rowspan]):nth-child(3):nth-last-child(4), DIV.fixtures-table.table-scroll > TABLE > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(6)"}, {"Column4", "DIV.fixtures-table.table-scroll > TABLE > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(5) + TH[colspan=""2""]:not([rowspan]):nth-child(3):nth-last-child(4), DIV.fixtures-table.table-scroll > TABLE > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(5)"}, {"Column5", "DIV.fixtures-table.table-scroll > TABLE > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(5) + TH[colspan=""2""]:not([rowspan]):nth-child(3):nth-last-child(4) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(3), DIV.fixtures-table.table-scroll > TABLE > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(4)"}, {"Column6", "DIV.fixtures-table.table-scroll > TABLE > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(5) + TH[colspan=""2""]:not([rowspan]):nth-child(3):nth-last-child(4) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(3) + TH[colspan=""2""]:not([rowspan]):nth-child(5):nth-last-child(2), DIV.fixtures-table.table-scroll > TABLE > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(3)"}, {"Column7", "DIV.fixtures-table.table-scroll > TABLE > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(5) + TH[colspan=""2""]:not([rowspan]):nth-child(3):nth-last-child(4) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(3) + TH[colspan=""2""]:not([rowspan]):nth-child(5):nth-last-child(2), DIV.fixtures-table.table-scroll > TABLE > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(3) + TD:not([colspan]):not([rowspan]):nth-child(7):nth-last-child(2)"}, {"Column8", "DIV.fixtures-table.table-scroll > TABLE > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(5) + TH[colspan=""2""]:not([rowspan]):nth-child(3):nth-last-child(4) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(3) + TH[colspan=""2""]:not([rowspan]):nth-child(5):nth-last-child(2) + TH:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(1), DIV.fixtures-table.table-scroll > TABLE > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(3) + TD:not([colspan]):not([rowspan]):nth-child(7):nth-last-child(2) + TD:not([colspan]):not([rowspan]):nth-child(8):nth-last-child(1)"}}, [RowSelector="DIV.fixtures-table.table-scroll > TABLE > * > TR"]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Type", type text}, {"Date / Time", type datetime}, {"Home Team", type text}, {"Home Team_1", type text}, {"", type text}, {"Away Team", type text}, {"Away Team_2", type text}, {"Venue", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Date / Time", "DATE"}}),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Renamed Columns", {{"DATE", type text}}, "en-GB"), "DATE", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"DATE.1", "DATE.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"DATE.1", type date}, {"DATE.2", type time}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"DATE.2", "KICK OFF"}, {"DATE.1", "DATE"}, {"Type", "COMPETITION"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"", "Home Team_1", "Away Team"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"DATE", "KICK OFF", "Home Team", "Away Team_2", "COMPETITION", "Venue"}),
    #"Added Custom" = Table.AddColumn(#"Reordered Columns", "Custom", each if[Home Team]="Abbey Rangers" then "H" else "A"),
    #"Renamed Columns2" = Table.RenameColumns(#"Added Custom",{{"Custom", "HOME/AWAY"}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns2",{"DATE", "KICK OFF", "Home Team", "Away Team_2", "COMPETITION", "HOME/AWAY", "Venue"}),
    #"Added Custom1" = Table.AddColumn(#"Reordered Columns1", "OPPOSITION", each if [Home Team]="Abbey Rangers" then [Away Team_2] else [Home Team]),
    #"Reordered Columns2" = Table.ReorderColumns(#"Added Custom1",{"DATE", "KICK OFF", "OPPOSITION", "Home Team", "Away Team_2", "COMPETITION", "HOME/AWAY", "Venue"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns2",{"Home Team", "Away Team_2", "Venue"}),
    #"Reordered Columns3" = Table.ReorderColumns(#"Removed Columns1",{"DATE", "KICK OFF", "OPPOSITION", "HOME/AWAY", "COMPETITION"}),
    #"Added Custom2" = Table.AddColumn(#"Reordered Columns3", "Custom", each if[COMPETITION]="L" then "League" else if[COMPETITION]="SCOM" then "Southern Combination Challenge Cup" else [COMPETITION]),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom2",{"COMPETITION"}),
    #"Renamed Columns3" = Table.RenameColumns(#"Removed Columns2",{{"Custom", "COMPETITION"}})
in
    #"Renamed Columns3"

专门寻找

#"Added Custom" = Table.AddColumn(#"Reordered Columns", "Custom", each if[Home Team]="Abbey Rangers" then "H" else "A"),

我希望它引用单元格 F4 中的值，而不是“Abbey Rangers”

鉴于我已经引用了一个 URL，我不确定该怎么做。我尝试了下面的方法，使用了一个名为 FullTimeName 的单元格，但不太正确

= Table.AddColumn(#"Reordered Columns", "Custom", each if[Home Team]="FullTimeName" then "H" else "A")
excel