.Find method problem 1

[claudehenri]

Hi

my problem is that Temp is always empty,even though all the cells in RadiusRange have been filled with numbers or text i.e. "6". What is wrong with the code, I've been racking my brain to try figure it out but no luck. I just upgraded to Office 2003, and I am sure that the code below worked. Any help greatly appreciated.

Claude-Henri

Set RadiusRange = .Range(.Cells(3, 1), .Cells(3 + TotNoRad, 1))
With RadiusRange
For j = 0 To TotNoRad
Set Temp = .Find(.Cells(j + 1, 1), LookIn:=xlValues, LookAt:=xlWhole)
If Not Temp Is Nothing Then
Addr = Temp.Address
Do
Set Temp = .FindNext(Temp)
If Temp.Address <> Addr Then Temp.Value = ""
Loop While Not Temp Is Nothing And Temp.Address <> Addr
End If
Next j

 

[xlbo]

Your code reads to me that you are trying to find a value FROM RadiusRange IN RadiusRange

If it's found you then do a loop which then sets Temp to nothing if it finds another instance of the same value...I'm confused - what are you trying to achieve here ?



Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[claudehenri]

what i'm trying to do is eliminate the second, third etc instance of a number in a list. I didn't make it clear, but what is happening is that it Temp is always "Nothing" even though all the cells are filled.

 

[claudehenri]

Can any one help me here?

 

[xlbo]

Temp is always nothing" - don't see how that can be from your code - it is guarenteed to be SOMETHING

This line
If Temp.Address <> Addr Then Temp.Value = ""
however DOES set TEMP to "" and keeps on looping so eventually, it sets everything to ""

Have you stepped through the code using F8 with a WATCH on Temp ??

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[claudehenri]

That is my problem I cant see how it can be nothing.

This line
If Temp.Address <> Addr Then Temp.Value = ""
is inside the loop all it is doing is checking that the code hasn't found the first instance of the value i am searching for. if it isn't the first instance then it deletes this instance. So i am guaranteed to only have the value show once. And temp is set to a new cell at the start of the for loop to a new cell.

Yes i've run through with F8, even add extra code so that i can see which cell is selected etc.

I have had this code working before, but i just doesn't seem to work here in excel 2003. I have no ideas. any would be helpful

Claude-Henri

 

[TonyJollans]

Hi Claude-Henri,

I can't make this nothing - can you post full code and relevant cell values.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[claudehenri]

Can anyone help with this?

 

[Loomah]

Claude-Henri
People are trying to help but if someone asks if you could "post full code and relevant cell values" it's because a better picture of your requirements is needed.

If you post the code and values as requested I'm sure both Tony & xlbo will be better placed to help you!

Happy Friday!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[claudehenri]

OK here is the entire code for the sub

I'll explain it a bit. there is a hole heap(63) of textboxes and i've put all of them into TBCol, except the first 3 which are FirstRadius, LastRadius and Increment. What I want is for any special radii that the user wants to imput manually to be added to the the set of radii that starts at FirstRadius and finishes at LastRadius with steps of Increment. So what i am doing is printing all the values to excel then removing any duplicated values (this where my problem is) then sorting and reading back in to the form. If there are more than 60 radii the extras go into a lable. Hope this is what you need to help me, I thought I gave you the code that was relevant to my problem.

