Total to be the same

hendrikbez

Board Regular
Joined
Dec 13, 2013
Messages
94
Office Version
  1. 2021
Platform
  1. Windows
Hi

I have a sheet "sheet1" that is on about 12 rows that all have the same amount in it

0,000213434

So If I change any one of these number in any row, i want all of the others to change to the new number also

if I change 0,00012345 to 0,01234546 all of the 0,000123456 must change to 0,000123456.

This number on the rows is the only number that have a , in it, all my other numbers is like this 0.00000000 without the ,

If this is possible, hos do I do it.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
on about 12 rows that all have the same amount in
Is it always the same rows and columns? If so, what are they?

A fixed range can easily be done in vba (a macro), but if the range is variable then tracking the cells by the format could be difficult.
 
Upvote 0
it is always the same column

A2
A22
A40
A58
A76
A94
A112
A130
A49


There will be new ones later on going down to new rows, but that is it for now.
 
Upvote 0
Is the last one correct, it appears to break from the pattern of the rest?
 
Upvote 0
That's messed up my theory, I was expecting A148 and A4 for the first row.

Apart from the first and last entries, it appears that they are all 18 rows apart which is a pattern that can be followed. Without a consistent pattern of some kind, I don't think that you will be able to do what you want.

A common identifier in another column would help, but the comma instead of dot in the number is not something that can be used.
 
Upvote 0
I can only change the rest of the number to , I cannot use dot, then the rest of my stuff will not work.

I have change from A2 to A4 and a149 to A148, will this
 
Upvote 0
Try this on a copy of your workbook, not the original.

Right click the tab (sheet name) of the sheet that you want this to work on, then click 'View Code'
Copy the code below and paste it into the vba editor.
Close the editor ans save.
Try changing something.
Note that this is set to run on a pattern of 18 rows, so the next row needs to be 166, then 184, 202, etc.
VBA Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
        Dim rw As Long, rws As Long

If Not Intersect(Target, Range("A:A")) Is Nothing Then
    If Target.Count = 1 Then
        If Target.Row Mod 18 = 4 Then
        
            With Application
                .EnableEvents = False
                .Calculation = xlManual
                .ScreenUpdating = False
                
                rw = Cells(Rows.Count, 1).End(xlUp).Row
                
                    For rws = 4 To rw Step 18
                        Cells(rws, 1) = Target.Value
                    Next
            
                .Calculation = xlAutomatic
                .EnableEvents = True
                .ScreenUpdating = True
            End With
        End If
    End If
End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top