Referencing two types of sheets - I get Error HELP!!
 

I am referencing two types of named sheet(s) in a work book. One named with
_Bal and the others named _Trial but I get error. What am I doing wrong. All
help totally appreciated. 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
Next
Application.ScreenUpdating = True
End Sub

Referencing two types of sheets - I get Error HELP!!
 

Can you locate the line the error occurs on and what the error is for us?

--
Rick (MVP - Excel)


"Yossy" wrote in message
...
I am referencing two types of named sheet(s) in a work book. One named with
_Bal and the others named _Trial but I get error. What am I doing wrong.
All
help totally appreciated. 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
Next
Application.ScreenUpdating = True
End Sub

Referencing two types of sheets - I get Error HELP!!
 

Notice that you have "with ws" and "with .range("G23")" in the top portion of
the code.

You don't have that in the second portion:

With ws
.Range ("g23")
formStr = .Formula
.Formula = formStr & "+" & ws.Range("g36").Value
End With

So maybe:

With ws
with .Range("g23")
formStr = .Formula
.Formula = formStr & "+" & ws.Range("g36").Value
end with
End With

Yossy wrote:

I am referencing two types of named sheet(s) in a work book. One named with
_Bal and the others named _Trial but I get error. What am I doing wrong. All
help totally appreciated. 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
Next
Application.ScreenUpdating = True
End Sub

--

Dave Peterson

Referencing two types of sheets - I get Error HELP!!
 

it highlights the . Range within this code and gave error as invalid use of
property.

ElseIf InStr(1, ws.Name, "_Trial") Then
With ws
.Range ("g23") ' it highlights the .Range here


"Rick Rothstein" wrote:

Can you locate the line the error occurs on and what the error is for us?

--
Rick (MVP - Excel)


"Yossy" wrote in message
...
I am referencing two types of named sheet(s) in a work book. One named with
_Bal and the others named _Trial but I get error. What am I doing wrong.
All
help totally appreciated. 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
Next
Application.ScreenUpdating = True
End Sub

Referencing two types of sheets - I get Error HELP!!
 

I think Dave has given you what the problem is. Right after the 'With ws'
you have .Range("g23") on the line all by itself... you aren't doing
anything with it. You probably meant the code to look like this (mirroring
how you handled the code structure in the "Then" section)...

With ws
With .Range("g23")
.....

instead of how you posted it.

--
Rick (MVP - Excel)


"Yossy" wrote in message
...
it highlights the . Range within this code and gave error as invalid use
of
property.

ElseIf InStr(1, ws.Name, "_Trial") Then
With ws
.Range ("g23") ' it highlights the .Range here


"Rick Rothstein" wrote:

Can you locate the line the error occurs on and what the error is for us?

--
Rick (MVP - Excel)


"Yossy" wrote in message
...
I am referencing two types of named sheet(s) in a work book. One named
with
_Bal and the others named _Trial but I get error. What am I doing
wrong.
All
help totally appreciated. 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
Next
Application.ScreenUpdating = True
End Sub

Referencing two types of sheets - I get Error HELP!!
 

Thanks to all, added the with and end with and it works. Thankssssss

"Dave Peterson" wrote:

Notice that you have "with ws" and "with .range("G23")" in the top portion of
the code.

You don't have that in the second portion:

With ws
.Range ("g23")
formStr = .Formula
.Formula = formStr & "+" & ws.Range("g36").Value
End With

So maybe:

With ws
with .Range("g23")
formStr = .Formula
.Formula = formStr & "+" & ws.Range("g36").Value
end with
End With

Yossy wrote:

I am referencing two types of named sheet(s) in a work book. One named with
_Bal and the others named _Trial but I get error. What am I doing wrong. All
help totally appreciated. 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
Next
Application.ScreenUpdating = True
End Sub

--

Dave Peterson