Dim Temp, j, Item, NoSpecRad, SecondRad, MinRad, MaxRad, Increment, TotNoRad, Addr
Dim RadiusRange As Object
MinRad = TextBox1
MaxRad = CSng(TextBox2)
Increment = TextBox3
If Not (IsNumeric(Increment)) Then Increment = 2#
With Worksheets("Wave 1")
j = -1
For Each Item In TBCol
j = j + 1
If Item <> 0 And Item <> "" Then
.Cells(3 + j, 1) = Item.Value 'can ommit the .value when DataOption1:=xlSortTextAsNumbers is added to sort command (for office xp only)
Else
Exit For
End If
Next Item
If UBound(Radius, 2) > 59 Then
For j = 60 To UBound(Radius, 2)
.Cells(3 + j, 1) = Round(CDbl(Radius(0, j)), 3) '"Round(CDbl( ), )" is to ensure that the
'cell that is printed to does not contain a double (even though radius(,) is declared a
'single for some reason if the decimal value is not *.5 then a double is entered into
'the excel cell
Next j
End If
NoSpecRad = j
For j = 1 To Application.WorksheetFunction.Min(NoSpecRad, 60)
TBCol(j) = ""
Next j
j = 0
.Cells(3 + j + NoSpecRad, 1) = MinRad
SecondRad = Int(MinRad / Increment)
SecondRad = SecondRad * Increment
Do
j = j + 1
Temp = SecondRad + Increment * j
If Temp > MaxRad Then Temp = Round(CDbl(MaxRad), 1)
.Cells(3 + j + NoSpecRad, 1) = Temp
Loop Until Temp = MaxRad
TotNoRad = j + NoSpecRad
Set RadiusRange = .Range(.Cells(3, 1), .Cells(3 + TotNoRad, 1))
With RadiusRange
For j = 0 To TotNoRad
Set Temp = .Find(.Cells(j + 1, 1), LookIn:=xlValues, LookAt:=xlWhole)
If Not Temp Is Nothing Then
Addr = Temp.Address
Do
Set Temp = .FindNext(Temp)
If Temp.Address <> Addr Then Temp.Value = ""
Loop While Not Temp Is Nothing And Temp.Address <> Addr
End If
Next j
.Sort Key1:=.Range("A3") ', DataOption1:=xlSortTextAsNumbers'can use this extra only in office xp, allows the omission of .value
End With
Label76 = ""
j = -1
For Each Item In RadiusRange
j = j + 1
Temp = .Cells(3 + j, 1)
If Temp = "" Then
Exit For
End If
If j < 60 Then
TBCol(j + 1) = Temp
Else
ReDim Preserve Radius(0, j)
Radius(0, j) = Temp
If j = 60 Then
Label76 = "R" & j + 1 & " = " & Temp & "m"
Else
Label76 = Label76 & ", R" & j + 1 & " = " & Temp & "m"
End If
End If
Next Item
End With
RadiusRange = ""
CommandButton1.SetFocus

End Sub

 

[TonyJollans]

Hi Claude-Henri,

I was hoping that I might find something if I saw more code but I'm afraid I haven't. It seems impossible to get Nothing as a result of your Find, and I can't manage it despite various tests. So I'm sorry to say I cannot provide an answer.

I would be happy to take a look at your actual workbook if it's not confidential - (my handle here) at VBAExpress dot com.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[claudehenri]

Hi Tony

Just trying to register for VBAExpress but my initialize email is taking a while to get to me, I'll send you the whole form canibalized from my program so you don't get all the crap, once I can log on.

Claude-Henri

 

[TonyJollans]

Hi Claude-Henri,

You're welcome at VBA Express but you don't need to register to send me an e-mail - just use the address like any other - TonyJollans at etc.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[claudehenri]

Tony

Just in the process of seperating the form from the program, and ran the form to make sure I initialized everything correctly. Guess What! .Find doesn't return "Nothing". Would this indicate any mistakes I've made else where?

C-H.

 

[TonyJollans]

Hi Claude-Henri,

The short answer is yes!!

I have received your e-mail and will take a look, but could you say what your symptoms actually are.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[claudehenri]

Symptoms? exactly what do you mean. The only thing I've noticed is that .find always returned nothing once i started using xl 2003. I did have this condition "If Temp <> Empty Then" after the line "Set Temp=.Find( )" initialy which returned the error. Once removing this codition and checking the rest of the code by steping through if found that .find always returned Nothing.

What I might do is give you my user interface Its about 24 forms, and the decisions i'm making, to see if that helps you find the problem.

 

[xlbo]

claudehenri - please describe to Tony what steps you take when you say that find gets set to nothing - that'd be what I infer as symptoms

btw - as this has gone offline, please let Tony make the offers as to whether he WANTS to receive any more info as he is the one GIVING the help here...do not assume you can just send it to him

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[TonyJollans]

Hi Claude-Henri,

Thanks for the full interface. I followed your instructions (because it's far too complex for me to try and work out for myself ) and Temp does indeed get set to Nothing as you describe.

What appears to be happening is that the Find is looking at the cell Text (i.e. the string values "6.0", etc) whilst the default property of the Cells object is the Value (i.e. numeric 6). When I changed the Find to:

[blue]Set Temp = .Find(.Cells(j + 1, 1)[red].Text[/red], LookIn:=xlValues, LookAt:=xlWhole)[/blue]

it appeared to work. Also, changing it to

[blue]Set Temp = .Find(.Cells(j + 1, 1), LookIn:=[red]xlFormulas[/red], LookAt:=xlWhole)[/blue]

appears to work.

As I write this I recall a similar problem when cells are displaying hash marks because the contents won't fit in the space available, but I can't remember the details. If I can find my notes I will post back, but for now try changing it to one of the above and see if it helps.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[claudehenri]

Hi Tony

Thanks a heap for the help. Didn't realize the cell format would make a difference to the .find any way I should be able to go full steam ahead now.

Yeh I know that the interface is fairly chaotic. Ive just been through the main calculation code and fixed all that up from 12K to 4K and then to 2K lines. All I have to do now is fix the interface.

Thanks again.
Claude-Henri