Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > How do I reference a Range of cells in excel using variable names
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3
Default How do I reference a Range of cells in excel using variable names
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


--
Kermit, long time windows Office user
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,533
Default How do I reference a Range of cells in excel using variable names
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" skrev i meddelelsen
...
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


--
Kermit, long time windows Office user

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default How do I reference a Range of cells in excel using variable na
Per,

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

I dont 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


"Per Jessen" wrote:

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" skrev i meddelelsen
...
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


--
Kermit, long time windows Office user


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default How do I reference a Range of cells in excel using variable na
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*****





--
Kermitp


"Kermitp" wrote:

Per,

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

I dont 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


"Per Jessen" wrote:

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" skrev i meddelelsen
...
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


--
Kermit, long time windows Office user


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7,247
Default How do I reference a Range of cells in excel using variable na
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)



On Thu, 16 Oct 2008 06:38:01 -0700, Kermitp
wrote:

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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Variable Names Range - Help Needed Les Excel Programming 4 July 15th 08 01:32 PM
reference other worksheets via variable names Huggy Excel Worksheet Functions 4 June 26th 08 05:52 AM
Is there a way to give range names (especially with relative reference) for series names and series values in Excel 2007 graphs? [email protected] Excel Programming 0 August 16th 07 02:52 PM
Variable names for named range Barb Reinhardt Excel Discussion (Misc queries) 4 March 19th 07 05:37 PM
How do I set up a variable reference range in Excel? Pecan1862 Excel Programming 1 January 27th 06 09:29 PM


All times are GMT +1. The time now is 02:36 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement