Excel - 根据垂直值选择水平列-Java 学习之路

我有一个工作表通过数据源填充，该数据源垂直和水平地列出日期 .

垂直日期是需要采取行动的项目 .
水平日期是即将到来的工作周日期 .

此时，我想要做的就是根据垂直行中找到的内容选择水平行的相应列 . 我会尝试做一个简单的图表 .

日期3 2 1 4 5 6 7 8 9

在这个例子中;我想选择水平行的列为1（如果这是一个新的工作表ID，则期望它是D列 . 我还需要能够通过运行宏来实现这一点，即我无法点击最初的任何细胞 .

2 回答

好的，谢谢你的指导 .

我打算延伸这个问题 . 基本上我最终想要做的是根据从数据源输入Excel的值来绘制Gannt图表 . 我有需要做的预测开始和结束的工作，所以我打开我的工作表，通过sql server以降序填充部门和日期，然后运行代码 . 这是一个人在这里花了两天时间手动完成这个（很多部门）

现在显然这对我来说很有特色，但我发现操纵这些日期有点棘手 . 我会发布模块的整个代码，以防万一有人在某些时候寻找类似的东西 .

它产生了这个; （我突出显示了隐藏的日期字段 . ）
alt text

说真的，这花了我一整天所以我当然希望它可以帮助某人;）Pace

码;

Sub One_Macro_To_Rule_Them_All()

'

'This clears the WOP sheet for formatting

    Sheets("WOP").Select
    Range("A8").Select
    Selection.CurrentRegion.Select
    Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1, _
    Selection.Columns.Count).Select

    Selection.ClearContents
    Cells.Select
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Cells(3, 2) = "Date : " & Format(Date, "dd/mm/yyyy")



 '**************

'Copy the data to the WOP Sheet

    Sheets("Data").Select

        Rows("1:1").Select
    Range( _
        "Table_FromMyServer_view_ForwardJobsLive_WOP[[#Headers],[Job No]]") _
        .Activate
    Range("B2").Select
    Selection.CurrentRegion.Select
    Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1, _
   Selection.Columns.Count - 1).Select




    Selection.Copy
        Sheets("WOP").Select
        Range("A8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
      Range("A8").Select
      Cells(2, 2) = "Works Order Priority Sheet - " & Cells(8, 1)
    Selection.CurrentRegion.Select
    Selection.Offset(0, 6).Resize(Selection.Rows.Count, _
   Selection.Columns.Count - 6).Select

    curdate = Format(Date, "dd/mm/yyyy")
    Dim dt  As String
     dt = CStr(curdate)

     'find the start of the date range

        Range("A8").Select
    Selection.CurrentRegion.Select
    Selection.Offset(0, 6).Resize(Selection.Rows.Count, _
   Selection.Columns.Count - 6).Select
    Dim rngetosearch As Range
    Set rngetosearch = Selection

 rngetosearch.Find(What:="EARLIER", After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Select
        Selection.Offset(0, 1).Resize(Selection.Rows.Count, Selection.Columns.Count + 1).Select

        dterangestart = ActiveCell



   '*********





'*************

'Format todays column as yellow
    Range("A8").Select
    Selection.CurrentRegion.Select
    Selection.Offset(0, 6).Resize(Selection.Rows.Count, _
    Selection.Columns.Count - 6).Select

Dim sel As Range
Dim rangetosearch As Range
Set rangetosearch = Selection
Dim strdate As String

strdate = Date

    strdate = Format(strdate, "Short Date")

Set sel = rangetosearch.Find(What:=CDate(strdate), After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

  If sel Is Nothing = False Then
    sel.Activate
  End If


         ActiveSheet.Range(sel.Cells.Address, ActiveSheet.Range(sel.Cells.Address).End(xlDown)).Select


    With Selection.Interior
        .Color = 65535
    End With
    '***************
    'Cycle Through the rows and change the blocks
    Sheets("WOP").Select
    Selection.CurrentRegion.Select

        Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1, _
   Selection.Columns.Count - 0).Select

    Dim strtdte As Date
    Dim enddte As Date
    Dim actdte As Date
    Dim diff As Integer
    Dim selrnge As Range
   Set selrnge = Selection


    For Each rwrow In selrnge.Rows
        strtdte = rwrow.Cells(5)
        enddte = rwrow.Cells(7)
        actdte = rwrow.Cells(6)
        cell = rwrow.Cells(1)

        If strtdte < dterangestart Then
            'strtdte = dterangestart
            diff = DateDiff("d", dterangestart, enddte) + 1

        Else
        diff = DateDiff("d", strtdte, enddte)
        End If



        strdate = strtdte
        strdate = Format(strdate, "Short Date")

    Range("A8").Select
    Selection.CurrentRegion.Select
    Selection.Offset(0, 6).Resize(Selection.Rows.Count, _
   Selection.Columns.Count - 6).Select

    Set rngetosearch = Selection
    If strtdte < dterangestart Then
    Set sel = rngetosearch.Find(What:="EARLIER", After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    Else
Set sel = rngetosearch.Find(What:=CDate(strdate), After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
  End If
  If sel Is Nothing = False Then
  Dim col As Integer
  col = CInt(sel.Column)

   Selection.CurrentRegion.Select
    ActiveSheet.Cells(CInt(rwrow.Row), col).Select

    Selection.Offset(0, 0).Resize(Selection.Rows.Count, Selection.Columns.Count + diff).Select
    With Selection.Interior
    .Color = getcolor(CStr(cell))
        If actdte <> #12:00:00 AM# Then
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0.399975585192419
         Else
         .TintAndShade = 0.399975585192419
         .PatternTintAndShade = 0.399975585192419
         End If
    End With
  End If

Next


    '*************
   Range("A8").Select

End Sub

回复于 2024-05-06T05:55:42+08:00

1

我不认为可以使用宏或标准XLS函数来完成 .

您需要编写VBA脚本来比较值 . 一旦写入，可以通过单击按钮或打开XLS来调用 .

回复于 2024-05-06T05:55:42+08:00

Excel - 根据垂直值选择水平列

2 回答

相关问题