Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: VBA To Autofill after finding specific data

  1. #1
    Board Regular
    Join Date
    Nov 2009
    Location
    western ma, usa
    Posts
    299
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question VBA To Autofill after finding specific data

    hello and thanks for helping

    I cant seem to quite find what I am looking for so here it goes,

    I am looking through column A for "*West"
    then, if the next cell down is blank, I want it to = *West,
    however,
    if the next cell down has data, I want it to stop

    I started with this, and had no luck,

    Code:
    For i = 1 To finalrow
        If WST.Cells(i, 1) = "*WEST" Then
                WST.Cells(i + 1, 1) = "*WEST"
                ElseIf WST.Cells(i + 1, 1).Value > "" Then
                ' i would like it to stop
    thank you

    Thomas

  2. #2
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    1,879
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA To Autofill after finding specific data

    .
    Code:
    Option Explicit
    
    
    Sub FindTerm()
        Dim c As Range
        Dim j As Integer
        Dim myString As String
    
    
        ' Change worksheet designations as needed
        'Set Source = ActiveWorkbook.Worksheets("Sheet2")
        'Set Target = ActiveWorkbook.Worksheets("Search Results")
        
        myString = Application.InputBox("Enter A Search")
        
        j = 2     ' Start copying to row 1 in target sheet
        For Each c In Range("A:A")
            If c = myString Then
            c.Select
                If ActiveCell.Offset(1, 0) = "" Then
                    ActiveCell.Offset(1, 0) = myString
                    Exit Sub
                Else
                    MsgBox "Search Complete - No Results", vbInformation, "Search Results"
                    Exit Sub
                End If
               
               j = j + 1
            End If
        Next c
    End Sub

  3. #3
    Board Regular
    Join Date
    Nov 2009
    Location
    western ma, usa
    Posts
    299
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA To Autofill after finding specific data

    Logit
    thank you helping

    not working
    I put in the worksheets names for set Source & Target, its "PasteHere" for both
    I entered my search term and that is "*West"

    it returned - Variable not defined
    and highlighted Source =
    any ideas?

    Thomas

  4. #4
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    1,879
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA To Autofill after finding specific data

    .
    You can totally omit a few lines from the macro.

    These lines are commented out in the first post:

    Code:
    ' Change worksheet designations as needed
        'Set Source = ActiveWorkbook.Worksheets("Sheet2")
        'Set Target = ActiveWorkbook.Worksheets("Search Results")
    Whenever a line of code has a single quote mark in front of it ' Excel understands anything after that ' on the same line is to be ignored.
    That is how you insert comments into your code so it can explain something about the code for future review. Just delete those lines ...
    using the macro below instead.

    However, to make things easy for you, you can download the working workbook from : https://www.amazon.com/clouddrive/sh...S5Mk4NawzpDy0s


    Code:
    Option Explicit
    
    
    Sub FindTerm()
        Dim c As Range
        Dim j As Integer
        Dim myString As String
    
    
        myString = Application.InputBox("Enter A Search")
        
        j = 2     ' Start copying to row 1 in target sheet
        For Each c In Range("A:A")
            If c = myString Then
            c.Select
                If ActiveCell.Offset(1, 0) = "" Then
                    ActiveCell.Offset(1, 0) = myString
                    Exit Sub
                Else
                    MsgBox "Search Complete - No Results", vbInformation, "Search Results"
                    Exit Sub
                End If
               
               j = j + 1
            End If
        Next c
    End Sub

  5. #5
    Board Regular
    Join Date
    Nov 2009
    Location
    western ma, usa
    Posts
    299
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA To Autofill after finding specific data

    Logit

    apologies, I thought the worksheet name would go there, so I left the lines commented out

    still not quite there,
    Both macros only copied 1 time to the cell below, probably 20 cells
    below that still need to be filled in


    any ideas, Thanks

    Thomas

  6. #6
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    1,879
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA To Autofill after finding specific data

    So I better understand ... is this representative of what you are working with ? Where ever a yellow cell appears (its empty) you want *West to be inputted ?

    Excel 2007 32 bit
    A
    1
    A1
    2
    A2
    3
    A3
    4
    *West
    5
    6
    A6
    7
    A7
    8
    A8
    9
    A9
    10
    *West
    11
    A11
    12
    A12
    13
    A13
    14
    A14
    15
    *West
    16
    A16
    17
    *West
    18
    19
    A19
    20
    A20
    21
    A21
    22
    *West
    23
    A23
    24
    A24
    25
    A25
    26
    A26
    27
    *West
    28
    29
    A29
    30
    A30
    Sheet: Sheet1
    Last edited by Logit; Jun 13th, 2018 at 12:04 PM.

  7. #7
    Board Regular
    Join Date
    Nov 2009
    Location
    western ma, usa
    Posts
    299
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA To Autofill after finding specific data

    Logit

    sorry about the delay, long day and all..
    ok, from your image
    Cell A4 is the 1st and only time we see *West
    the next 20+ consecutive cells below it will be empty, I would like to fill them with *West

    eventually there is a cell in column A that has data in it , and I need to stop at the cell right above it

    hope that helps

    Thomas

  8. #8
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    1,879
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA To Autofill after finding specific data

    .
    This edited macro will fill all empty cells below the found term cell. Also keep in mind, if *West is the last cell in Col A and all cells below that are empty,
    those cells will also be populated with *West .... all the way to the 1,048,576th row.

    Code:
    Option Explicit
    
    
    Sub FindTerm()
        Dim c As Range
        Dim j As Integer
        Dim myString As String
    
    
        myString = Application.InputBox("Enter A Search")
        
        j = 2    ' Start copying to row 1 in target sheet
        For Each c In Range("A:A")
            If c = myString Then
            c.Select
                If ActiveCell.Offset(1, 0) = "" Then
                    If c.Offset(1, 0) = "" Then
                        c.Offset(1, 0) = myString
                    End If
                End If
               
               j = j + 1
            End If
        Next c
        MsgBox "Search Complete", vbInformation, "Search Results"
        
    End Sub

  9. #9
    Board Regular
    Join Date
    Nov 2009
    Location
    western ma, usa
    Posts
    299
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA To Autofill after finding specific data

    Logit

    Thank you very much, worked great
    greatly appreciate the help

    Thomas

  10. #10
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    1,879
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA To Autofill after finding specific data

    You are welcome

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •