VBA Help

  • Thread starter Thread starter dipsy
  • Start date Start date
D

dipsy

Hi:

The Code below works when the sheet is activated. However
when the code does not work - "'With Sheets(Name)"

Any help in explaining this is greatly appreciated.

TIA.


Sub resetdata()

Dim SheetName(1) As String
SheetName(0) = "2004"
SheetName(1) = "2004Data"

For Each Name In SheetName
Sheets(Name).Activate
'With Sheets(Name)
Do
If TypeName(Cells.Find("Index")) = "Range" Then
Cells.Find("Index", After:=ActiveCell).Activate
ActiveCell.Copy
ActiveCell.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Else
Exit Do
End If
Loop

'End With
Next
 
When you use the with statement you must assume the sheet name with a period
in front.
ie:
..cells
 
First, I'd stay away from variable names that look like VBA properties/methods
(like Name).

And it looks like you want to convert formulas to values for cells that contain
the word Index.

Excel likes to remember your last .find settings, so it's better to explicitly
set them in your find statement. (I guessed at the ones I used.)

Option Explicit

Sub resetdata()

Dim iCtr As Long
Dim myName As Variant
Dim SheetName(0 To 1) As String
Dim myRng As Range
Dim FoundCell As Range
Dim FirstAddress As String

SheetName(0) = "2004"
SheetName(1) = "2004Data"

For Each myName In SheetName
With Sheets(myName)
Set myRng = Nothing
On Error Resume Next
Set myRng = .Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If myRng Is Nothing Then
'do nothing
Else
With myRng
Set FoundCell = .Cells.Find(What:="index", After:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlPart, _
MatchCase:=False, _
SearchDirection:=xlNext)

If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
FoundCell.Copy
FoundCell.PasteSpecial Paste:=xlPasteValues

Set FoundCell = .FindNext(FoundCell)

Loop While Not FoundCell Is Nothing _
And FoundCell.Address <> FirstAddress
End If

End With
End If
End With
Next

End Sub

instead of copy|Pastespecial|values, you could use:

with foundcell
.value = .value
end with

(just set the value to itself.)

And most of the .find stuff came from VBA's help for find.
 
Dave:

Thank you so much for your help. It helped tons!

Regards.
-----Original Message-----
First, I'd stay away from variable names that look like VBA properties/methods
(like Name).

And it looks like you want to convert formulas to values for cells that contain
the word Index.

Excel likes to remember your last .find settings, so it's better to explicitly
set them in your find statement. (I guessed at the ones I used.)

Option Explicit

Sub resetdata()

Dim iCtr As Long
Dim myName As Variant
Dim SheetName(0 To 1) As String
Dim myRng As Range
Dim FoundCell As Range
Dim FirstAddress As String

SheetName(0) = "2004"
SheetName(1) = "2004Data"

For Each myName In SheetName
With Sheets(myName)
Set myRng = Nothing
On Error Resume Next
Set myRng = .Cells.SpecialCells (xlCellTypeFormulas)
On Error GoTo 0

If myRng Is Nothing Then
'do nothing
Else
With myRng
Set FoundCell = .Cells.Find
Click to expand...
(What:="index", After:=.Cells(1), _
 
Back
Top