VBA copying WRONG cell

[klutch]

I have this

Sub CopyAndPaste()
Dim myfile, wdApp As New Word.Application, wdDoc As Word.Document
myfile = Application.GetOpenFilename(, , "Browse for Document")
Dim lrow As Long, rng As Range, cell As Range
    lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
    Set rng = Range("A2:A" & lrow)
    For Each cell In rng
        If InStr(1, cell.Value, "avg", vbTextCompare) > 0 Then
            cell(0, 4).Select
            Selection.Copy
        End If
    Next cell
wdApp.Visible = True
Set wdDoc = wdApp.Documents.Open(myfile)
'select the word range you want to paste into
    wdDoc.Bookmarks("PRtable").Select
    'and paste the clipboard contents
    wdApp.Selection.Paste
End Sub

It was working fine yesterday but today it is being difficult.
Here are my objectives.
1. Search column A for the text "avg"
2. Select Column E of that same row
3. Paste selected cell into myfile, bookmark "PRTable" Cell(3,4).

Here is what is happening when I run the code.
1. Copies the wrong cell
2. Pastes into every cell of the table.

Yesterday it was copying the correct cell, but was still pasting into the entire table.

 

[njimack]

What happens if you step through the code using F8?
(btw you don't need to select cells to perform actions on them. You can use this single line of code:

If InStr(1,cell,"avg",vgTextCompare) > 0 Then cell.Copy

 

[klutch]

Without altering my code, I am now getting error "User-defined type not defined"
for this line

, wdapp As New word.Application

Disregard This: For some reason my vb decided to unadd Microsoft Word 16.0 as a reference

 

[klutch]

@njimack It is still copying the wrong code when I go through with F8

 

[njimack]

You're copying cell(0,4) rather than the cell you're testing

 

[NoSparks]

cell(0, 4) is column D

cell.offset(, 4) would be column E

If there's more than one occurrence of "avg" in rng you will only be copying the last one.

 

[klutch]

Ok, I changed my code and am now copying the correct cell.

Sub CopyAndPaste()
Dim myfile, wdApp As New Word.Application, wdDoc As Word.Document
myfile = Application.GetOpenFilename(, , "Browse for Document")
Dim i As Integer
i = Application.Match("Avg", Sheet1.Range("A1:A20"), 0)
Range("E" & i).Select
Selection.Copy

wdApp.Visible = True
Set wdDoc = wdApp.Documents.Open(myfile)
'select the word range you want to paste into
    wdDoc.Bookmarks("PRtable").Select
    'and paste the clipboard contents
    wdApp.Selection.Paste
End Sub

Now I need to paste into the correct part of the table in word. It is pasting into every single cell where as I need it to specifically be in Cell (3,4) of the bookmarked table

 

[njimack]

Presumably because you're selecting the entire table and then using Selection.Paste (can't offer a correction as I have no idea how to code in Word).

 

[Fluff]

Cross posted https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1204466-if-statements-using-vba-code

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.