Total to be the same

[hendrikbez]

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.

 

[jasonb75]

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.

 

[hendrikbez]

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.

 

[jasonb75]

Is the last one correct, it appears to break from the pattern of the rest?

 

[hendrikbez]

O it should be A149, typing mistake, sorry

 

[jasonb75]

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.

 

[hendrikbez]

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

 

[jasonb75]

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.

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

 

[hendrikbez]

jabsonb75, thank you it is working like it should.