MACRO Request: How Can I Have One Cell Equals X Then Another Cell Equals Y (Can Not be a Formula)

[Bowater]

Please help! I am attempting to make one larger macro to automate the creation of a report for work. I have got to a stage where the data looks like so:

A B < columns

£ 1
$ 2
% 3
^ 4
& 1

{100 rows in between}

£ 1
$ 2
% 3
^ 4
& 1

Column B contains data and column A needs to contain a title. The five rows/two columns give a summary of the data further right and there are 100 rows in between the summaries and approximately 40 different summaries (documents). The '£' row is the title of the document and '&' is a URL link to the document, both of these cells have identical values but a different title so a formula will not help me!

What I would like to do is this: Value 3 is always the same value. So my solution is to have the macro search for 3 in column B and then insert value % into column A... then the macro can search for value % in column A, if % is two cells below input £, if it is one cell below input $, if it is one cell above then input ^ and if it is two above input &.

The summary box is actually made up of ten boxes in the excel but if I knew the general format for the code I could do it for all 10.


Thank you.

 

[drsarao]

Bowater,
Welcome to Board!
(From whatever I understood) Try this:

Sub AddTitles()
lr = Cells(Rows.Count, "B").End(xlUp).Row 'last row in column B
For r = 1 To lr
    If Cells(r, "B") = 3 Then
        Cells(r - 2, "A") = "£"
        Cells(r - 1, "A") = "$"
        Cells(r, "A") = "%"
        Cells(r + 1, "A") = "^"
        Cells(r + 2, "A") = "&"
    End If
Next r
End Sub

 

[Bowater]

Thank you very much drsarao, you got it perfect first time!

 

[drsarao]

You are welcome!