How to select general (unspecified) range that is active ?

[Sachy]

Hello to everyone, how are you today ? I am little bit sad (or mad) because I am stuck at relatively easy task (but not for me now as I discovered) and cant find nothing useful on net , so please help:D

I have this macro for searching text in whole column and then add answer to next column - row. The problem is that the search range is only for specific column (now only for C1 to its end), but I want to search the text for any active column that I will select. So I need to know how to define, that the range will be dynamic or general for all columns that I select to search in, not only column C or some other. Hope I explain it clear.

Please Login or Register  to view this content.

[alansidman]

Replace this:

Please Login or Register  to view this content.

with this

Please Login or Register  to view this content.

[Sachy]

Hi thanks for your quick reply, but its not working after I change it. I get Run time error "1004". My question is still how to define a general range of one whole column without using the letter of it. I think that can solve the problem. It should look something like this, (but the question-mark will be replaced with some symbol or sign) to make it sense to VBA:

Range(ActiveSheet.Range("?"), ActiveSheet.Range("?").End(xlUp)).Cells

[Norie]

To get the whole column for the active cell use ActiveCell.EntireColumn.

Please Login or Register  to view this content.

[Sachy]

Ok I will try it, hope it will work

[Sachy]

Yes, I finally have it ! I change the code to this to get it to work:

For Each FindCell In Range(ActiveCell.EntireColumn, ActiveCell.EntireColumn.End(xlUp)).Cells

But then when I want to use it to find more words at the same time I get an error. Likely a friend of mine adjust it to work with more word. The code looks like this:

Public Sub findWord()


Dim findString As String
Dim myReplace As String
Dim findCell As Range

findString = "Need to be added to SESDR"
myReplace = "Add to SESDR"
Set findRange = Selection

If findString = "" Then Exit Sub

For Each cell a In findRange
If InStr(cell, findString) > 0 Then cell.Offset(0, 1) = myReplace
Next cell
End Sub