VBA To Autofill after finding specific data

[amxtomzo]

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,

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

 

[Logit]

.

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

 

[amxtomzo]

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

 

[Logit]

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

These lines are commented out in the first post:

' 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/share/pXpESTovj0bDOL1tDkI7NgSwV3clbS5Mk4NawzpDy0s

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

 

[amxtomzo]

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

 

[Logit]

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
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td]A1[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]A2[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]A3[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]*West[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td=bgcolor:#FFFF00][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td]A6[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

7​

[/td][td]A7[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

8​

[/td][td]A8[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

9​

[/td][td]A9[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

10​

[/td][td]*West[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

11​

[/td][td]A11[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

12​

[/td][td]A12[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

13​

[/td][td]A13[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

14​

[/td][td]A14[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

15​

[/td][td]*West[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

16​

[/td][td]A16[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

17​

[/td][td]*West[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

18​

[/td][td=bgcolor:#FFFF00][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

19​

[/td][td]A19[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

20​

[/td][td]A20[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

21​

[/td][td]A21[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

22​

[/td][td]*West[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

23​

[/td][td]A23[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

24​

[/td][td]A24[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

25​

[/td][td]A25[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

26​

[/td][td]A26[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

27​

[/td][td]*West[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

28​

[/td][td=bgcolor:#FFFF00][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

29​

[/td][td]A29[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

30​

[/td][td]A30[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

 

[amxtomzo]

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

 

[Logit]

.
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.

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

 

[amxtomzo]

Logit

Thank you very much, worked great
greatly appreciate the help

Thomas

 

[Logit]

You are welcome