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

 

[pgc01]

Hi Rich

Check the example in the help, under "Find method". I believe it's exactly what you need.

HTH
PGC

 

[jindon]

To the starter

Sub test()
Dim r As Range, ff As String
Set r = Range("C1:C50").Find("0330",Range("C50"),,xlPart)
If Not r Is Nothing Then
   ff = r.Address
   Do
      'Call YourMacro
      Set r = Range("C1:C50").FindNext(r)
   Loop Until ff = r.Address Or r Is Nothing
End If

 

[cheekyrich]

Great thank you! I have used the following, but I still get a 'Loop without Do' error when I run it. Any idea what I'm doing wrong now? Thanks again,

Rich



Dim r As Range, ff As String
Set r = Range("G60:G1000").Find("0330", Range("G60"), , xlPart)
If Not r Is Nothing Then
ff = r.Address
Do
ActiveCell.Offset(0, -5).Select
ActiveCell.FormulaR1C1 = "'1001"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "'1000"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "'002"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "'01"
ActiveCell.Offset(0, 1).Select 'This is cat 4 - what change to?
If Trim(Sheet1.Cells(i, "S").Text) = "se" Then
ActiveCell.FormulaR1C1 = "'1100"
Set r = Range("G60:G1000").FindNext(r)
Loop Until ff = r.Address Or r Is Nothing
End If

 

[GlennUK]

You are missing an End If ....

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

should be closed before the Loop statement.

 

[cheekyrich]

Aaaaaaaaaaaargh!!!! Still doesn't work. It just doesn't find 'xyz'!!! HELP please before I have no hair left.

Thank you!




Worksheets("GL07").Activate
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
ActiveCell.Offset(0, -5).Select
ActiveCell.FormulaR1C1 = "'1001"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "'1000"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "'002"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "'01"
ActiveCell.Offset(0, 1).Select
If Trim(Sheet1.Cells(i, "S").Text) = "te" Then
ActiveCell.FormulaR1C1 = "'1100"
End If

Set r = Range("G61:G1000").FindNext(r)
Loop Until ff = r.Address Or r Is Nothing
End If

 

[GlennUK]

Instead of just scanning your code quickly to find the compile error, I actually read it this time, and the code looks wrong .... very wrong. You are using ActiveCell inside your loop instead of r. You are doing selects and offsets instead of just changing the contents of cells directly.

Is there "xyz" in the range G61:G1000? How do you know that the Find failed?

 

[cheekyrich]

Yikes. I know the find failed because nothing happens!

Errr can you rearrange it for me?

Gods of VBA, help me please!!!

 

[GlennUK]

Yikes. I know the find failed because nothing happens!

... or because of the errors I pointed out. Fix the code!

 

[cheekyrich]

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