Reference two types of named sheet in a code

[Yossy]

how do I reference two types of multiple sheet in a code. I have sheets with
name _Balance adding up ranges and I want to reference another sheet named
_Trial to add different range together. Code sample

Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Worksheets
'this calculate on only sheets named _Balances
If InStr(1, ws.Name, "_Balances") Then
With ws
..Range("g35").Value = .Range("g90").Value
With .Range("g123")
formStr = .Formula
..Formula = formStr & "+" & ws.Range("g76").Value
End With

I want to do a different calculation for some other sheet with named _Trial.
So for sheet named _Trial I want to add range g39 to g20. I used an Elseif
InStr(1, ws.Name, "_Trial") after If InStr(1, ws.Name, "_Balances") but I get
error. Please help me figure this out. THanks for Helping....

 

[Bob Phillips]

This should work

Dim ws As Worksheet

Application.ScreenUpdating = False
For Each ws In Worksheets
'this calculate on only sheets named _Balances
If InStr(1, ws.Name, "_Balances") Then

With ws
.Range("g35").Value = .Range("g90").Value
With .Range("g123")
formStr = .Formula
.Formula = formStr & "+" & ws.Range("g76").Value
End With
ElseIf InStr(1, ws.Name, "_Trial") Then

'do something else
End If

 

[Yossy]

Bob this is what I did and I get error. Help me out, thanks

Sub T_Bal()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Worksheets
If InStr(1, ws.Name, "_Bal") Then
With ws
.Range("g35").Value = .Range("g40").Value
With .Range("g23")
formStr = .Formula
.Formula = formStr & "+" & ws.Range("g36").Value
End With
With .Range("g30")
formStr = .Formula
.Formula = formStr & "+" & ws.Range("g57").Value
End With
.Range("g57").Value = 0
End With
ElseIf InStr(1, ws.Name, "_Trial") Then
With ws
With .Range("g23")
formStr = .Formula
.Formula = formStr & "+" & ws.Range("g36").Value
End With
End If
End If
Next
Application.ScreenUpdating = True
End Sub

 

[Yossy]

Bob this is what I did and I get error. Help me out, thanks

Sub T_Bal()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Worksheets
If InStr(1, ws.Name, "_Bal") Then
With ws
.Range("g35").Value = .Range("g40").Value
With .Range("g23")
formStr = .Formula
.Formula = formStr & "+" & ws.Range("g36").Value
End With
With .Range("g30")
formStr = .Formula
.Formula = formStr & "+" & ws.Range("g57").Value
End With
.Range("g57").Value = 0
End With
ElseIf InStr(1, ws.Name, "_Trial") Then
With ws
.Range("g23")
formStr = .Formula
.Formula = formStr & "+" & ws.Range("g36").Value
End With
End If
End If
Next
Application.ScreenUpdating = True
End Sub

 

[Bob Phillips]

You seem to have one too many End If statements

Sub T_Bal()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Worksheets
If InStr(1, ws.Name, "_Bal") Then
With ws
.Range("g35").Value = .Range("g40").Value
With .Range("g23")
formStr = .Formula
.Formula = formStr & "+" & ws.Range("g36").Value
End With
With .Range("g30")
formStr = .Formula
.Formula = formStr & "+" & ws.Range("g57").Value
End With
.Range("g57").Value = 0
End With
ElseIf InStr(1, ws.Name, "_Trial") Then
With ws
.Range ("g23")
formStr = .Formula
.Formula = formStr & "+" & ws.Range("g36").Value
End With
End If
Next
Application.ScreenUpdating = True
End Sub