VBA does not recognize values

Asked

Viewed 805 times

0

I am trying to get data type values from a certain excel cell, however, my macro does not recognize the values of the cell, even if it is filled (date). Remembering that there are three Heets.

  With Worksheets("TEMP") 
    ActiveWorkbook.Sheets("TEMP").Activate
    minDate = WorksheetFunction.Min(Worksheets("TEMP").Range("F2:F65000"))
    maxDate = WorksheetFunction.Max(.Range(.Range("F2"), .Range("F65000")))
    numMonths = 1 + DateDiff("m", minDate, maxDate)
    If (numMonths < 3) Then
        countMonths = 3
    Else
        countMonths = numMonths
    End If
End With

The minDate and maxDate return as 0, only when I manually click on the cell and hit Enter or click on another cell the macro recognizes, only that cell.

How to make the macro recognize that value?

excel vba
  • 1

    Their .Range in the fourth row are wrong. You should do equal to the top function. The minDate works properly for me.

  • Actually both work when I click on some cell manually and hit enter or click on another cell. The problem is the "inactive cell".

1 answer

1


Public Sub Teste()  
  Dim MinDate As Date
  Dim MaxDate As Date  
  MinDate = WorksheetFunction.Min(Sheets("Plan1").Range("A1:A100"))
  MaxDate = WorksheetFunction.Max(Sheets("Plan1").Range("A1:A100"))  
  Debug.Print MinDate
  Debug.Print MaxDate  
End Sub

It worked perfectly, make sure the data was entered as date in the intenvalo, if not, make a conversion.

inserir a descrição da imagem aqui

inserir a descrição da imagem aqui

Browser other questions tagged excel vba

You are not signed in. Login or sign up in order to post.