Automatically copying contents of cell

Robert E Lee · May 20, 2011

Hi all

I would like to be able to copy a cell to a particular range of cells in the same row based on its contents, specifically, if Column B contains the word "void" I would like the next 15 cells in that row to contain the same word.

I know that this can be done pretty simply using VBA, but would like to avoid this approach. I wondered if there was an element in conditional formatting that would enable me to define the word "void" as a pattern but can't see such an option

thanks

Robert

Akashwani · May 20, 2011

Hi Robert,

This probably isn't the best solution but it appears to work and if nothing else it has bumped you back up

Excel Workbook

B

C

D

E

1

Text

Result1

Result2

Result3

2

In a void

Void

3

The Voiid

*

4

Voided

Void

5

Big Void

Void

6

Void Droid

Void

7

No Voiid here

*

Sheet1

The formula in C2 will not work pre Excel2007.
The formula in C3 will (I hope)

Copy the formula across and down as far as is required.

I hope that helps.

Ak

Robert E Lee · May 21, 2011

Hi Ak

thanks for your suggestion. The problem with a formula is that it would have to be entered in a cell that may contain other information. If the formula could be adapted to be entered outside the body of data, perhaps using OFFSET that might work

Any thoughts?

Robert

Akashwani · May 21, 2011

Hi Robert,

If I understand you correctly, you can put those formulas in any column you like.

Excel Workbook

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

1

Text

*

Result1

Result2

Result3

2

In a void

*

Void

3

The Voiid

*

4

Voided

*

Void

5

Big Void

*

Void

6

Void Droid

*

Void

7

No Voiid here

*

Sheet1

Or on any sheet....

Excel Workbook

D

E

F

G

11

Text from Sheet1

Result1

Result2

Result3

12

In a void

Void

*

13

The Voiid

*

14

Voided

Void

15

Big Void

Void

16

Void Droid

Void

17

No Voiid here

*

Sheet2

Ak

Peter_SSs · May 21, 2011

Robert, the only two things I can think of that might be some use are Conditional Formatting. If the column B value is "void" you could CF the other cells in the row to ..

a) Font colour the same as background (white?) so the cells just appear blank, or

b) Strikethrough font so those other values are still visible but appear 'crossed out'

Peter_SSs · May 21, 2011

Unless the other cells contain formulas? If this was so you could replace the formulas with something like ..

=IF($B2="void",$B2,existing_formula)

Robert E Lee · May 30, 2011

Hi Peter & Ak

thanks for your suggestions, I apologise for not replying sooner but have been away for a short holiday.

Ak, a formula based solution will not work as the cells will have to contain either relevant information or "VOID".

Peter I have CF'd the relevant cells but would still like "VOID" to be in those cells.

I think the best way would be to call a macro for each cell entry, something like

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Value = "VOID" Then
        Call myMacro
    End If
End Sub

myMacro should then do the necessary.

However, whilst the macro works when called manually, the code attached to the worksheet does not call it.

Can you see where I am going wrong?

Regards

Robert

Peter_SSs · May 30, 2011

Robert

I have a few comments about your code.

1. If you type "void" in a cell, your code will not call the macro because your code is case-sensitive. If you want the code to call the macro for "void", "Void", "VOID" etc then see the UCase() function in my code below.

2. If you type "VOID" in a cell and the macro is still not being called by your current code then, assuming you have the code in the correct place (in the worksheet module not a standard module), it is likely that your 'Events' (eg Worksheet_Change) are disabled. In the VBA window, ensure the Immediate Window is visible (View|Immediate Window) and then on a new line in the Immediate Window, type
Application.EnableEvents=True and press Enter
Now go back to your sheet and try typing "VOID" in a cell.

2. Your code is not robust. It would be a rare worksheet that never had more than one cell changed at a time. Your code will error if that happens. With your current code in place and once you are sure 'events' are enabled (see previous point), select any 2 cells, even empty ones, and press the Delete key. You will see that your code errors. It does so because Target.Value fails if Target consists of multiple cells (after all, the Target cells could all have different values). My code shows one way to deal with that situation.

3. We haven't seen your 'myMacro' code but I assume it is placing "VOID" in some cells. Unless you have disabled events in your myMacro code, doing that will trigger your Worksheet_Change event meaning it will get into a loop continually adding "VOID" to cells and re-calling itself. If you have disabled/enabled events in myMacro then this bit should be okay. Otherwise, you could just disable/enable them once in the Worksheet_Change code as I have done below.

