声明一个变量，其中显示了一个在表格中发现的日期 [英] Declaring a variable with a date found in sheet, issue

问题描述

目前，我正在尝试声明一个变量作为我从excel中的工作表中提取的日期，然后使用该声明的日期作为基准，所以我可以删除大于它的一切。我认为我的代码对于这个任务是坚实的，但我一直在关注的问题是当我看到我的日期被宣布为而不是1/12/2015，因为它应该是在12:00:00 am，它取代了我从中提取日期的单元格的内容。这是我的代码到目前为止...

  Sub Remove_Unecessary_Data_1（）
 
 Dim ALLCs As Worksheet 
 Dim DS As Worksheet 
 Dim Dailystring As String 
 Dim Blank As String 
 Dim finaldate As Date 
 
 
 Dailystring = - 每日
空白=
 
设置DS =表（数据摘要）
设置ALLCs =表（资产有限责任公司（输入））
 
 
 
 ALLCs.Select 
对于y = 1至40 
如果InStr（1，Allcs.Cells（13，y），执行时间戳）然后
 finaldate = Allcs.Cells（50，y）
 End If 
 Next 
 
 ALLCs.Select 
对于u = 1至40 
如果InStr（1 ，Allcs.Cells（13，u），开始日期）然后
对于p = 2 To 69584 
如果allcs.Cells（p + 14，u）>然后
 allcs.Cells（p + 14，u）.EntireRow.Delete 
 End If 
 Next 
 End If 
 Next 
  

我认为这里的主要问题在于最终日期正在转化为这个奇怪的时刻。任何想法？

另请参阅这个单元格从50提取，y读取

1/12/2015

编辑：我更新了我的代码，以反映@freemans评论的建议，当我尝试赋值一个值时，我的finaldate的位置。我有它的逆转。

我的新问题是我的代码的第二部分不能准确地删除所有必要的日期。例如，我需要删除大约1/12/15的所有日期，我的程序仍然不这样做，以为我没有看到我的代码中的错误

  ALLCs.Select 
对于u = 1至40 
如果InStr（1，Allcs.Cells（13，u），开始日期）然后
对于p = 2 To 69584 
如果allcs.Cells（p + 14，u）>然后
 allcs.Cells（p + 14，u）.EntireRow.Delete 
 End If 
 Next 
 End If 
 Next 
  

我的问题在于这里，为什么如果finaldate被宣布为1/12/15，1/13/15不会被删除。 >

解决方案

您声明

  Dim finaldate作为日期
  

，您在这里使用

 单元格（50，y）= finaldate 
  

/ p>

 如果Cells（p + 14，u）>然后
单元格（p + 14，u）.EntireRow.Delete 
如果
  

