#### Ste_Moore01

##### Active Member

- Joined
- Jul 13, 2005

- Messages
- 467

I've created a spreadsheet that I want to add some code to.

I have 10 columns, the first 6 have data that doesn't change. The last 4 are blank.

I want to add a macro that runs automatically when a cell is selected.

If the cell is merged with another it doesn't run a macro (in this case the cells that would be merged would be from column A to J), but if it isn't merged then it would check to see if columns G, H, I and J were empty.

If all of these cells were empty it would hide the line, if 1 of the cells wasn't empty, dependng on which cell it was, it would add the rest of the information into the empty cells.

I know this may be hard to follow so I've posted an example below.

blank.xls | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|

A | B | C | D | E | F | G | H | I | J | K | |||

1 | Stock Details | TRADE PRICE | Discount | INVOICE PRICE | |||||||||

2 | Reference | Description | Pack Qty | Pack | Each/Metre | Length | % | Pack | Each/Metre | Length | |||

3 | This row is merged so it is left alone. | ||||||||||||

4 | 100mm x 50mm Kestrel Perimeter Trunking | This row is merged so it is left alone. | |||||||||||

5 | This row is merged so it is left alone. | ||||||||||||

6 | MCT100 | 100mm x 50mm Kestrel Trunking | 12 metres | 208.20 | 17.35 | 52.05 | 85.00 | This row has data in column G so will enter the data into H, I and J. | |||||

7 | MDS100 | 50mm Clip-In Dividing Strip | 3 metres | 9.00 | 3.00 | 9.00 | 1.35 | This row has data in column J so will enter the data into G, H, and I. | |||||

8 | MCT100/IA | Internal Angle - To Suit MCT100 | 1 | 22.13 | 22.13 | N/A | 3.32 | This row has data in column I so will enter the data into G, H and J | |||||

9 | MCT100/EA | External Angle - To Suit MCT100 | 1 | 22.13 | 22.13 | N/A | 3.32 | This row has data in column H so will enter the data into G, I and J | |||||

10 | MCT100/FA | Flat Angle - To Suit MCT100 | 1 | 22.13 | 22.13 | N/A | This row doesn't have data in G, H, I or J so will be hidden. | ||||||

11 | MCT100/FT | Flat Tee - To Suit MCT100 | 1 | 46.67 | 46.67 | N/A | This row doesn't have data in G, H, I or J so will be hidden. | ||||||

12 | MCT100/SC | Joint Cover - To Suit MCT100 | 1 | 3.33 | 3.33 | N/A | This row doesn't have data in G, H, I or J so will be hidden. | ||||||

13 | MCT100/SE | Stop End - To Suit MCT100 | 1 | 3.33 | 3.33 | N/A | This row doesn't have data in G, H, I or J so will be hidden. | ||||||

14 | This row is merged so it is left alone. | ||||||||||||

DISCOUNT ENTRY |

I already have the code to add the data which is

Code:

```
Sub test()
Dim thisrow
thisrow = ActiveCell.Row
If Range("G" & thisrow)<> "" Then
Call daddpack
Call daddeach
Call daddlength
ElseIf Range("H" & thisrow)<> "" Then
Call padddisc
Call paddeach
Call paddlength
ElseIf Range("I" & thisrow)<> "" Then
Call eadddisc
Call eaddpack
Call eaddlength
ElseIf Range("J" & thisrow)<> "" Then
Call ladddisc
Call laddpack
Call laddeach
Else
Rows(thisrow).Select
Selection.EntireRow.Hidden = True
End If
End Sub
Sub daddpack()
Dim thisrow
thisrow = ActiveCell.Row
Range("h" & thisrow).Formula = "=D" & thisrow & "-((D" & thisrow & "/100)*G" & thisrow & ")"
End Sub
Sub daddeach()
Dim thisrow
thisrow = ActiveCell.Row
Range("I" & thisrow).Formula = "=E" & thisrow & "-((E" & thisrow & "/100)*G" & thisrow & ")"
End Sub
Sub daddlength()
Dim thisrow
thisrow = ActiveCell.Row
Range("J" & thisrow).Formula = "=IF(F" & thisrow & "=""N/A"",""N/A"",F" & thisrow & "-((F" & thisrow & "/100)*G" & thisrow & "))"
End Sub
Sub padddisc()
Dim thisrow
thisrow = ActiveCell.Row
Range("G" & thisrow).Formula = "=100-((H" & thisrow & "/D" & thisrow & ")*100)"
End Sub
Sub paddeach()
Dim thisrow
thisrow = ActiveCell.Row
Range("I" & thisrow).Formula = "=E" & thisrow & "-((E" & thisrow & "/100)*G" & thisrow & ")"
End Sub
Sub paddlength()
Dim thisrow
thisrow = ActiveCell.Row
Range("J" & thisrow).Formula = "=if(F" & thisrow & "=""N/A"",""N/A"",F" & thisrow & "-((F" & thisrow & "/100)*G" & thisrow & "))"
End Sub
Sub eadddisc()
Dim thisrow
thisrow = ActiveCell.Row
Range("G" & thisrow).Formula = "=100-((I" & thisrow & "/E" & thisrow & ")*100)"
End Sub
Sub eaddpack()
Dim thisrow
thisrow = ActiveCell.Row
Range("H" & thisrow).Formula = "=D" & thisrow & "-((D" & thisrow & "/100)*G" & thisrow & ")"
End Sub
Sub eaddlength()
Dim thisrow
thisrow = ActiveCell.Row
Range("J" & thisrow).Formula = "=if(F" & thisrow & "=""N/A"",""N/A"",F" & thisrow & "-((F" & thisrow & "/100)*G" & thisrow & "))"
End Sub
Sub ladddisc()
Dim thisrow
thisrow = ActiveCell.Row
Range("G" & thisrow).Formula = "=100-((J" & thisrow & "/F" & thisrow & ")*100)"
End Sub
Sub laddpack()
Dim thisrow
thisrow = ActiveCell.Row
Range("H" & thisrow).Formula = "=D" & thisrow & "-((D" & thisrow & "/100)*G" & thisrow & ")"
End Sub
Sub laddeach()
Dim thisrow
thisrow = ActiveCell.Row
Range("I" & thisrow).Formula = "=E" & thisrow & "-((E" & thisrow & "/100)*G" & thisrow & ")"
End Sub
```

So is it possible to add the check to see if the cell is merged and make it run automatically?

Any help would be much appreciated. Also, if there was a way of condensing this code a little that would be hlepful too!

Thanks!