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

#### Bowater

##### New Member
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.

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Bowater,
Welcome to Board!
(From whatever I understood) Try this:

Code:
``````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``````

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

You are welcome!

Replies
3
Views
325
Replies
1
Views
276
Replies
1
Views
209
Replies
2
Views
239
Replies
13
Views
386

1,196,073
Messages
6,013,267
Members
441,758
Latest member
Abren

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back