VBA: automatic recalculation if a value is typed in a cell

[Nelson78]

Hello everybody.

I'm attempting to define an Excel area in which, in case of human typing, a macro has to run in order to recalculate automatically values in other cells.

The process suggested by Microsoft, until a certain point, is ok.

https://support.microsoft.com/en-us...un-a-macro-when-certain-cells-change-in-excel

It defines the area in this way:

Set KeyCells = Range("A1:C10")

The problem arises if I have to define an area with variables.

In my case, a previous macro runs in a blank sheet producing an outcome that I cannot know a priori.

I mean: at the end of the macro, the area I've to set is something like this:

for each cell in range B1 and B & lr (last populated row)
if the value of the cell is the word "CODE" than the area I've to set is from cell.offset(0, 2) to the column z of the same row.

Any suggestion?

 

[mumps]

Can there be more that one occurrence of the word "Code" in column B? If so, how do you decide which row you want to use a s a reference?

 

[Nelson78]

Can there be more that one occurrence of the word "Code" in column B? If so, how do you decide which row you want to use a s a reference?

Yes, there can be more then one (at the moment, put in practical terms, about 10).
Any of them has to undergo the same process (not just one).

 

[mumps]

I'm not sure if I understood properly, but see if this helps:

Sub Test()
    Application.ScreenUpdating = False
    Dim bottomB As Long
    bottomB = Range("B" & Rows.Count).End(xlUp).Row
    Dim rng As Range, KeyCells As Range
    For Each rng In Range("B1:B" & bottomB)
        If rng = "Code" Then
            Set KeyCells = Range("D" & rng.Row & ":Z" & rng.Row)
            'code to do your recalculating here
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[Nelson78]

I'm not sure if I understood properly, but see if this helps:

Sub Test()
    Application.ScreenUpdating = False
    Dim bottomB As Long
    bottomB = Range("B" & Rows.Count).End(xlUp).Row
    Dim rng As Range, KeyCells As Range
    For Each rng In Range("B1:B" & bottomB)
        If rng = "Code" Then
            Set KeyCells = Range("D" & rng.Row & ":Z" & rng.Row)
            'code to do your recalculating here
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

I cannot understand if you're understood (if not, absolutely my bad).

Anyway, I've reported the code in the sheet tab.
Then, when you write

[B]'code to do your recalculating here[/B]

I've tried to implement the following code, meaning keycells as one of the cell in which a manual change of value has been done.

keycells.Offset(1, 0).Value = keycells.Offset(-1, 0).Value / keycells.Value

But it is clear I've not identified it in the correct way (error 'Run-time error '13': Type mismatch')

 

[mumps]

Can you post a screen shot of what your data looks like? Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html Alternately, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[Nelson78]

Can you post a screen shot of what your data looks like? Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html Alternately, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

Ok, I figured it out exploiting your instructions in post #4 .
Thank you very much.

 

[mumps]

You are very welcome.