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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,851
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,402

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,402

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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,424
Messages
5,528,685
Members
409,830
Latest member
KT50

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top