VBA将日期格式从D-M-Y更改为M-D-Y [英] VBA changes date format from D-M-Y to M-D-Y

问题描述

将一个字符串与一个工作表中的值进行比较时，我有一个问题。
在csv文件中，我获得了一个值为8-9-2016的变量 BookingDate 。我想循环通过Excel工作表来检查是否与该值匹配。我发现当我想在VBA中使用它们时，我从表中读取的值被更改。假设我设置一个变量 BookingDate2 ，并给它一个这样的值：

  LR = ThisWorkbook.Sheets（Sheetname）。Range（A& Rows.Count）.End（xlUp）.Row 
 For i = 2 To LR 
 BookingDate2 = ThisWorkbook.Sheets （Boekingen AMS-IAD）。细胞（i，1）.Value 
  

然后我想检查是否找到这样的匹配：

 如果（BookingDate = BookingDate2）然后
'做某事
  

现在我遇到的问题是 BookingDate 有价值8-9-2016和 BookingDate2 的值应为8-9-2016，但更改为9-8-2016。如何解决这个问题？

解决方案

我已经修复了！

我忘了把变量减到日期。
它是这样的：

  Dim BookingDate As String 
 Dim BookingDate2 As String 
  

但它必须是：

  Dim BookingDate As Date 
 Dim BookingDate2 As Date 
  

我

I have a problem when comparing a string to values from a sheet. Out of a csv file, I obtained a variable BookingDate with value "8-9-2016". I want to loop through an Excel sheet to check if there is a match with that value. I have discovered that the values I read from the sheet are changed when I want to use them in VBA. Let's say I set a variable BookingDate2 and give it a value like this:

LR = ThisWorkbook.Sheets("Sheetname").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    BookingDate2 = ThisWorkbook.Sheets("Boekingen AMS-IAD").Cells(i, 1).Value

Then I want to check if it found a match like this:

If (BookingDate = BookingDate2) Then
    'Do something

The problem I have right now is that BookingDate has value "8-9-2016" and the value of BookingDate2 is supposed to be "8-9-2016", but is changed to "9-8-2016". How can I fix this?

解决方案

I fixed it!

I forgot to Dim the variables to Date. It was like this:

Dim BookingDate As String
Dim BookingDate2 As String

But it had to be:

Dim BookingDate As Date
Dim BookingDate2 As Date

That did the trick for me.

这篇关于VBA将日期格式从D-M-Y更改为M-D-Y的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！