Solved: Referencing objects

[fredlo2010]

Hello,

I have been working on this macro project but i cannot get to work. I am trying to set the worksheet to be activated as a variable so i can use it later on on another sub and thus modularize my job a little bit ( there is a lot of repetition in my code)

I have tried several things but nothings works. I have defined the variables as string, worksheet and nothing always errors. Maybe i am doing something wrong.

I am new to VBA, when i thought i had a concept...a new issue arises, but it comes with learning.

Here is my code

Sub ActivateSpecificSheet(text1 As String)

Dim rangeSheet As Range

text1.Activate

Range("A:A").Select

End Sub



Sub Letstry()

ActivateSpecificSheet (Sheet1)

End Sub


I am trying to activate sheet1 in my document, i am not sure whats better to use sheet names or sheet numeration.

Thanks a lot

[Paul_Hossler]

Sub ActivateSpecificSheet(text1 As String)

Dim rangeSheet As Range

Activeworkbook.Worksheets(text1).Activate

Set rangeSheet = Range("A:A")

End Sub



The Worksheets object is a Collection of Worksheet objects

To get a particular instance out of a Collection, you can usually index by number or Name

Activeworkbook.Worksheets(1).Activate

but using the .Name (i.e. text1) is IMHO preferred for the way you want to do it

To assign something to an Object variable, you can use Set

Paul

[fredlo2010]

Thanks a lot for the help Paul. It works perfectly. This will shrink my code from 100 lines to about 10 and one extra module.

By the way I will also wanted to point out that since we are using text1 as a string double quotation is needed.

Here is the final code so others can benefit from this.

Sub ActivateSpecificSheet(text1 As String)

Dim rangeSheet As Range

ActiveWorkbook.Worksheets(text1).Activate

Range("A:A").Select

End Sub


Sub Letstry()

ActivateSpecificSheet ("Sheet1")

End Sub

[Paul_Hossler]

Another approach that has advantages is to use the 'Code Name' (I think that's what it's called) instead of the .Name which might be changed by the user.



Sub ActivateSpecificSheet(MySheet As Worksheet)

Dim rangeSheet As Range

MySheet.Select

Range("A:A").Select

End Sub


Sub Letstry()

Call ActivateSpecificSheet(Sheet1)

End Sub


Paul

[snb]

In VBA you shouldn't select/activate anything, but do anything you want directly:


sub snb()
sheets("snb_02").columns(3)=sheets("fred").columns(1).value
end sub

[fredlo2010]

Hello guys,

I thought I had this one but again when I tried to apply it to a more complex level I ran into another problem, this time a compile error. I don't know what I am doing wrong this time.

This is the code

Sub SearchDelete(varColumn As String, varFind As String, varReplace As String)


ActiveSheet.Columns(varColumn).Select

Selection.Replace What:=varFind, Replacement:=varReplace, LookAt:=xlPart, _
SearchOrder:=xlByRows

Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete


End Sub


Sub lets_do_this()

SearchDelete ("A:A","to be found", "To be replaced")


End Sub


I tried the ActiveSheet.Columns(varColumn).Select part and it works fine so the issue is in the find-replace part of the code.

[snb]

Sub lets_do_this()

SearchDelete 1,"to be found", "To be replaced"
End Sub


Sub SearchDelete(varColumn As integer, varFind As String, varReplace As String)
on error resume next
with Columns(varColumn)
.Replace varFind, varReplace, xlPart
.SpecialCells(4).EntireRow.Delete
end with
End Sub

[Bob Phillips]

Sub SearchDelete(varColumn As String, varFind As String, varReplace As String)
Dim rng As Range

With ActiveSheet.Columns(varColumn)

.Replace What:=varFind, Replacement:=varReplace, LookAt:=xlPart, SearchOrder:=xlByRows

On Error Resume Next
Set rng = .SpecialCells(xlCellTypeBlanks).Select
On Error GoTo 0
If Not rng Is Nothing Then .EntireRow.Delete
End With
End Sub


Sub lets_do_this()

Call SearchDelete("A:A", "to be found", "To be replaced")
End Sub

[fredlo2010]

In VBA you shouldn't select/activate anything, but do anything you want directly:


sub snb()
sheets("snb_02").columns(3)=sheets("fred").columns(1).value
end sub



Thanks I finally understood what you meant to say. I am new to this and you are right the selection and activation slows down the code.

Now I avoid it as much as I can unless there is no other way to do things.

Thanks