ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Reference two types of named sheet in a code (https://www.excelbanter.com/excel-programming/422485-reference-two-types-named-sheet-code.html)

Yossy
Reference two types of named sheet in a code
 
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[_3_]
Reference two types of named sheet in a code
 
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


--
__________________________________
HTH

Bob

"Yossy" wrote in message
...
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....



Yossy
Reference two types of named sheet in a code
 
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

"Bob Phillips" wrote:

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


--
__________________________________
HTH

Bob

"Yossy" wrote in message
...
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....




Yossy
Reference two types of named sheet in a code
 
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" wrote:

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


--
__________________________________
HTH

Bob

"Yossy" wrote in message
...
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[_3_]
Reference two types of named sheet in a code
 
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



--
__________________________________
HTH

Bob

"Yossy" wrote in message
...
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" wrote:

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


--
__________________________________
HTH

Bob

"Yossy" wrote in message
...
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....


All times are GMT +1. The time now is 05:48 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com