Announcement

Collapse
No announcement yet.

IF statements using VBA code

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • IF statements using VBA code



    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 Ecorresponding row to "avg").
    Is this possible? If not, how else could I code my macro to select the cell I want?

  • #2
    Hi,

    Why don't you attach a sample workbook ... to make things a lot easier ...
    If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

    Comment


    • #3
      Carim 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!
      Attached Files

      Comment


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

        Comment


        • #5
          Testing if this fixes the problem
          Attached Files

          Comment


          • #6
            This is the code I have to copy a specific cell and paste it into the word doc I want.
            Code:
            Sub CopyAndPaste()
            Dim myfile, wdApp As New Word.Application, wdDoc As Word.document
            myfile = Application.GetOpenFilename(, , "Browse for Document")
            Range("e4").Copy
            wdApp.Visible = True
            Set wdDoc = wdApp.Documents.Open(myfile)
            wdDoc.Range.Characters.Last.PasteExcelTable False, False, False
            End Sub
            Right now, I am manually entering the cell in the Range("E4").copy
            As the location of this cell will be changing with each worksheet, I need to code it so that it copies column E of the row that the text "avg" is in.
            I know pictures are generally not useful on this forum but I am having trouble uploading a file so I will attach a screenshot of the sheet so you can have a reference.
            After I am able to code this, I am still looking for a way to paste it into the right table in the word doc. I will attach a second image so you can see what I am talking about.
            I think that I can bookmark the table and then use code to select the cell I want it in. In my attachment I put "y" where I would like it to copy to

            Comment


            • #7
              Hi,

              Your Excel looks empty ...

              Attached is a sample workbook ...

              Hope this will help
              Attached Files
              If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

              Comment


              • #8
                Carim 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
                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).

                Comment


                • #9
                  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
                  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

                  Comment


                  • #10
                    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
                    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?

                    Comment


                    • #11
                      Cross Post

                      Please read the rules regarding cross posting.
                      Reafidy

                      Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

                      Comment


                      • #12


                        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
                        Good Luck
                        Mike
                        Last edited by Reafidy; June 18th, 2018, 12:08.

                        Comment

                        Working...
                        X