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]

Describe the sequence of events leading up to how you run this macro please.

 

[cheekyrich]

Ok...

I open VBA. The entire code that I am looking at is below. I have a spreadsheet of data. The column I am concerned with is G. I want to look up values in that column (call them xyz), and change corresponding values elsewhere on each row found.

I execute the macro by either running the entire code. Or tabbing through pressing F8. Everything goes fine and there are no errors signalled. My code tabs all the way through to "If Not r Is Nothing Then" and then jumps straight past my data-changing code to "End if". So I am assuming that the xyz is just not being found.

Then I reconsider my value in life, before re-posting back on Mr.Excel as to how utterly rubbish my VBA capabilities are!

Thank you again,

Rich






Private Sub CommandButton2_Click()

Worksheets("GL07").Activate
Application.ScreenUpdating = True
Dim copyIndex As Integer
Dim rowFound As Boolean
rowFound = False
copyIndex = 61
Worksheets("GL07").Activate

Dim r As Range, ff As String
Set r = Range("G61:G1000").Find("xyz", Range("G1000"), xlValues, 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

End Sub

 

[GlennUK]

This clue

CommandButton2_Click

says a lot.

Are you running this from an embedded button? If so, did you set the TakeFocusOnClick property to False?

 

[cheekyrich]

I have added that line. That hasn't changed anything



Private Sub CommandButton2_Click()

TakeFocusOnClick = False
Worksheets("GL07").Activate
Application.ScreenUpdating = True
Dim copyIndex As Integer
Dim rowFound As Boolean
rowFound = False
copyIndex = 61
Worksheets("GL07").Activate

Dim r As Range, ff As String
Set r = Range("G61:G1000").Find("xyz", Range("G1000"), xlValues, 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

End Sub

 

[GlennUK]

You have added a line to the macro that just sets a variable to a value. I am talking about changing the property of your embedded button. Go to the sheet and change to design mode and check the properties of the button.

 

[cheekyrich]

I have done that and it's still exactly as I described it above. Not having much luck am I!

 

[cheekyrich]

Ok I've changed a few things around on it now - I solved the skipping issue (it was the "" that it didn't need). So my code now looks as shown below. Brilliant - it now does actually go through the Value-change steps, but isn't actually changing anything!!! Any thoughts on how this now looks?

Oh, and the bit that I've commented out, it really doesn't like.

Thank you,

Rich




Worksheets("GL07").Activate
Dim w As Range, ff As String
Set w = Range("G61:G500").Find(xyz, Range("G500"), xlValues, xlPart)
If Not w Is Nothing Then
ff = w.Address
Do
w.Offset(0, -5).Value = "'1001"
w.Offset(0, 1).Value = "'1000"
w.Offset(0, 1).Value = "'002"
w.Offset(0, 1).Value = "'01"
'If Trim(Sheet1.Cells(i, "S").Text) = "te" Then
'w.Offset(0, 1).Value = "'1100"
'End If
Set w = Range("G60:G500").FindNext(w)
Loop Until ff = w.Address Or w Is Nothing
End If

 

[cheekyrich]

Ok I've changed a few things around on it now - I solved the skipping issue (it was the "" that it didn't need). So my code now looks as shown below. Brilliant - it now does actually go through the Value-change steps, but isn't actually changing anything!!! Any thoughts on how this now looks?

Oh, and the bit that I've commented out, it really doesn't like.

Thank you,

Rich




Worksheets("GL07").Activate
Dim w As Range, ff As String
Set w = Range("G61:G500").Find(xyz, Range("G500"), xlValues, xlPart)
If Not w Is Nothing Then
ff = w.Address
Do
w.Offset(0, -5).Value = "'1001"
w.Offset(0, 1).Value = "'1000"
w.Offset(0, 1).Value = "'002"
w.Offset(0, 1).Value = "'01"
'If Trim(Sheet1.Cells(i, "S").Text) = "te" Then
'w.Offset(0, 1).Value = "'1100"
'End If
Set w = Range("G60:G500").FindNext(w)
Loop Until ff = w.Address Or w Is Nothing
End If

 

[Norie]

Rich

In the Find method the 2nd argument is After.

You appear to be setting this to G1000 in the earlier code and G500 in the latest code.

You are searching G61:G500.

Now I may be wrong but surely starting the search at after the last cell in the search range won't work.

 

[GlennUK]

Take Norie's advice. And also change the arguments in your Offsets back to what I had.