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