Hide row error on worksheet calculate event

[mandalocascio]

[FONT=&quot]Hello,[/FONT]
[FONT=&quot]I have a hide row on a calculate event which is partially working when it is above and below a given value. [/FONT]
[FONT=&quot]1. H57 is a calculation with an error message [/FONT]
[FONT=&quot]=IFERROR(SUM(G41:G51,G54:G55)/SUM(E41:E51,E54:E55),"please complete all sections")[/FONT]
[FONT=&quot]2. my code [/FONT]

[COLOR=#333333][FONT=&quot]Private Sub Worksheet_Calculate()
If Me.Range("H57").Value > 2.5 Then
        Rows("61:72").EntireRow.Hidden = True
    Else
  
 If Me.Range("H57").Value < 2.4 Then
        Rows("61:72").EntireRow.Hidden = False

End If
    End If
End Sub[/FONT][/COLOR]

[FONT=&quot]Problem 1 - it is only working when i hit play in the VBA window ie not dynamically as it should on a calculate event? [/FONT]
[FONT=&quot]Problem 2 - as the person is working through the sheet ie H57 = "please complete all sections" - when the cell has this text the cells are also hidden, so i need to include that in the event.[/FONT]
[FONT=&quot]Thanks, Amanda [/FONT]

 

[myall_blues]

Problem 1:
Where are you putting the code? It needs to go in the calculate event for the specific worksheet, not as a separate module.
i.e. in the VBA editor, in the VBA project window on the left hand side, right click on the relevant Sheet (e.g. Sheet 1) and select 'View Code". Then paste your code in the resultant window. In the dropdown at the top, left it should say 'Worksheet', in the right hand dropdown it should say 'Calculate'.

Problem 2: I don't understand the question sorry - can you elaborate?

Not sure if it's an issue, but your code should allow for the eventuality of H57=2.5, or indeed any value between 2.4 and 2.5 inclusive. This might be as simple as:
If Me.Range("H57").Value >= 2.5 Then and
If Me.Range("H57").Value < 2.5

Regards

Murray

 

[mandalocascio]

Hello thanks for responding ! sorry sent to soon !

1. yes it is in the VBA editor i cant paste the image to show you as I dont have permission. but it is as you said ie Worksheet and calculate. Not sure what else it can be. Ive changed as suggested ie to include = 2.5 thanks - that was a bit silly!

i'll try again -

2a. H57 is a calculated cell ie its a weighted average ie SUM(G41:G51,G54:G55)/SUM(E41:E51,E54:E55).

Column G = score X weight - which come from Vlook up value from column D X weight (importance) which comes from Column E (manually entered)

so if the user has not yet made a decision ie hasn't decided yet on a weight then the result in H57 is a #Value! which is why i have put an IFERROR

=IFERROR(SUM(G41:G51,G54:G55)/SUM(E41:E51,E54:E55),"please complete all sections")

2b. I need to include that in the hidden.rows event properly because at the moment if the user has not completed all sections ie the sum is not possible and i hot run on the vba editor window the rows are hidden

 

[mandalocascio]

Hello thanks for responding ! sorry sent to soon !

1. yes it is in the VBA editor i cant paste the image to show you as I dont have permission. but it is as you said ie Worksheet and calculate. Not sure what else it can be. Ive changed as suggested ie to include = 2.5 thanks - that was a bit silly!

i'll try again -

2a. H57 is a calculated cell ie its a weighted average ie SUM(G41:G51,G54:G55)/SUM(E41:E51,E54:E55).

Column G = score X weight - which come from Vlook up value from column D X weight (importance) which comes from Column E (manually entered)

so if the user has not yet made a decision ie hasn't decided yet on a weight then the result in H57 is a #Value! which is why i have put an IFERROR

=IFERROR(SUM(G41:G51,G54:G55)/SUM(E41:E51,E54:E55),"please complete all sections")

2b. I need to include that in the hidden.rows event properly because at the moment if the user has not completed all sections ie the sum is not possible and i hot run on the vba editor window the rows are hidden

its been solved here

https://answers.microsoft.com/en-us...-aa28-4ee1-8878-396dc94dd5d5?tm=1483492544842