4. Is "VOID" likely to occur in any cell(s) in your whole worksheet? Or is it only like to be in a restricted range (eg particular columns)? If there is any restriction then it might be better to also include something in the Worksheet_Change code to check if 'Target' is in (or partly in) the relevant range. For example, if "VOID" is only likely to be in, say, columns B:E and somebody pastes 1,000 cells into column J then it would be pointless looping through the Worksheet_Change loop 1,000 times doing nothing each time through. Post back with details of the possible "VOID" range if you want help with how to include such a check.

Anyway, after you have investigated the above points, try replacing your code with this and re-investigate.

 Private Sub Worksheet_Change(ByVal Target As Range) Dim rCell As Range Application.EnableEvents = False For Each rCell In Target If UCase(rCell.Value) = "VOID" Then Call myMacro End If Next rCell Application.EnableEvents = True End Sub

Robert E Lee · May 31, 2011

Peter

thanks for that exhaustive explanation. I have copied the code into the appropriate place and added the myMacro process to that code, rather than calling the macro, which was only putting "VOID" into the next 15 cells on the same row, so adding a line of code to your code seems sensible.This all seems to work perfectly

The code I have used is

Code:

Range(activecell.Offset(-1, 1), activecell.Offset(-1, 15)) = "VOID"

The only column in which "VOID" would be entered is column B so any direction you could give with that in mind would be welcome.

Thanks for all your time

Robert

Peter_SSs · May 31, 2011

Robert E Lee said:
Peter

thanks for that exhaustive explanation. I have copied the code into the appropriate place and added the myMacro process to that code, rather than calling the macro, which was only putting "VOID" into the next 15 cells on the same row, so adding a line of code to your code seems sensible.This all seems to work perfectly

The code I have used is

Code:

Range(activecell.Offset(-1, 1), activecell.Offset(-1, 15)) = "VOID"

The only column in which "VOID" would be entered is column B so any direction you could give with that in mind would be welcome.

Thanks for all your time

Robert

Robert

Putting a single line of code into the event code makes sense to me. However, not the particular line you have used as again it is not robust. Using ActiveCell is dangerous in this citcumstance. Dangerous in terms of not getting the result you want I mean.

I think you have written it on the basis that 'VOID' would be entered in a single cell and confirmed by pressing Enter, moving the active cell down one row. However, that is not the only way to enter values. With that line still in place, try these ..

a) Select B30 and type VOID but confirm with the Tab key, not Enter.

b) Select B33 and type VOID follwed by the up arrow, not the Enter key.

c) Select B34 and type VOID, don't press the Enter key but select cell E40 with the mouse.

d) Select B33:B34 (both cell should contain VOID from b & c above, copy them. Select cell B43 and Paste.

Now try replacing that line of code with

Code:

rCell.Offset(, 1).Resize(, 15).Value = "VOID"

Consider removing the all values generated with the above examples and try repeating the exercises.

Now to focus on column B, here is some new code. After replacing the old code with this, try entering VOID in different ways in column B and also try entering VOID in other columns just to confirm that nothing happens in that case.

 Private Sub Worksheet_Change(ByVal Target As Range) Dim rCell As Range, Changed As Range Set Changed = Intersect(Target, Columns("B")) If Not Changed Is Nothing Then Application.EnableEvents = False For Each rCell In Changed '<- Note this line has changed as well If UCase(rCell.Value) = "VOID" Then rCell.Offset(, 1).Resize(, 15).Value = "VOID" End If Next rCell Application.EnableEvents = True End If End Sub

Automatically copying contents of cell

Robert E Lee

Active Member

Excel Facts

Akashwani

Well-known Member

Robert E Lee

Active Member

Akashwani

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Peter_SSs

MrExcel MVP, Moderator

Robert E Lee

Active Member

Peter_SSs

MrExcel MVP, Moderator

Robert E Lee

Active Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Automatically copying contents of cell

Active Member

Excel Facts

Well-known Member

Active Member

Well-known Member

MrExcel MVP, Moderator

MrExcel MVP, Moderator

Active Member

MrExcel MVP, Moderator

Active Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock