CODEWITHSUNDEEP
excelvbaexcel-formula
user9758877
user9758877

Reputation:

function DATE() type mismatch

I can't make this work

Dim StrtD As Long, EndD As Long
Dim StartDate As Date, EndDate As Date
Dim myVar As Variant
Dim stringaAppoggio As String

With ThisWorkbook.Sheets("Settimana")
    StartDate = .TextBox1.Value
    EndDate = .TextBox2.Value
End With

StrtD = Month(StartDate)
EndD = StrtD + DateDiff("m", StartDate, EndDate)
yearData = year(StartDate)
arr4 = Application.Transpose(.Evaluate("TEXT(DATE(yearData,ROW(" & StrtD & ":" & EndD & "),1), ""[$-0410]mmmm yyyy"")"))

For Each myVar In arr4

stringaAppoggio = myVar

StartDate is "01/01/2020"
EndDate is "01/10/2020"

The error is stringaAppoggio = myVar

type mismatch

I think the error is yearData in Application.Transpose because if I put 2020 it works!

Thank you

Upvotes: 0

Views: 97

Answers (2)

JvdV
JvdV

Reputation: 75840

There are some things going wrong for you here:

  • Your code should not even compile in the first place, because (with the information you have shown) Application.Transpose(.Evaluate holds a unqualified .Evaluate method.
  • As I told you yesterday, Debug.Print is a great way of checking if what you intended to write, actually gets written in the .Evaluate. Currently you are using yeardata wrong within the formula. It's a variable holding a string value! Don't get confused with thinking that writing this word within another string automatically picks up you meant to use the variable.
  • To TRANSPOSE values from a worksheet into a 1D-array, you can do so directly through your formula.
  • I just assume your code sample above isn't complete and includes a Next below stringaAppoggio = myVar

Considering the above, I think your code can be rewritten to something like:

arr4 = Evaluate("TRANSPOSE(TEXT(DATE(" & yearData & ",ROW(" & StrtD & ":" & EndD & "),1), ""[$-0410]mmmm yyyy""))")

Upvotes: 0

L42
L42

Reputation: 19727

yeardata variable is not evaluated passed correctly to your string formula. You need to concatenate it directly into your formula string like what you did in StrtD and EndD variables. Try:

arr4 = Application.Transpose(.Evaluate("TEXT(DATE(" & yearData & _
       ",ROW(" & StrtD & ":" & EndD & "),1), ""[$-0410]mmmm yyyy"")"))

Upvotes: 1

Related Questions