Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Reference two types of named sheet in a code
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 127
Default 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....
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,420
Default 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....


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 127
Default 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....



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 127
Default 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....



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,420
Default 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....





Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Sheet won't reference a named cell Mike H Excel Worksheet Functions 2 September 9th 09 08:28 PM
Formatting different zip code types within a column prizm Excel Discussion (Misc queries) 4 May 30th 07 02:45 PM
vb code for renaming a work sheet with a cell reference John Britto Excel Discussion (Misc queries) 3 September 17th 06 07:12 PM
See code enclosed - Convert to formulas with absolute reference inculding the sheet references! Maria J-son Excel Programming 0 May 10th 05 08:40 AM
Reference Addin Module from sheet code zSplash Excel Programming 2 April 19th 04 06:35 PM


All times are GMT +1. The time now is 12:16 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement