VBA Syntax Error

S

Steve1970

New Member
Joined
Feb 6, 2021
Messages
10
Office Version
  1. 365
  2. 2019
Platform
  1. MacOS
Hi, trying to understand what is causing the below Syntax error below.

If anyone could advise that would be greatly appreciated.

I've put the affected code in bold and where the error is referenced in red.

Rich (BB code): 
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
   
    If Target.Address = "$G$15" Then
        Dim ReceivingReportsChannels As Range
        Set ReceivingReportsChannels = Range("G15")
   
        If ReceivingReportsChannels = "N/A" Then
            Range("G17:G18").Value = "N/A"
            Rows("17:24").EntireRow.Hidden = True
        Else
            Rows("17:24").EntireRow.Hidden = False
            Range("G17:G18").Value = ""
        End If
       
    ElseIf Target.Address = "$G$26" Then
        Dim ReceivingReportsStakeholders As Range
        Set ReceivingReportsStakeholders = Range("G26")

        If ReceivingReportsStakeholders = "N/A" Then
            Range("G28:G31").Value = "N/A"
            Rows("28:37").EntireRow.Hidden = True
        Else
            Rows("28:37").EntireRow.Hidden = False
            Range("G28:G31").Value = ""
        End If
      
    ElseIf Target.Address = "$G$39" Then
        Dim ReceivingReportsOnlinePortal  As Range
        Set ReceivingReportsOnlinePortal = Range("G39")
   
        If ReceivingReportsOnlinePortal = "N/A" Then
            Range("G41:G62").Value = "N/A"
            Rows("41:68").EntireRow.Hidden = True
        Else
            Rows("41:68").EntireRow.Hidden = False
            Range("G41:G62").Value = ""
        End If
      
    ElseIf Target.Address = "$G$70" Then
        Dim ReceivingReportsThirdPartyOnlinePortal As Range
        Set ReceivingReportsThirdPartyOnlinePortal = Range("G70")
   
        If ReceivingReportsThirdPartyOnlinePortal = "N/A" Then
            Range("G72:G95").Value = "N/A"
            Rows("72:101").EntireRow.Hidden = True
        Else
            Rows("72:101").EntireRow.Hidden = False
            Range("G72:G95").Value = ""
        End If
      
    ElseIf Target.Address = "$G$103" Then
        Dim ReceivingReportsInternalIVR As Range
        Set ReceivingReportsInternalIVR = Range("G103")
   
        If ReceivingReportsInternalIVR = "N/A" Then
            Range("G105:G129").Value = "N/A"
            Rows("105:135").EntireRow.Hidden = True
        Else
            Rows("105:135").EntireRow.Hidden = False
            Range("G105:G129").Value = ""
        End If
   
    ElseIf Target.Address = "$G$137" Then
        Dim ReceivingReportsExternalIVR As Range
        Set ReceivingReportsExternalIVR = Range("G137")
   
        If ReceivingReportsExternalIVR = "N/A" Then
            Range("G139:G166").Value = "N/A"
            Rows("139:172").EntireRow.Hidden = True
        Else
            Rows("139:172").EntireRow.Hidden = False
            Range("G139:G166").Value = ""
        End If
   
    ElseIf Target.Address = "$G$174" Then
        Dim ReceivingReportsPostal As Range
        Set ReceivingReportsPostal = Range("G174")
   
        If ReceivingReportsPostal = "N/A" Then
            Range("G176:G182").Value = "N/A"
            Rows("176:188").EntireRow.Hidden = True
        Else
            Rows("176:188").EntireRow.Hidden = False
            Range("G176:G182").Value = ""
        End If
   
    ElseIf Target.Address = "$G$190" Then
        Dim ReceivingReportsAppAvailable As Range
        Set ReceivingReportsAppAvailable = Range("G190")
   
        If ReceivingReportsAppAvailable = "N/A" Then
            Range("G192:G195”,”G197:G218").Value = "N/A"
            Rows("192:224").EntireRow.Hidden = True
        Else
            Rows("192:224").EntireRow.Hidden = False
            Range("G192:G195”,”G197:G218").Value = ""
        End If
   
    ElseIf Target.Address = "$G$226" Then
        Dim ReceivingReportsIVRAvailable As Range
        Set ReceivingReportsIVRAvailable = Range("G226")
   
        If ReceivingReportsIVRAvailable = "N/A" Then
            Range("G228:G229”,”G230:G250").Value = "N/A"
            Rows("228:256").EntireRow.Hidden = True
        Else
            Rows("228:256").EntireRow.Hidden = False
            Range("G228:G229”,”G230:G250").Value = ""
        End If
   
    ElseIf Target.Address = "$G$258" Then
        Dim ReceivingReportsSLA As Range
        Set ReceivingReportsSLA = Range("G258")
    
        If ReceivingReportsSLA = "N/A" Then
            Range("G260:G264”).Value = "N/A"
            Rows("260:270").EntireRow.Hidden = True
        Else
            Rows("260:270").EntireRow.Hidden = False
            Range("G260:G264”).Value = ""
        End If
   
    ElseIf Target.Address = "$G$272" Then
        Dim AssessingDocumented As Range
        Set AssessingDocumented = Range("G272")
   
        If AssessingDocumented = "N/A" Then
            Range("G274:G275").Value = "N/A"
            Rows("274:281").EntireRow.Hidden = True
        Else
            Rows("274:281").EntireRow.Hidden = False
            Range("G274:G275").Value = ""
        End If

    ElseIf Target.Address = "$G$283" Then
        Dim AssessingDocumentedTriage As Range
        Set AssessingDocumentedTriage = Range("G283")
   
        If AssessingDocumentedTriage = "N/A" Then
            Range("G285:G297").Value = "N/A"
            Rows("285:303").EntireRow.Hidden = True
        Else
            Rows("285:303").EntireRow.Hidden = False
            Range("G285:G297").Value = ""
        End If

    ElseIf Target.Address = "$G$305" Then
        Dim AssessingInvestigationProcess As Range
        Set AssessingInvestigationProcess = Range("G305")
   
        If AssessingInvestigationProcess = "N/A" Then
            Range("G307:G313").Value = "N/A"
            Rows("307:319").EntireRow.Hidden = True
        Else
            Rows("307:319").EntireRow.Hidden = False
            Range("G307:G313").Value = ""
        End If

    ElseIf Target.Address = "$G$321" Then
        Dim AddressingReportsWrongdoing As Range
        Set AddressingReportsWrongdoing = Range("G321")
   
        If AddressingReportsWrongdoing = "N/A" Then
            Range("G323:G346").Value = "N/A"
            Rows("323:352").EntireRow.Hidden = True
        Else
            Rows("323:352").EntireRow.Hidden = False
            Range("G323:G346").Value = ""
        End If

    ElseIf Target.Address = "$G$354" Then
        Dim AddressingWhistleblowerProtection As Range
        Set AddressingWhistleblowerProtection = Range("G354")
   
        If AddressingWhistleblowerProtection = "N/A" Then
            Range("G356:G363").Value = "N/A"
            Rows("356:369").EntireRow.Hidden = True
        Else
            Rows("356:369").EntireRow.Hidden = False
            Range("G356:G363").Value = ""
        End If

    ElseIf Target.Address = "$G$371" Then
        Dim AddressingDetrimentalConduct As Range
        Set AddressingDetrimentalConduct = Range("G371")
   
        If AddressingDetrimentalConduct = "N/A" Then
            Range("G373:G378").Value = "N/A"
            Rows("373:384").EntireRow.Hidden = True
        Else
            Rows("373:384").EntireRow.Hidden = False
            Range("G373:G378").Value = ""
        End If

ElseIf Target.Address = "$G$386" Then
        Dim AddressingReportProtection As Range
        Set AddressingReportProtection = Range("G386")
   
        If AddressingReportProtection = "N/A" Then
            Range("G388:G397").Value = "N/A"
            Rows("388:403").EntireRow.Hidden = True
        Else
            Rows("388:403").EntireRow.Hidden = False
            Range("G388:G397").Value = ""
        End If

ElseIf Target.Address = "$G$405" Then
        Dim AddressingWhistleblowingCases As Range
        Set AddressingWhistleblowingCases = Range("G405")
   
        If AddressingWhistleblowingCases = "N/A" Then
            Range("G407:G412").Value = "N/A"
            Rows("407:418").EntireRow.Hidden = True
        Else
            Rows("407:418").EntireRow.Hidden = False
            Range("G407:G412").Value = ""
        End If

ElseIf Target.Address = "$G$420" Then
        Dim ConcludingWhistleblowingCases As Range
        Set ConcludingWhistleblowingCases = Range("G420")
   
        If ConcludingWhistleblowingCases = "N/A" Then
            Range("G422:G438").Value = "N/A"
            Rows("422:444").EntireRow.Hidden = True
        Else
            Rows("422:444").EntireRow.Hidden = False
            Range("G422:G438").Value = ""
        End If
End If
   
    Application.EnableEvents = True
End Sub
 
Last edited by a moderator:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
The quote marks after each range address are wrong they should be the same as those at the start.
 
Upvote 0
Solution
Fluff said:
The quote marks after each range address are wrong they should be the same as those at the start.
Click to expand...
Thx Fluff

That seems to have sorted it, but I've since pulled up another issue, again with the two ranges. I think I must have written this wrong. I've got two specific ranges that I'm looking to apply, but I've written it incorrectly.


ElseIf Target.Address = "$G$190" Then

Dim ReceivingReportsAppAvailable As Range

Set ReceivingReportsAppAvailable = Range("G190")


If ReceivingReportsAppAvailable = "N/A" Then

Range("G192:G195”,”G197:G218").Value = "N/A"

Rows("192:224").EntireRow.Hidden = True

Else

Rows("192:224").EntireRow.Hidden = False

Range("G192:G195”,”G197:G218").Value = ""

End If
 
Upvote 0
You still have some slanted quote marks instead of straight ones:
Range("G192:G195”,”G197:G218")
 
Upvote 0
Joe4 said:
You still have some slanted quote marks instead of straight ones:
Range("G192:G195”,”G197:G218")
Click to expand...
Joe4 said:
You still have some slanted quote marks instead of straight ones:
Range("G192:G195”,”G197:G218")
Click to expand...
Thanks Joe. Between you and Fluff, I've managed to get it all working. Thanks for your time, its greatly appreciated.
 
Upvote 0
You are welcome.
Glad we could help!

I marked as Fluff's reply as the solution, as he said it first, and I was just reiterating what he said.
 
Upvote 0
Joe4 said:
You are welcome.
Glad we could help!

I marked as Fluff's reply as the solution, as he said it first, and I was just reiterating what he said.
Click to expand...
Thanks Joe, I’m quite new to the Forum, so wasn’t sure. Fluff’s reply certainly got me on my way, so thank you for amending the response.
KR, Steve
 
Upvote 0
You must log in or register to reply here.

Similar threads

imback2nite
  • Solved
VBA help.
2
Replies
10
Views
401
Peter_SSs
Peter_SSs
R
  • Solved
Hide rows according to checkboxes on seperate sheet
Replies
8
Views
172
John_w
J
H
  • Solved
Amend the VBA code to Filter Columns Instead of Rows
Replies
9
Views
201
Akuini
Akuini
J
  • Question
VBA Macro for hiding and unhiding rows is rearranging cells
2
Replies
14
Views
1K
dmt32
D
N
  • Question
ActiveX Checkbox fails after sheetname change
Replies
4
Views
174
bobsan42
bobsan42

Forum statistics

Threads
1,215,669
Messages
6,126,125
Members
449,293
Latest member
yallaire64

Share this page

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