Do an action until find a word in a column

[kfilippo]

Hello,

What is the command to do an action until I find a specific work in my column A? And once this word is found, stop the action.

Thank you

 

[Tom Urtis]

Maybe the command is called a loop using VBA, but maybe also you can use a formula depending on what it is you are doing. More info from you can help people here to help you with a solution.

 

[kfilippo]

Maybe the command is called a loop using VBA, but maybe also you can use a formula depending on what it is you are doing. More info from you can help people here to help you with a solution.

I need to paste a certain text in the rows of column H until the word "account" appears in column A.
For ex, if the word "account" appears in cell A6, I need to paste my text on H1 to H5 cells.

 

[Tom Urtis]

This can be done with a formula if by paste you mean just put the word "account" into (for example) H1:H5.

But if you...
• need to append the existing values that are each in H1:H5 with "account",
• or if by paste you are first copying it from another outside source,
• or you don't want all those formulas in column H,
...then VBA would be the better way to go. What's your preference?

 

[kfilippo]

This can be done with a formula if by paste you mean just put the word "account" into (for example) H1:H5.

But if you...
• need to append the existing values that are each in H1:H5 with "account",
• or if by paste you are first copying it from another outside source,
• or you don't want all those formulas in column H,
...then VBA would be the better way to go. What's your preference?

Yes, I need to append the existing values that are each in H1:H5 with "account". Also, I´m copying it from another outside source.

Thanks

 

[Tom Urtis]

Assuming by append, you want account to precede the existing data,

Sub Test1()
Dim keyVal$, vRow As Variant
keyVal = "account"
vRow = Application.Match(keyVal, Columns(1), 0)
If IsError(vRow) Then
MsgBox keyVal & " not found", 64, "No row reference"
Else
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Range(Cells(1, 8), Cells(vRow - 1, 8))
cell.Value = keyVal & " " & cell.Value
Next cell
Application.ScreenUpdating = True
End If
End Sub

 

[Tom Urtis]

If you have different text at any given time on your clipboard, this is what you would need for that:

Sub Test2()
Dim keyVal$, vRow As Variant
keyVal = "account"
vRow = Application.Match(keyVal, Columns(1), 0)
If IsError(vRow) Then
MsgBox keyVal & " not found", 64, "No row reference"
Else
Dim objAppend As MsForms.DataObject, strAppend$, cell As Range
Set objAppend = New MsForms.DataObject
objAppend.GetFromClipboard
On Error Resume Next
strAppend = objAppend.GetText(1)
If Err.Number <> 0 Then
Err.Clear
MsgBox "No text is on the clipboard.", 48, "Nothing to append"
Exit Sub
End If
Application.ScreenUpdating = False
For Each cell In Range(Cells(1, 8), Cells(vRow - 1, 8))
cell.Value = strAppend & " " & cell.Value
Next cell
Set objAppend = Nothing
Application.ScreenUpdating = True
End If
End Sub