is there something wrong with this post? .find prob

[pbarreda77]

PLease let me know if my question is vague or if i am asking a silly question i have gone through hundres of posts trying to solve this problem.

I get an error in the .find statment the program doesn't seem to recognize the value of the variable couponDate when used in the find statement

Below is the code

sub

Dim couponcash as integer
Dim couponDate as Date
Dim z as integer

z = 2

CouponCash = Cells(3,z).value
CouponDate = Cells(4,z).value

With Worksheets(1).Range("a1:a500")
Set c = .find(CouponDate, Lookin:=xlvalues)
If not c is nothing then
firstaddress = c.address
do
c.offset(0,1).value = couponcash
set c =.findnext(c)
Loop while not c is nothing and c.address<> firstaddress
end if
end with
end sub
This message was edited by pbarreda77 on 2002-08-24 08:55
This message was edited by pbarreda77 on 2002-08-24 09:32
This message was edited by pbarreda77 on 2002-08-24 13:50

 

[Ivan F Moala]

What error are you getting

Oh BTW did you type this in ? or Copy and paste as this;

c.offset(0,1).vlaue = couponcash

it should read

c.offset(0,1).Value = couponcash

 

[pbarreda77]

Hello Ivan thank you for your reply, sorry c.offset(0,1).Value = couponCash was correctly typed in the wookbook.

couponDate is a date variable

when I use the following code

Set c = .Find(couponDate, LookIn:= xlValues)

the entry couponDate seems to fail to trigger the .Find, nothing is placed into the offset target location if i where to place the number "2" instead of "couponDate" the offset target receives the value couponCash where there is a "2" in the dates in Column 1.

I know that the variable couponDate is valid as it displays in a message box I have placed in the code.

Could it be that I need to format the date value in some way?

Thanks again.


Pedro

 

[Ivan F Moala]

This worked for me
Xl2000 / win98

<PRE>
<FONT color=blue>Sub </FONT>Tester1()

<FONT color=blue>Dim </FONT>couponcash <FONT color=blue>As</FONT><FONT color=blue> Integer</FONT>

<FONT color=blue>Dim </FONT>couponDate

<FONT color=blue>Dim </FONT>z <FONT color=blue>As</FONT><FONT color=blue> Integer</FONT>



z = 2



couponcash = Cells(3, z).Value

couponDate = Format((Cells(4, z).Value), Cells(4, z).NumberFormat)



<FONT color=blue>With </FONT>Sheet1.<FONT color=blue>Range</FONT>("A1:A500")

<FONT color=#339966> 'Find
</FONT>
<FONT color=blue>Set </FONT>c = .Find(couponDate, LookIn:=xlValues)

<FONT color=blue>If </FONT>Not c Is<FONT color=blue> Nothing</FONT> Then

firstaddress = c.Address

Do

c.Offset(0, 1).Value = couponcash

<FONT color=blue>Set </FONT>c = .Find<FONT color=blue>Next</FONT>(c)

<FONT color=blue>Loop</FONT> While Not c Is<FONT color=blue> Nothing</FONT> And c.Address <> firstaddress

<FONT color=blue>End If</FONT>

<FONT color=blue>End With</FONT>



<FONT color=blue>End Sub</FONT>
</PRE>

 

[pbarreda77]

Ivan wowowowowowow yes it does work, was it a format issue with the way the date value was being read by vba? I noticed you changed couponDate for a Date variable to a variant and i take it the Format function put the coupon date value into the correct format so that it could be understood by vba.


Thank you very very much.

Pedro