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
 
Describe the sequence of events leading up to how you run this macro please.
 
Upvote 0

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Take Norie's advice. And also change the arguments in your Offsets back to what I had.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

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
Back
Top