Excel

  Robbo99 08:38 19 Apr 2006
Locked

I have seven columns, mon tue etc and 1 column called Balance.
I need to type data in mon and the same data appear in column balance then I want to type data in tues and want it to over write the column balance with tue data. leaving the mon column intact. I want to do the same with the rest of the week

Robbo

  VoG II 08:46 19 Apr 2006

Right click the sheet tab, View Code. Paste in the following

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim iRow
iRow = Target.Row
Cells(iRow, 8) = Target.Value
End Sub

  Whaty 12:43 19 Apr 2006

Just like to keep these bit of code for future ref.... :-)

  John B 13:51 19 Apr 2006

Same as Whaty!

  holly polly 14:08 19 Apr 2006

same as above-hol pol.

  Robbo99 08:47 20 Apr 2006

Thanks Vog, is it possible to build on this ie use this code to make it do 2 things.
Mon tue wed Balance leaving this balance intact after wed and then thu fri sat sun balance intact.
Dave

  VoG II 10:44 20 Apr 2006

Sorry, I don't understand. Can you explain in more detail please.

  Robbo99 16:50 07 May 2006

Sorry it has taken a while to get back to you - i've been away.
I want to divide the week into two sections - the first being Mon,Tues, Wed and then a balance column. The balance will always be the last figure that was entered. The formula you gave does exactly what I want for this first section of the week.

Then I want to leave this as it is, but do the same thing for the second half of the week i.e. Thur, Fri, Sat, Sun and then balance.

  VoG II 17:09 07 May 2006

So you mean that you will have 2 balance columns (H and I)?

  Robbo99 17:13 07 May 2006

Yes, the first balance column will be after Mon, Tue, Wed and the second balance column will be after Thur, Fri, Sat, Sun.

  VoG II 17:15 07 May 2006

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim iRow, iCol
iRow = Target.Row
iCol = Target.Column
If iCol >= 1 And iCol <= 3 Then
Cells(iRow, 8) = Target.Value
ElseIf iCol >= 4 And iCol <= 7 Then
Cells(iRow, 9) = Target.Value
End If
End Sub

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Best of the Grad Shows 2017: University of the West of England (UWE)

Best value Mac: Which is the best £1249 Mac to buy

Les meilleures GoPro 2017