How do I reference a Range of cells in excel using variable names

[Kermitp]

I am trying to find users (email address) in a range within a single column.
The column changes as I process worksheets. I have a list of contacts in
another worksheet:

In the code below I explain which statement works and which one fails. I
need a method to move from column to column searching for matching email
addresses.

I appreciate any help you can give to solve this problem. I'm open to all
suggestions.

Thanks, Kermitp

_____________________________________________
____________________________________________
Sub findeachopen()



gonogo = "Y"
contactCol = 2
' "NL-opens-by-NL" ' contains a Column (A) with master list of email
addresses and a column for each sent email
' "NL-opens" ' contains multple columns with list of who opened the email
' this sub finds if the person has open each email and marks the cell Y
or N in that email column
' this way we can determine how many emails each person has opened

While Not (IsEmpty(Worksheets("NL-opens-by-NL").Cells(1, contactCol).Value)
Or gonogo = "N")
contactstartrow = 2
contactRow = 2
contactlastrow = 300
headrow = 1

With Worksheets(1)
.Range(.Cells(1, 1), _
.Cells(10, 10)).Borders.LineStyle = xlThick
End With

While Not (IsEmpty(Worksheets("NL-opens-by-NL").Cells(contactRow,
"A").Value) Or gonogo = "N")

UserName = Worksheets("NL-opens-by-NL").Cells(contactRow, "A").Value
'
' The next statement get an error of Invalid or unqualified reference
'
With Worksheets("NL-opens").Range(.Cells(contactstartrow,
contactCol), .Cells(contactlastrow, contactCol))

'
' The next statement works but I can't change the Column in the range
' which I need to do in order to procees all columns
'
' With Worksheets("NL-opens").Range("B2:B300")

Set c = .Find(UserName, LookIn:=xlValues)
If Not c Is Nothing Then

Worksheets("NL-opens-by-NL").Cells(contactRow,
contactCol).Value = "Y"
Else
Worksheets("NL-opens-by-NL").Cells(contactRow,
contactCol).Value = "N"
End If
End With
contactRow = contactRow + 1

Wend

contactCol = contactCol + 1

Wend

End Sub

 

[Per Jessen]

Hi Kermit

Try this:

Sub findeachopen()
Dim TargetRange As Range
GoNoGo = "Y"
ContactCol = 2
' "NL-opens-by-NL" ' contains a Column (A) with master list of email
addresses and a column for each sent email
' "NL-opens" ' contains multple columns with list of who opened the
email
' this sub finds if the person has open each email and marks the cell Y
or N in that email column
' this way we can determine how many emails each person has opened

While Not (IsEmpty(Worksheets("NL-opens-by-NL").Cells(1, ContactCol).Value)
Or GoNoGo = "N")
ContactStartRow = 2
ContactRow = 2
ContactLastRow = 300
HeadRow = 1

With Worksheets(1)
.Range(.Cells(1, 1), _
.Cells(10, 10)).Borders.LineStyle = xlThick
End With

While Not (IsEmpty(Worksheets("NL-opens-by-NL").Cells(ContactRow,
"A").Value) Or GoNoGo = "N")
UserName = Worksheets("NL-opens-by-NL").Cells(ContactRow, "A").Value
Set TargetRange =
Worksheets("NL-opens").Range(Cells(ContactStartRow, ContactCol),
Cells(ContactLastRow, ContactCol))
Set c = TargetRange.Find(UserName, LookIn:=xlValues)
If Not c Is Nothing Then
Worksheets("NL-opens-by-NL").Cells(ContactRow, ContactCol).Value
= "Y"
Else
Worksheets("NL-opens-by-NL").Cells(ContactRow, ContactCol).Value
= "N"
End If
ContactRow = ContactRow + 1
Wend
ContactCol = ContactCol + 1
Wend
End Sub

Regards,
Per

 

[Kermitp]

Per,

I appreciate your response but I get an error message of run-time error
‘1004’ on the Set TargetRange statement.

I don’t see anything wrong as far as variable names or misspelling

I tried to simplify the example to remove confusion on my part. Looks like
it comes down to the format of the Set TargetRange statement.

Any suggestions will be greatly appreciated

Sub testrange()

Dim TargetRange As Range

ContactCol = 2
ContactStartRow = 2
ContactRow = 2
ContactLastRow = 300


UserName = Worksheets("NL-opens-by-NL").Cells(ContactRow, "A").Value
Set TargetRange = Worksheets("NL-opens").Range(Cells(ContactStartRow,
ContactCol),Cells(ContactLastRow, ContactCol))

Set c = TargetRange.Find(UserName, LookIn:=xlValues)

End Sub

 

[Kermitp]

I found the answer in another posting. The "DOTS" were missing.

Thanks for all the help. These communities saves me a ton of time. Just wish
I was better at searching for the solution.

***Here is the fix***

The code doesn't know which worksheet CELLS is locat on. Use this instead

with Workbooks("Workbook1").Sheets("Sheet1")
Set MyRangeName = .Range(.Cells(12, 12), .Cells(15, 12))
end with

****Notice the dot I put in front of CELLS*****

 

[Chip Pearson]

The problem is when the reference to Cells in the line

Set TargetRange = Worksheets("NL-opens").Range(Cells(ContactStartRow,
ContactCol),Cells(ContactLastRow, ContactCol))


Here, the Cells reference does not point to cells on worksheet
"NL-opens". Instead, because it is not qualified with a specific
worksheet, it points to the ActiveSheet. You can use a With statement
to refer to worksheet "NL_Opens". For example


With Worksheets("NL_opens")
Set TargetRange = .Range( _
.Cells(ContactStartRow, ContactCol), _
.Cells(ContactLastRow, ContactCol))
End With

Note the period before the words "Range" and both "Cells". The period
tells the code that the property prefixed with the period refers to
the object named in the With statement. This code is equivalent to

Set TargetRange = Worksheets("NL_opens").Range( _
Worksheets("NL_opens").Cells(ContactStartRow, ContactCol), _
Worksheets("NL_opens").Cells(ContactLastRow, ContactCol))

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)