Fixing the syntax, again

Big Lar · Sep 14, 2015

I have several modules with identical procedures.
I’m attempting to improve processing speed and make the code more “elegant”. So, rather than duplicate this code multiple times, I tried by starting here:

Rich (BB code):

  Sub MacroTest9()
  Dim ws = Worksheet 
  Application.ScreenUpdating = False
   
  If Sheets("Settings").[A49] = 0 Then
  Exit Sub
  Else
   
  ‘I’ve added this is the revised code to loop through the worksheets
  For Each ws In Sheets(Array("G1", "G2", "G3"))
   
  ws.Visible = True
  ws.Select
  ws.Unprotect
   
  With ws
   
      .Range("BH4:BH21").ClearContents
   
  ‘I’ve attempted to change this:
  If Sheets("G1").[AI1] <> "x" Then
      temp = Application.Match(Sheets("G1").[AI1], Sheets("G1").[B3].Resize(129), 0)
      Sheets("G1").[BH4] = Application.Index(Sheets("G1").[A3].Resize(129), temp)
      Else
      Sheets("G1").[BH4] = ""
  End If
   
  ‘to this but…haven’t got the proper syntax.  Getting “Expected End of Statement” errors
  If Range.[AI1] <> "x" Then
      temp = Application.MatchRange.[AI1], Range.[B3].Resize(129), 0)
      Range.[BH4] = Application.IndexRange.[A3].Resize(129), temp)

      Else
      Range.[BH4] = ""

  End If
  ‘More procedure follows

The syntax is incorrect and I’m tripped up on how to fix it. Can someone provide some insight and assistance with this?

RickXL · Sep 14, 2015

Hi,

Not tested but this compiles:

Code:

Sub MacroTest9()
    Dim ws As Worksheet
    Dim temp As Variant
    
    Application.ScreenUpdating = False
    
    If Sheets("Settings").[A49] = 0 Then Exit Sub
    
    'I’ve added this is the revised code to loop through the worksheets
    For Each ws In Sheets(Array("G1", "G2", "G3"))
        With ws
            .Visible = True
            .Select
            .Unprotect
            .Range("BH4:BH21").ClearContents
            
            'I’ve attempted to change this:
            If Sheets("G1").[AI1] <> "x" Then
                temp = Application.Match(Sheets("G1").[AI1], Sheets("G1").[B3].Resize(129), 0)
                Sheets("G1").[BH4] = Application.Index(Sheets("G1").[A3].Resize(129), temp)
            Else
                Sheets("G1").[BH4] = ""
            End If
            
            'to this but…haven’t got the proper syntax.  Getting “Expected End of Statement” errors
            If .[AI1] <> "x" Then
                temp = Application.Match(.[AI1], .[B3].Resize(129), 0)
                .[BH4] = Application.Index(.[A3].Resize(129), temp)
            Else
                .[BH4] = ""
            End If
            
            'More procedure follows

Big Lar · Sep 14, 2015

RickXL,
Thank you very much!
With your help I was able to eliminate at least 432 lines of duplicate code from my procedure!
I'm looking and expect I'll find another 400.
Sweet!

RickXL · Sep 14, 2015

Great news - thanks for the feedback.

Regards,

Fixing the syntax, again

Big Lar

Well-known Member

Excel Facts

RickXL

MrExcel MVP

Big Lar

Well-known Member

RickXL

MrExcel MVP

Similar threads

Forum statistics

Share this page

Fixing the syntax, again

Big Lar

Well-known Member

Excel Facts

RickXL

MrExcel MVP

Big Lar

Well-known Member

RickXL

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock