Comparing Worksheet Names

  • Thread starter Thread starter BQ
  • Start date Start date
B

BQ

I can't figure out why my Statements:
If Worksheets(Usersheet).Name <> wsSheet.Name Then
and
If Worksheets(Usersheet).Name = wsSheet.Name Then
don't work.

This code is supposed to highlight the dependents on all
sheets of the selected cell, but it doesn't compare
correctly and it highlights all the cells without regard to
the Sheet the the cell came from. If you can help and
provide explainations of what I did wrong and how I can
improve it, I would be very thankful.




Private Sub btnHighlight_Click()

Dim wsSheet As Worksheet
Dim FirstCol, LastCol, FirstRow, LastRow As Long
Dim rng As Range
Dim MyCell As String

Set Usersheet = ActiveSheet

FirstCol = 1
FirstRow = 1


If txtFormBox.Text <> "" Then
'FINDS BOTTOM RIGHT CELLS
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards
by Rows.
LastRow = Cells.Find(What:="*", After:=[A1],
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'Search for any entry, by searching backwards
by Columns.
LastCol = Cells.Find(What:="*", After:=[A1],
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
End If

For i = FirstCol To LastCol
For j = FirstRow To LastRow
On Error Resume Next
If InStr(Cells(j, i).Formula,
txtFormBox.Text) > 0 Then
If rng Is Nothing Then
Set rng = Cells(j, i)
End If
Set rng = Union(rng, Cells(j, i))
End If
On Error GoTo 0
Next
Next

rng.Select
Set rng = Nothing


Else


MyCell = Selection.Address


If InStr(MyCell, ",") = 0 Then
If InStr(MyCell, ":") > 0 Then
MyCell = Replace(MyCell, "$", "")
MyCell = Mid$(MyCell, 1, InStr(1, MyCell,
":") - 1)
ElseIf InStr(MyCell, ":") = 0 Then
MyCell = Replace(MyCell, "$", "")
End If

On Error Resume Next
For Each wsSheet In Worksheets
wsSheet.Activate

If Usersheet.Name <> wsSheet.Name Then
Range("a1").Select
End If


If WorksheetFunction.CountA(Cells) > 0 Then
LastRow = Cells.Find(What:="*",
After:=[A1], SearchOrder:=xlByRows,
SearchDirection:=xlPrevious).Row
LastCol = Cells.Find(What:="*",
After:=[A1], SearchOrder:=xlByColumns,
SearchDirection:=xlPrevious).Column
End If


For i = FirstCol To LastCol
For j = FirstRow To LastRow
If Worksheets(Usersheet).Name =
wsSheet.Name Then
If InStr(1,
Replace(Cells(j, i).Formula, "$", ""), MyCell) > 0 Then
If rng Is Nothing Then
Set rng = Cells(j, i)
End If
Set rng = Union(rng,
Cells(j, i))
End If
End If


If Worksheets(Usersheet).Name
<> wsSheet.Name Then
If InStr(1,
Replace(Cells(j, i).Formula, "$", ""), "'" & wsSheet.Name &
"'!" & MyCell) > 0 Then
If rng Is Nothing Then
Set rng = Cells(j, i)
End If
Set rng = Union(rng,
Cells(j, i))
End If
End If
Next
Next

If Usersheet.Name <> wsSheet.Name Then
Application.Goto
Reference:=Range(ActiveCell.Address), Scroll:=True
End If

rng.Select
Set rng = Nothing

Next wsSheet

On Error GoTo 0
Set wSheet = Nothing

End If
End If
End Sub
 
I didn't try your code, but ............. You have this statement,
which sets a reference to sheet.

Set Usersheet = ActiveSheet 'Reference to sheet

-------------------------------------------------

In your code, you use the following statement, which I assume works.

If Usersheet.Name <> wsSheet.Name Then ' ...........

I don't understand why you're not using this statement throughout your
code.

--------------------------------------------------

For this to work, you would have to

Dim Usersheet as String

Usersheet = ActiveSheet.Name

If Worksheets(Usersheet).Name <> wsSheet.Name Then ' ..............

--------------------------------------------------

Additionally, have you declared the variable Usersheet

Dim Usersheet as WorkSheet

--------------------------------------------------

HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------

I can't figure out why my Statements:
If Worksheets(Usersheet).Name <> wsSheet.Name Then
and
If Worksheets(Usersheet).Name = wsSheet.Name Then
don't work.

This code is supposed to highlight the dependents on all
sheets of the selected cell, but it doesn't compare
correctly and it highlights all the cells without regard to
the Sheet the the cell came from. If you can help and
provide explainations of what I did wrong and how I can
improve it, I would be very thankful.




Private Sub btnHighlight_Click()

Dim wsSheet As Worksheet
Dim FirstCol, LastCol, FirstRow, LastRow As Long
Dim rng As Range
Dim MyCell As String

Set Usersheet = ActiveSheet

FirstCol = 1
FirstRow = 1


If txtFormBox.Text <> "" Then
'FINDS BOTTOM RIGHT CELLS
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards
by Rows.
LastRow = Cells.Find(What:="*", After:=[A1],
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'Search for any entry, by searching backwards
by Columns.
LastCol = Cells.Find(What:="*", After:=[A1],
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
End If

For i = FirstCol To LastCol
For j = FirstRow To LastRow
On Error Resume Next
If InStr(Cells(j, i).Formula,
txtFormBox.Text) > 0 Then
If rng Is Nothing Then
Set rng = Cells(j, i)
End If
Set rng = Union(rng, Cells(j, i))
End If
On Error GoTo 0
Next
Next

rng.Select
Set rng = Nothing


Else


MyCell = Selection.Address


If InStr(MyCell, ",") = 0 Then
If InStr(MyCell, ":") > 0 Then
MyCell = Replace(MyCell, "$", "")
MyCell = Mid$(MyCell, 1, InStr(1, MyCell,
":") - 1)
ElseIf InStr(MyCell, ":") = 0 Then
MyCell = Replace(MyCell, "$", "")
End If

On Error Resume Next
For Each wsSheet In Worksheets
wsSheet.Activate

If Usersheet.Name <> wsSheet.Name Then
Range("a1").Select
End If


If WorksheetFunction.CountA(Cells) > 0 Then
LastRow = Cells.Find(What:="*",
After:=[A1], SearchOrder:=xlByRows,
SearchDirection:=xlPrevious).Row
LastCol = Cells.Find(What:="*",
After:=[A1], SearchOrder:=xlByColumns,
SearchDirection:=xlPrevious).Column
End If


For i = FirstCol To LastCol
For j = FirstRow To LastRow
If Worksheets(Usersheet).Name =
wsSheet.Name Then
If InStr(1,
Replace(Cells(j, i).Formula, "$", ""), MyCell) > 0 Then
If rng Is Nothing Then
Set rng = Cells(j, i)
End If
Set rng = Union(rng,
Cells(j, i))
End If
End If


If Worksheets(Usersheet).Name
<> wsSheet.Name Then
If InStr(1,
Replace(Cells(j, i).Formula, "$", ""), "'" & wsSheet.Name &
"'!" & MyCell) > 0 Then
If rng Is Nothing Then
Set rng = Cells(j, i)
End If
Set rng = Union(rng,
Cells(j, i))
End If
End If
Next
Next

If Usersheet.Name <> wsSheet.Name Then
Application.Goto
Reference:=Range(ActiveCell.Address), Scroll:=True
End If

rng.Select
Set rng = Nothing

Next wsSheet

On Error GoTo 0
Set wSheet = Nothing

End If
End If
End Sub
Click to expand...
 
Back
Top