GOTO or FIND NEXT function - any suggestions please?

[cheekyrich]

Hi,

I have a range of data, lets say A1:D50. I am concerned only with column C. In that column, I want my VBA macro to go to (in turn) the next cell with the number '0330' in it (assuming all cells have different numbers). I will then perform a few bits of macro around that cell before my search will continue on to find the next '0330' in column C.

I know this is a simple one but have searched the forum and am getting all sorts except for the answer I need!

Thanks so much for your help in advance. Reeally appreciated,

Rich

 

[GlennUK]

But the reason why I am here is cos I don't know what I'm doing wrong - I don't know how to re-write it. Am not as good with VBA as you clearly are. Can ayone fix this for me please? Thank you,

Rich

... I said ...

You are using ActiveCell inside your loop instead of r.

... which I thought was enough.

here is part of it

Do 
r.Offset(0, -5).Value = "'1001" 
r.Offset(0, -4).Value = "'1000" 
r.Offset(0, -3).Value = "'002" 
r.Offset(0, -2).Value= "'01"

I don't know what "i" is in

If Trim(Sheet1.Cells(i, "S").Text) = "te" Then

so don't want to mess with that part of the code.

 

[cheekyrich]

Great, thanks Glenn.

I have changed that Activecell. to r. now. I can see why that would help. The "i" by the way, was the relevant row that r is on but I have changed that now too, so it reads as follows...

But **** this code!!! It is still not finding "xyz". I have a column of numbers and about 30 rows down I have inserted xyz to test it. Won't find it at all though!!!




Dim r As Range, ff As String
Set r = Range("G61:G1000").Find("xyz", Range("G1000"), , xlPart)
If Not r Is Nothing Then
ff = r.Address
Do
r.Offset(0, -5).Select
r.FormulaR1C1 = "'1001"
r.Offset(0, -4).Select
r.FormulaR1C1 = "'1000"
r.Offset(0, -3).Select
r.FormulaR1C1 = "'002"
r.Offset(0, -2).Select
r.FormulaR1C1 = "'01"
If Trim(r.Offset(0, 12).Text) = "te" Then
r.Offset(0, -1).Select
r.FormulaR1C1 = "'1100"
End If
Set r = Range("G61:G1000").FindNext(r)
Loop Until ff = r.Address Or r Is Nothing
End If

 

[GlennUK]

that is NOT the code I posted!

 

[cheekyrich]

Yes sorry i already realised. It reads...

But it still doesn't find xyz





Dim r As Range, ff As String
Set r = Range("G61:G1000").Find("xyz", Range("G1000"), , xlPart)
If Not r Is Nothing Then
ff = r.Address
Do
r.Offset(0, -5).Value = "'1001"
r.Offset(0, -4).Value = "'1000"
r.Offset(0, -3).Value = "'002"
r.Offset(0, -2).Value = "'01"
If Trim(r.Offset(0, 12).Text) = "te" Then
r.Offset(0, -1).Value = "'1100"
End If
Set r = Range("G61:G1000").FindNext(r)
Loop Until ff = r.Address Or r Is Nothing
End If

 

[GlennUK]

It works for me. Where is your entry of xyz exactly?

 

[cheekyrich]

In column G, row 75. here's an extract. You can see XYZ there



2801 1000 0030
2801 1000 0040
2801 1000 0050
2801 1000 0060
2801 1000 0070
2801 1000 0080
2801 1000 0090
2801 1000 0160
2801 1000 0190
2801 1000 0200
2801 1000 0230
2801 1000 0240
2801 1000 0300
2801 1000 xyz
2801 1000 0600
2801 1000 0605
2801 1000 0670
2801 1000 0700
2801 1000 0726
2801 1000 0772
2801 1000 0779
2801 1000 0830
2801 1000 0839
2801 1000 0895
2801 1000 0917
2801 1000 0985
2801 1000 0996

 

[pgc01]

Hi

Just a thought...

The parameters LookIn, LookAt, SearchOrder, and MatchByte in the Find method do not have a default value and so the first time we use Find in a macro we may have an unpleasant surprise. After the first time, Find remembers their values, and so, no problem.

So, try:

Set r = Range("G61:G1000").Find("xyz", Range("G1000"), xlValues, xlPart)

Kind regards
PGC

 

[cheekyrich]

Tried that - still no good unfortunately. Doesn't do anything. When I F8, it just goes straight past the code. That is, it gets to the line below, and then jumps to "End If"

If Not r Is Nothing Then

 

[GlennUK]

What happens when you are in the spreadsheet and you select the range G61:G1000 and you press Ctrl-F and search for xyz?

 

[cheekyrich]

no problem - finds both of my "xyz" entries