N/A # and error 2015

Hello to all,

I have this while with this condition:

dim nav as variant

roomvalue= CVErr(xlErrNA)


While IsError(roomvalue) 'while nav is error "this formula = n/A# ,
I do the formula

StrFormula2 = "Client(""CLIENT"", " & name & ", ""ROOM"", """ &
currentdate & """)"
roomvalue= Evaluate(StrFormula2)
currentdate = getpreviousday(currentdate)

Wend

But I have an error 2015 and I do not understand why? roomvalue is a
float number and I declared it as variant; could it be a problem

ina

It is OK to have a variant to hold a float, so I don't think that is it. But
one thing is that in your sample at least you seem to have changed the
variable nav to roomvalue, so roomvalue was never declared with a type. I
think you mean to have the Dim statement to be Dim roomvalue as variant.

However, that may not be the problem - it could be in your functions, either
Client() or getpreviousday(). Also, you never initialize currentdate so the
first time through your call to Client that is evaluated would be
Client("CLIENT", , "ROOM", "") - is this what you want, and will Client()
accept a blank string as the date here?

Hard to say any more without knowing which line of code is giving the error
and also what is the code behind your functions.
--
- K Dales


"ina" wrote:

> Hello to all,
>
> I have this while with this condition:
>
> dim nav as variant
>
> roomvalue= CVErr(xlErrNA)
>
>
> While IsError(roomvalue) 'while nav is error "this formula = n/A# ,
> I do the formula
>
> StrFormula2 = "Client(""CLIENT"", " & name & ", ""ROOM"", """ &
> currentdate & """)"
> roomvalue= Evaluate(StrFormula2)
> currentdate = getpreviousday(currentdate)
>
> Wend
>
> But I have an error 2015 and I do not understand why? roomvalue is a
> float number and I declared it as variant; could it be a problem
>
> ina
>
>

I don't see why you are using evaluate. Evaluate is used to with formulas
that your would place in a worksheet - see it as a virtual cell. If you have
a Function named client, then just pass it the arguments and have it return
the results.

Dim roomvalue as Variant
roomvalue= CVErr(xlErrNA)
While IsError(roomvalue)
roomvalue = Client("CLIENT", name , "ROOM",
Format(currentDate,"mm/dd/yyyy"))
currentdate = getpreviousday(currentdate)
Wend

--
Regards,
Tom Ogilvy



--
Regards,
Tom Ogilvy


"ina" wrote:

> Hello to all,
>
> I have this while with this condition:
>
> dim nav as variant
>
> roomvalue= CVErr(xlErrNA)
>
>
> While IsError(roomvalue) 'while nav is error "this formula = n/A# ,
> I do the formula
>
> StrFormula2 = "Client(""CLIENT"", " & name & ", ""ROOM"", """ &
> currentdate & """)"
> roomvalue= Evaluate(StrFormula2)
> currentdate = getpreviousday(currentdate)
>
> Wend
>
> But I have an error 2015 and I do not understand why? roomvalue is a
> float number and I declared it as variant; could it be a problem
>
> ina
>
>

Thanks guys

This is my code; this roomvalue does not work


Function getroom(ByVal client As String) As Variant


Dim beginofmonth As Date
Dim endofmonth As Date
Dim startdate As Date
Dim currentdate As Date
Dim todaydate As Date
Dim i As Integer
Dim r As Integer
Dim size As Integer
Dim roomvalue As Variant
Dim var(1 To 1000, 1 To 6) As Variant
Dim strFormula As String
Dim StrFormula2 As String
Dim rng_adrss As String
Dim StrFormula3 As String


todaydate = Date



strFormula = "getfirstarrival(""ROOM"", """ & client & """)"
startdate = Evaluate(strFormula)

endofmonth = getendofmonth(startdate)

Debug.Print startdate

i = 0
r = 1


currentdate = endofmonth

Debug.Print currentdate; endofmonth

While DateDiff("m", currentdate, todaydate) > 0



beginofmonth = getbeginofmonth(currentdate)



roomvalue = Evaluate("na()")


While IsError(roomvalue)


StrFormula2 = "Client(""CLIENT"", " & Client& ", ""ROOM"", """
& currentdate & """)"
roomvalue = Evaluate(StrFormula2)
currentdate = getpreviousday(currentdate)
Debug.Print nav

Wend


currentdate = getnextday(currentdate)


If DateDiff("d", beginofmonth, currentdate) < 0 Then

roomvalue = Evaluate("na()")
currentdate = beginofmonth

End If








var(r, 1) = client
var(r, 2) = currentdate

var(r, 3) = roomvalue
var(r, 4) = "ROOM"

var(r, 5) = "vacation"

var(r, 6) = "COMMENTS"



r = r + 1




currentdate = getnextendofmonth(currentdate)



Wend



size = 6

rng_adrss = selectRange(size, r)

Debug.Print rng_adrss

Range(rng_adrss) = var

getroom = var

End Function


Function getendofmonth(ByVal currentdate As Date) As Date

getendofmonth = DateSerial(Year(currentdate), Month(currentdate) +
1, 0)


End Function



Function getbeginofmonth(ByVal currentdate As Date) As Date

getbeginofmonth = DateSerial(Year(currentdate), Month(currentdate),
1)


End Function



Function getnextendofmonth(ByVal currentdate As Date) As Date

currentdate = DateSerial(Year(currentdate), Month(currentdate) + 1,
1)

getnextendofmonth = getendofmonth(currentdate)

End Function



Function getpreviousday(ByVal currentdate As Date) As Date

getpreviousday = DateSerial(Year(currentdate), Month(currentdate),
Day(currentdate) - 1)


End Function



Function getnextday(ByVal currentdate As Date) As Date

getnextday = DateSerial(Year(currentdate), Month(currentdate),
Day(currentdate) + 1)


End Function


Function ColRef2ColNo(ColRef As String) As String
ColRef2ColNo = 0
On Error Resume Next
ColRef2ColNo = Range(ColRef & "1").Column
End Function

Function ColNo2ColRef(ColNo As Integer) As String
If ColNo < 1 Or ColNo > 256 Then
ColNo2ColRef = "#VALUE!"
Exit Function
End If
ColNo2ColRef = Cells(1, ColNo).Address(True, False, xlA1)
ColNo2ColRef = Left(ColNo2ColRef, InStr(1, ColNo2ColRef, "$") - 1)
End Function

Function selectRange(ByVal size As Integer, row As Integer) As String
Dim i As Integer
Dim r As Integer
Dim arraysize As Integer
Dim j As Integer
Dim h As Integer
Dim rowcell As String
Dim newcolumn As String
Dim addr1 As String
Dim rng As Range

arraysize = size
r = row
Debug.Print arraysize
Dim addr As String
addr = ActiveCell.Address


j = ColRef2ColNo(addr)
h = j + arraysize - 1
newcolumn = ColNo2ColRef(h)

Debug.Print newcolumn

rowcell = ActiveCell.row
arraysize = (CInt(rowcell)) + r - 2


Set rng = Range(ActiveCell, newcolumn & arraysize)

Debug.Print addr1
addr1 = rng.Address

Debug.Print addr1

selectRange = addr1

End Function

---Ina



I have a problem with only the roomvalue loop the others functions work
fine; I do not know why?

Since you haven't shown the function "CLIENT", there is little substantive
addition to your original post.

based on that, the advice has not changed.

--
Regards,
Tom Ogilvy


"ina" wrote:

> Thanks guys
>
> This is my code; this roomvalue does not work
>
>
> Function getroom(ByVal client As String) As Variant
>
>
> Dim beginofmonth As Date
> Dim endofmonth As Date
> Dim startdate As Date
> Dim currentdate As Date
> Dim todaydate As Date
> Dim i As Integer
> Dim r As Integer
> Dim size As Integer
> Dim roomvalue As Variant
> Dim var(1 To 1000, 1 To 6) As Variant
> Dim strFormula As String
> Dim StrFormula2 As String
> Dim rng_adrss As String
> Dim StrFormula3 As String
>
>
> todaydate = Date
>
>
>
> strFormula = "getfirstarrival(""ROOM"", """ & client & """)"
> startdate = Evaluate(strFormula)
>
> endofmonth = getendofmonth(startdate)
>
> Debug.Print startdate
>
> i = 0
> r = 1
>
>
> currentdate = endofmonth
>
> Debug.Print currentdate; endofmonth
>
> While DateDiff("m", currentdate, todaydate) > 0
>
>
>
> beginofmonth = getbeginofmonth(currentdate)
>
>
>
> roomvalue = Evaluate("na()")
>
>
> While IsError(roomvalue)
>
>
> StrFormula2 = "Client(""CLIENT"", " & Client& ", ""ROOM"", """
> & currentdate & """)"
> roomvalue = Evaluate(StrFormula2)
> currentdate = getpreviousday(currentdate)
> Debug.Print nav
>
> Wend
>
>
> currentdate = getnextday(currentdate)
>
>
> If DateDiff("d", beginofmonth, currentdate) < 0 Then
>
> roomvalue = Evaluate("na()")
> currentdate = beginofmonth
>
> End If
>
>
>
>
>
>
>
>
> var(r, 1) = client
> var(r, 2) = currentdate
>
> var(r, 3) = roomvalue
> var(r, 4) = "ROOM"
>
> var(r, 5) = "vacation"
>
> var(r, 6) = "COMMENTS"
>
>
>
> r = r + 1
>
>
>
>
> currentdate = getnextendofmonth(currentdate)
>
>
>
> Wend
>
>
>
> size = 6
>
> rng_adrss = selectRange(size, r)
>
> Debug.Print rng_adrss
>
> Range(rng_adrss) = var
>
> getroom = var
>
> End Function
>
>
> Function getendofmonth(ByVal currentdate As Date) As Date
>
> getendofmonth = DateSerial(Year(currentdate), Month(currentdate) +
> 1, 0)
>
>
> End Function
>
>
>
> Function getbeginofmonth(ByVal currentdate As Date) As Date
>
> getbeginofmonth = DateSerial(Year(currentdate), Month(currentdate),
> 1)
>
>
> End Function
>
>
>
> Function getnextendofmonth(ByVal currentdate As Date) As Date
>
> currentdate = DateSerial(Year(currentdate), Month(currentdate) + 1,
> 1)
>
> getnextendofmonth = getendofmonth(currentdate)
>
> End Function
>
>
>
> Function getpreviousday(ByVal currentdate As Date) As Date
>
> getpreviousday = DateSerial(Year(currentdate), Month(currentdate),
> Day(currentdate) - 1)
>
>
> End Function
>
>
>
> Function getnextday(ByVal currentdate As Date) As Date
>
> getnextday = DateSerial(Year(currentdate), Month(currentdate),
> Day(currentdate) + 1)
>
>
> End Function
>
>
> Function ColRef2ColNo(ColRef As String) As String
> ColRef2ColNo = 0
> On Error Resume Next
> ColRef2ColNo = Range(ColRef & "1").Column
> End Function
>
> Function ColNo2ColRef(ColNo As Integer) As String
> If ColNo < 1 Or ColNo > 256 Then
> ColNo2ColRef = "#VALUE!"
> Exit Function
> End If
> ColNo2ColRef = Cells(1, ColNo).Address(True, False, xlA1)
> ColNo2ColRef = Left(ColNo2ColRef, InStr(1, ColNo2ColRef, "$") - 1)
> End Function
>
> Function selectRange(ByVal size As Integer, row As Integer) As String
> Dim i As Integer
> Dim r As Integer
> Dim arraysize As Integer
> Dim j As Integer
> Dim h As Integer
> Dim rowcell As String
> Dim newcolumn As String
> Dim addr1 As String
> Dim rng As Range
>
> arraysize = size
> r = row
> Debug.Print arraysize
> Dim addr As String
> addr = ActiveCell.Address
>
>
> j = ColRef2ColNo(addr)
> h = j + arraysize - 1
> newcolumn = ColNo2ColRef(h)
>
> Debug.Print newcolumn
>
> rowcell = ActiveCell.row
> arraysize = (CInt(rowcell)) + r - 2
>
>
> Set rng = Range(ActiveCell, newcolumn & arraysize)
>
> Debug.Print addr1
> addr1 = rng.Address
>
> Debug.Print addr1
>
> selectRange = addr1
>
> End Function
>
> ---Ina
>
>
>
> I have a problem with only the roomvalue loop the others functions work
> fine; I do not know why?
>
>

ok thanks, unfornately I do not have the access to this function client
because it is an addins provided to me I can not see the code, it is
protected

ina

Ok, Thanks Unfornately i cannot have the access to this function client
because it is an addins provided to me (it is come from a client
product) and I cannot see the code because it is protected

then you will have to ask the author why it returns a 2015 error or for what
reasons.

a 2015 is equivalent of #VALUE! in the worksheet.

? cvErr(xlerrValue)
Error 2015

I would get it working in a worksheet using identical arguments. Then make
sure that is what you are passing to it in the Evaluate command with the VBA.

--
Regards,
Tom Ogilvy



"ina" wrote:

> Ok, Thanks Unfornately i cannot have the access to this function client
> because it is an addins provided to me (it is come from a client
> product) and I cannot see the code because it is protected
>
>

Yes I will do ... I do not know why I have this error... I will ask
them.

Ina