CODEWITHSUNDEEP
vbaexcelmacros
Mads Hjorth
Mads Hjorth

Reputation: 447

Macro not executing itself completely

So i have the following macro

Private Sub Worksheet_Change(ByVal Target As Range)

BeginRow = 178
EndRow = 178
ChkCol = 8

For RowCnt = BeginRow To EndRow
If IsError(Sheet1.Cells(RowCnt, ChkCol).Value) Then
 Sheet1.Cells(169, ChkCol).EntireRow.Hidden = True
 Sheet1.Cells(170, ChkCol).EntireRow.Hidden = True
 Sheet1.Cells(171, ChkCol).EntireRow.Hidden = True
 Sheet1.Cells(172, ChkCol).EntireRow.Hidden = True
 Sheet1.Cells(173, ChkCol).EntireRow.Hidden = True
 Sheet1.Cells(174, ChkCol).EntireRow.Hidden = True
 Sheet1.Cells(175, ChkCol).EntireRow.Hidden = True
 Sheet1.Cells(176, ChkCol).EntireRow.Hidden = True
 Sheet1.Cells(177, ChkCol).EntireRow.Hidden = True
 Sheet1.Cells(178, ChkCol).EntireRow.Hidden = True
 Sheet1.Cells(179, ChkCol).EntireRow.Hidden = True
End If
Next RowCnt

For RowCnt = BeginRow To EndRow
If Not IsError(Sheet1.Cells(RowCnt, ChkCol).Value) Then
 Sheet1.Cells(169, ChkCol).EntireRow.Hidden = False
 Sheet1.Cells(170, ChkCol).EntireRow.Hidden = False
 Sheet1.Cells(171, ChkCol).EntireRow.Hidden = False
 Sheet1.Cells(172, ChkCol).EntireRow.Hidden = False
 Sheet1.Cells(173, ChkCol).EntireRow.Hidden = False
 Sheet1.Cells(174, ChkCol).EntireRow.Hidden = False
 Sheet1.Cells(175, ChkCol).EntireRow.Hidden = False
 Sheet1.Cells(176, ChkCol).EntireRow.Hidden = False
 Sheet1.Cells(177, ChkCol).EntireRow.Hidden = False
 Sheet1.Cells(178, ChkCol).EntireRow.Hidden = False
 Sheet1.Cells(179, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End Sub

You could probably do this a lot prettier, but i am quite new to VBA. The formula H178 has the following input:

`=(H170+H171+H172+H173+H174+H175+H176+H177)/7`

However these cells H170, H171 etc. get their data from Sheet2 So i.e. when H170 is an error, H178 gives a #REF! which should automatically mean that the macro is runned, but it doesn't, unless if i double-click on the cell H178 and hit enter.

But if i i.e. change the cell H170 directly on sheet1, to =5/0 (which gives error) then the macro is runned.

What am i doing wrong?

Upvotes: 0

Views: 60

Answers (1)

RGA
RGA

Reputation: 2607

You should place this code in the Worksheet_Calculate event in order to have it recalculate when the error is passed

Upvotes: 1

Related Questions