但是您从未将值分配给 finaldate ，因此您填写的日期/时间 0 。 Cells（[+ 14，u））/ / code>中的任何日期值很可能大于零，因此该行将被删除。

根据以下评论，请尝试此更新的代码：

  Sub Remove_Unecessary_Data_1（）
 
 Dim ALLCs As Worksheet 
 Dim DS As Worksheet 
 Dim Dailystring As String 
 Dim Blank As String 
 Dim finaldate As Date 
 
 Dailystring = - 每日
空白=
 
'----------------------------请注意行：
 finaldate ='something！今天？现在？一些用户指定日期？你不告诉我们
'---------------------------- 
 
设置DS =表格（数据摘要）
设置ALLCs =表（Asset LLC（输入））
 
'ALLCs.Select'以消除未来模糊的可能性，使变化低于
对于y = 1到40 
如果InStr（1，AllCs.Cells（13，y），执行时间戳）然后
'注意，你分配到左边的等于从右边的符号（=）
 finaldate = AllCs.Cells（50，y）
结束If 
下一个
 
'ALLCs.Select 
 For u = 1到40 
如果InStr（1，AllCs.Cells（13，u），开始日期）然后
对于p = 2到69584 
如果AllCs.Cells（p + 14，u）>然后
 AllCs.Cells（p + 14，u）.EntireRow.Delete 
 End If 
 Next 
 End If 
 Next 
  

Currently I am trying to declare a variable as a date I pulled from a worksheet in excel, then use that declared date as a benchmark so I can delete everything greater than it. I think my code is solid for this task but the issue I keep having is when I look at what my date is declared as instead of being 1/12/2015 as it should it comes up at 12:00:00am and it replaces the contents of the cell in which I pulled the date from. Here is my code so far...

Sub Remove_Unecessary_Data_1()

Dim ALLCs As Worksheet
Dim DS As Worksheet
Dim Dailystring As String
Dim Blank As String
Dim finaldate As Date


Dailystring = " - Daily"
Blank = ""

Set DS = Sheets("Data Summary")
Set ALLCs = Sheets("Asset LLC (Input)")



    ALLCs.Select
        For y = 1 To 40
           If InStr(1, Allcs.Cells(13, y), "Timestamp of Execution") Then
                finaldate = Allcs.Cells(50, y)
            End If
        Next

    ALLCs.Select
        For u = 1 To 40
            If InStr(1, Allcs.Cells(13, u), "Start Date") Then
                For p = 2 To 69584
                If allcs.Cells(p + 14, u) > finaldate Then
                allcs.Cells(p + 14, u).EntireRow.Delete
                End If
                Next
            End If
        Next

I think the main issue herein lies that finaldate is being transforming into that odd time. Any ideas?

Also for reference the cell its pulling from 50,y reads

1/12/2015.

EDIT: I updated my code to reflect @freemans commented suggestion on the placement of my finaldate when trying to assign it a value. I had it reversed.

My new problem is the second part of my code doesn't accurately remove all of the dates necessary. For instance I need all of the dates about 1/12/15 removed, my program still does not do this, thought I don't see the error in my code

        ALLCs.Select
        For u = 1 To 40
            If InStr(1, Allcs.Cells(13, u), "Start Date") Then
                For p = 2 To 69584
                If allcs.Cells(p + 14, u) > finaldate Then
                allcs.Cells(p + 14, u).EntireRow.Delete
                End If
                Next
            End If
        Next

My issue lies here, why would 1/13/15 not be deleted if finaldate is declared as 1/12/15.

解决方案

You declare

Dim finaldate As Date

and you use it here

Cells(50, y) = finaldate

and here

            If Cells(p + 14, u) > finaldate Then
            Cells(p + 14, u).EntireRow.Delete
            End If

But you've never assigned a value to finaldate, therefore you're filling in a date/time of 0. Any date value in Cells([+14,u) is highly likely to be greater than zero, therefore the row will be deleted.

Based on the comments below, try this updated code:

Sub Remove_Unecessary_Data_1()

Dim ALLCs As Worksheet
Dim DS As Worksheet
Dim Dailystring As String
Dim Blank As String
Dim finaldate As Date

Dailystring = " - Daily"
Blank = ""

'----------------------------Note this line:
finaldate = 'something! Today? Now? some user specified date? you don't tell us
'----------------------------

Set DS = Sheets("Data Summary")
Set ALLCs = Sheets("Asset LLC (Input)")

'ALLCs.Select     'to remove the possibility of future ambiguity, make the change below
For y = 1 To 40
    If InStr(1, AllCs.Cells(13, y), "Timestamp of Execution") Then
'note, you assign TO the left side of the equal sign (=) FROM the right side
        finaldate = AllCs.Cells(50, y)
    End If
Next

'ALLCs.Select
For u = 1 To 40
    If InStr(1, AllCs.Cells(13, u), "Start Date") Then
        For p = 2 To 69584
          If AllCs.Cells(p + 14, u) > finaldate Then
            AllCs.Cells(p + 14, u).EntireRow.Delete
          End If
        Next
    End If
Next

这篇关于声明一个变量，其中显示了一个在表格中发现的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！