0

I have a code that's first job is to find "Qty" and "Fty" word in P column. Then it will get the row number of that row. The code was running smoothly. But now when I added a loop the code throws an error. "run time error method find of object range failed" I want that the loop will be get that two row number and a value will be print after the first row till the last row.

Suppose you have input "qty" word in row number 12 and column number P and "fty" word in row number 15 and column number P(Here is to mention that the column number of the input cell will be always in P). The loop's work is to get the value from A13 to A14 and print in the P13 to P14.

Here is my code :

Private Sub Worksheet_Change(ByVal Target As Range)

Dim qtyWordFinder As Range
Dim qtyWordFinder2 As Range
Dim acidWashQty As Integer
Dim rw1 As Integer
Dim rw2 As Integer
    
    Set qtyWordFinder = Range("$P$1:$P$53").Find("Qty", SearchOrder:=xlColumns)
    Set qtyWordFinder2 = Range("$P$1:$P$53").Find("Fty", SearchOrder:=xlColumns)
    
    If qtyWordFinder Is Nothing Then
    Else
        rw1 = qtyWordFinder.row
    End If
    
    If qtyWordFinder2 Is Nothing Then
    Else
        rw2 = qtyWordFinder2.row
    End If
    
Dim i As Integer
    For i = rw1 + 1 To rw2 - 1
        Cells(i, 16).Value = Cells(i, 1).Value
    Next i

End Sub

Help Pls!

1

Try this syntax

For the msg request:

If Not Intersect(Target, Range("A1")) Is Nothing Then
Dim WordFinder As Range
Dim rw1 As Range
Dim rw2 As Range

    
    For Each WordFinder In Range("$P$1:$P$53")
        If InStr(WordFinder.Value, "qty") Then Set rw1 = Range(WordFinder.Address)
        If InStr(WordFinder.Value, "fty") Then Set rw2 = Range(WordFinder.Address)
    Next WordFinder

Debug.Print rw1.Value & "-" & rw2.Value

End If

End Sub
Option Compare Text


Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1")) Is Nothing Then
Dim WordFinder As Range
Dim qtyWordFinder2 As Range
Dim rw1 As Integer
Dim rw2 As Integer
    
    For Each WordFinder In Range("$P$1:$P$53")
        If InStr(WordFinder.Value, "qty") Then rw1 = WordFinder.Row
        If InStr(WordFinder.Value, "fty") Then rw2 = WordFinder.Row
    Next WordFinder
    
    If rw1 > 0 And rw2 > 0 Then
        Dim i As Integer
        For i = rw1 + 1 To rw2 - 1
            Cells(i, 16).Value = Cells(i, 1).Value
        Next i
    End If
End If

End Sub

The option compare text at the top of the page means it will ignore upper/lower case differences in the text.

I have linked it to a change in A1 for testing

7
  • many many thanks bro. It really helped. Happy coding :) – Mehedi Aug 27 '20 at 6:01
  • brother if I want to get the cell address of the word "qty" is entered and store that in a variable then how to get that from this code? – Mehedi Aug 27 '20 at 6:23
  • var = wordfinder.address – Justin Doward Aug 27 '20 at 6:35
  • I tried. But it is showing an error. Run time error object variable or with block variable not set. The error is in line var = wordfinder.address – Mehedi Aug 27 '20 at 6:40
  • Have updated the question with an example. – Justin Doward Aug 27 '20 at 9:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.