GOTO or FIND NEXT function - any suggestions please?

cheekyrich

Board Regular
Joined
Jun 2, 2005
Messages
102
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi Rich

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

HTH
PGC
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
To the starter
Code:
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

Board Regular
Joined
Jun 2, 2005
Messages
102
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

Well-known Member
Joined
Jul 8, 2002
Messages
11,547

ADVERTISEMENT

You are missing an End If ....
Code:
If Trim(Sheet1.Cells(i, "S").Text) = "se" Then
should be closed before the Loop statement.
 

cheekyrich

Board Regular
Joined
Jun 2, 2005
Messages
102
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

Well-known Member
Joined
Jul 8, 2002
Messages
11,547

ADVERTISEMENT

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

Board Regular
Joined
Jun 2, 2005
Messages
102
Yikes. I know the find failed because nothing happens!

Errr can you rearrange it for me?

Gods of VBA, help me please!!!
 

cheekyrich

Board Regular
Joined
Jun 2, 2005
Messages
102
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
 

Forum statistics

Threads
1,141,139
Messages
5,704,514
Members
421,353
Latest member
jekoxien15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top