IF statements using VBA code

klutch · Jun 13th 2018

I am trying to write vba code for data that will not always be in the same cells in the workbooks, however the cell that I want will always have "avg" in Column A. I want to code something along the lines of If range("A1:A50") = avg then copy E:(corresponding row to "avg").
Is this possible? If not, how else could I code my macro to select the cell I want?

**Carim** · Jun 14th 2018

Hi,

Why don't you attach a sample workbook ... to make things a lot easier ...:wink:

klutch · Jun 14th 2018

[USER="31712"]Carim[/USER] is this okay? To reiterate, I am trying to select the cell in column E that corresponds to the row that "Avg" is in Column A. I think IF statements or lookup would be my best route but I am not skilled enough in VBA to know the code for it. Thanks!

klutch · Jun 14th 2018

Whoops, It uploaded with no data! How do I upload it without directly attaching my example file?

klutch · Jun 14th 2018

Testing if this fixes the problem

**Carim** · Jun 15th 2018

Hi,

Your Excel looks empty ...

Attached is a sample workbook ...:wink:

Hope this will help

klutch · Jun 15th 2018

[USER="31712"]Carim[/USER] Hello, thank you for taking your time to help me from the code you provided, how would I then select and copy that cell with vba? I need to automate the copy/paste process. This is what I have for my macro rn.

Code

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

Display More

The problem with this is that it wont select the cell I want it to. Also, when it copies into my table, it takes up every singe cell, where as I just want it to take up one of them. Specifically Cell(2,4).

klutch · Jun 15th 2018

Code

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

Display More

Okay this works. Now I just need to paste it into the correct part of the bookmarked table. It copies into every cell of it where I would like it to just copy into Cell (3,4) of the table

klutch · Jun 15th 2018

Some more development..

Code

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
  If Range("c2") = 22 Then wdDoc.Bookmarks("d22").Select
  If Range("c2") = 5 Then wdDoc.Bookmarks("d5").Select
  If Range("c2") = -20 Then wdDoc.Bookmarks("d20").Select
 
   
   
    'and paste the clipboard contents
    wdApp.Selection.Paste


End Sub

Display More

This is what I was looking to do. Is there a way to code it so that if the bookmark already has text in it, then it will paste below it, and so on?

**Reafidy** · Jun 15th 2018

Cross Post

Please read the rules regarding cross posting.

Fullam1 · Jun 17th 2018

Hello Klutch

Try this....

Code

Sub ExcelToWord()


Dim wdApp As Object
Set wdApp = GetObject(, "Word.Application")
Set wdDoc = wdApp.ActiveDocument


'Copy single cell from Excel to Word _
Finds the cell with "Avg" [you say is in column A] then offsets by 4 to column "E" and copies


ThisWorkbook.Sheets(1).Cells.Find("Avg").Offset(0, 4).Copy


'It then pastes into first cell in "Table 1" in the active word doc, change destination if you need.
wdDoc.Tables(1).Cell(1, 1).Range.PasteAndFormat (22)


Set wdApp = Nothing
Set wdDoc = Nothing


End Sub

Display More

Good Luck
Mike

IF statements using VBA code

Participate now!

Share