Solved: Copy cells + manual input!

[dgt]

Hi all

I have encountered another problem today due to a change of requirements in my workbook.

I currently have a worksheet where Column I contains numeric data. I now need to add an adjacent column (J) where the adjacent cell will equal the contents of Column I by default but also allow for a manual entry to overwrite the copied contents.

In others words J2 = I2 unless manually overwritten.

The range of each column will be from I2 & J2 downwards as there is a header title for each column and the contents of the columns will expand each week, with the addition of new data.

Data validation is not a solution; so I hope this can be solved using VBA. I did a search but nothing came up that matches my requirements.

TIA ...David

[Bob Phillips]

[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "I:I"

On Error GoTo ws_exit

Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

If Target.Value <> "" And Target.Offset(0, 1).Value2 = "" Then

Target.Offset(0, 1).FormulaR1C1 = "=RC[-1]"
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub[/VBA]

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

[dgt]

Xld

Once again thanks for your very quick response, which works in the way that I need allowing for manual entry.

However, in answer to your message that I received:

Are you wanting to automatically create that formula, or will you enter them all?

I would need the formula to be entered automatically to take into account new data entry and to ensure that the cells (Column J) default to the formula in the event of its contents being deleted.

In the long term, I probably need to give this some more thought as it will give me erroneous results in other worksheets, if the manual entry is deleted by mistake.

David

[Bob Phillips]

So does my code do the job?

You could always add extra code to stop column J data being removed.

[dgt]

So does my code do the job?

Not exactly. I have attached a workbook which contains the worksheet from my primary workbook with the code included.

Even if I put in the formulas in Column J manually, once deleted, the cell does not default back to J100-I100 for example. Thinking about it, I'm not sure if it is possible to distinguish between deleting something and manually overwriting the embedded formula.

Do you have any other ideas on this for me?

David

[Bob Phillips]

My code put a formula in column J automatically when I is input/changed.

This should cater for J as well

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE_I As String = "I:I"
Const WS_RANGE_J As String = "J:J"

On Error GoTo ws_exit

Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE_I)) Is Nothing Then

With Target

If Target.Value <> "" And Target.Offset(0, 1).Value2 = "" Then

Target.Offset(0, 1).FormulaR1C1 = "=RC[-1]"
End If
End With

ElseIf Not Intersect(Target, Me.Range(WS_RANGE_J)) Is Nothing Then

With Target

If Target.Value = "" And Target.Offset(0, -1).Value2 <> "" Then

Target.FormulaR1C1 = "=RC[-1]"

End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

[dgt]

xld

I think I know what is causing the problem with copying the data from I to J - which may be due to the fact that Column I is formula based and therefore the data is not actually manualy inputted.

Even when adding a new row to the worksheet, the result in Column I does not get copied to Column J as you can see from the revised attachment.

Is there any way round this?

David

[Bob Phillips]

I don't know what you mean, it seems to work fine for me.

[dgt]

Thats wierd because I can't get it to work for me.

If you look at my last attachment, I have put the code in the correct worksheet and then saved the workbook.

At this point, the cells of Column J should read the same as the cells in Column I. The only way that I could get J2104 to show 504.00 was to overtype the figure in Column I but that must remain as a formula.

I also added a new row at the bottom using the 'double click' code, which duplicates the last row. I then amended the figures in Columns F & G which then alter the contents of H & I (shift F9) both of which contain formulas but still nothing appears in Column J.

I'm very puzzled but don't know the answer, hope you can resolve this.

David

[Bob Phillips]

I see what you are meaning now David.

The code I gave you only puts that formula in when column I is entered and column J is blank. It is event code, happening on a single cell change. It does NOT set the values for all cells in column I initially.

You can pre-load them by inputting =I1187 in J1187 and copying it down. My code can then take over for you.

[dgt]

xld

Sorry but I did something silly. Instead of using =i2 copied down, I just pasted the values into column J, which was causing the hitch.

All is working well now and I would like to thanks you for all your efforts in getting to the solution for my problem.

David