Excel macro - search for content and fill rows accordingly

[clem562]

Hello,

I have searched the forum for any macro resembling the one I am trying to create but no luck...

I am not an expert in VBA but I know other programming languages so the algorithm I am trying to create is clear but I lack the knowledge in Excel macro syntax to make it work...

Basically what I want to do is make a macro that searches for a specific string, which will be "CODE A" and then fills the cell containing "CODE A" (basically a find and replace) and the cells under with specific content.

For example : cell A1 contains "CODE A", I want the macro to fill cell A1 with "fubar1", A2 with "fubar2" and so on until A5.

An English version of the code would be :
search for "CODE A"
var = cell of "CODE A"
fill cell var with "fubar1"
fill cell under var with "fubar2" (I believe there is an "offset" function that does something like that...)
...

Any help would be greatly appreciated :D

Thanks in advance for your answers

PS : Sorry for any grammar/spelling mistakes, English is not my native language.

 

[Fluff]

Hi & welcome to the board.
A few questions
1) Do you want want to change CODE A once, or are there multiple instances that need changing?
2) where in the sheet will it be found? in Col A only or anywhere?
3) Once found, are the 4 cells below to be overwritten, or do you need 4 cells inserted?

 

[clem562]

Hi & welcome to the board.

Hello and thank you for your answer

1) Do you want want to change CODE A once, or are there multiple instances that need changing?
2) where in the sheet will it be found? in Col A only or anywhere?
3) Once found, are the 4 cells below to be overwritten, or do you need 4 cells inserted?

1) There are multiples instance of CODE A
2) All of them are located in column F
3) The cells under need to be overwritten (they are blank anyway)

 

[Fluff]

Ok, try this

Sub ReplaceCodeA()

    Dim Fnd As Range
    Dim Cnt As Long

    Set Fnd = Range("F1")
    For Cnt = 1 To Application.Countif(Columns(6), "CODE A")
        Set Fnd = Columns(6).Find(What:="CODE A", After:=Fnd, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        If Not Fnd Is Nothing Then
            Fnd.Value = "fubar1"
            Fnd.AutoFill Fnd.Resize(5)
        End If
    Next Cnt

End Sub

This need to go into a standard module

 

[clem562]

Thanks for your kind answer.
I tested the code, it does almost exactly what I want except for one thing which is due to my poorly formulated question : I want to fill the cells under CODE A with several different strings (and not string1, string2, string3) for example :
CODE A becomes "My tailor is rich"
cell under CODE A become "My sister is not a boy"
etc.
I have tried to modify the code myself but had no success, I fail to understand which part of the code generates the content of the cells to be filled... I guess it is

"Fnd.Autofill Fnd.Resize(5)"

but have no clue whatsoever as to how I would be able to modify it to make it do what I want to do...

I have tried this but I had little hope about whether it would work and indeed it doesn't, I get a compilation error

Sub ReplaceCodeA()

    Dim Fnd As Range
    Dim Cnt As Long

    Set Fnd = Range("F1")
    For Cnt = 1 To Application.CountIf(Columns(6), "CODE A")
        Set Fnd = Columns(6).Find(What:="CODE A", After:=Fnd, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        If Not Fnd Is Nothing Then
            Fnd.Value = "fubar1" Fnd.Resize (5)
            Fnd.Value = "string number 2"
            Fnd.Value = "my string 3"
            Fnd.Value = "my string number 4"
            Fnd.Value = "this is the last string"
            
        End If
    Next Cnt

End Sub

 

[Fluff]

OK, you need to offset the Fnd range

Sub ReplaceCodeA()

    Dim Fnd As Range
    Dim Cnt As Long

    Set Fnd = Range("F1")
    For Cnt = 1 To Application.Countif(Columns(6), "CODE A")
        Set Fnd = Columns(6).Find(What:="CODE A", After:=Fnd, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        If Not Fnd Is Nothing Then
            Fnd.Value = "fubar1"
            Fnd.Offset(1).Value = "And now"
            Fnd.Offset(2).Value = "for something"
            Fnd.Offset(3).Value = "completely"
            Fnd.Offset(4).Value = "different"
        End If
    Next Cnt

End Sub

 

[clem562]

Works like a charm, thank you very much for your help !!
Love the Monty Python reference by the way

Should I mark the thread as solved or something like that ?

 

[Fluff]

Glad to help & thanks for the feedback.

Should I mark the thread as solved or something like that ?

Nope, threads are kept open, incase of any further queries.