Excel

  Robbo99 08:38 19 Apr 06
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 06

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 06

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

  John B 13:51 19 Apr 06

Same as Whaty!

  holly polly 14:08 19 Apr 06

same as above-hol pol.

  Robbo99 08:47 20 Apr 06

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 06

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

  Robbo99 16:50 07 May 06

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 06

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

  Robbo99 17:13 07 May 06

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 06

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.

Hands-on: Acer Predator Triton 700 review

D&AD Awards 2017: see the best design, advertising, illustration, animation and VR of the past year

How to lose weight with an Apple Watch