I need to write a macro that will return the column number of n instances of a string. The idea is to find the first instance of a particular string, return its row number and modify something using that row number, then using a for next loop use that row number as the new starting point to find the next instance, etc.
Heres the code I have so far:
Sub Find() Dim RowCount As Integer RowCount = 0 For i = 1 To 5 RowCount = WorksheetFunction.Match("Hello", Range("A" & RowCount + 1 & ":A100"), 0) Range("B" & RowCount) = "World" Next i End Sub
In this example I am finding every instance of Hello in Column A, the using the row number to modify the exact same row in Column B to say World. Then the for loop increments and the Match function starts from the row just below the previous instance of Hello.
I just can't seem to get this to work right.
Any help is greatly appreciated.
suppose data like this from A1 down
1
2
3
hello
4
5
hello
6
7
8
hello
9
8
7
try this macro
Sub test() Dim r As Range, cfind As Range, j As Long, add As String Set r = Range(Range("A1"), Range("a1").End(xlDown)) Set cfind = r.Cells.Find(what:="hello", lookat:=xlWhole) If Not cfind Is Nothing Then add = cfind.Address j = cfind.Row MsgBox j Cells(cfind.Row, "B") = "world" Else MsgBox "that value is not available" Exit Sub End If Do Set cfind = Cells.FindNext(cfind) If cfind Is Nothing Then Exit Do If cfind.Address = add Then Exit Do j = cfind.Row MsgBox j Cells(cfind.Row, "B") = "world" Loop End Sub
I am not an expert. better solutions may be available
$$$$venkat1926$$$$@gmail.com
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks