Not sure if I'm on the right track with this project or not, but keep getting a 'object variable or with block not set' on the highlighted line of code below. There are lots of variables defined and not yet used as I'm not finished with the code. Here's what I'm attempting to do:
Using two tables, each on a separate sheet: Database and Email
Each line of the Database contains information for one student with email addresses for two parents
- Headers for the parent emails are MEmail and FEmail - These columns are not next to each other.
I have a separate sub to take all unique emails from these two lists and put them into one list on my email list (Header= Email)
In the event that there are duplicates (i.e. multiple siblings in the school) I need to combine all the student names from Database table into one cell on Email table. I have a sub to do this (Sub JoinCells listed below) which requires the input of rngJoin(The cells to join that I'm trying to determine here) and rngDest(where the combined string goes - which should be a 'simple' match function). It is very likely that the multiple instances would be non contiguous. I suppose I could sort each list first, but I don't know if that would gain me much and it won't solve the problem with the error I'm getting.
The challenge ocurs when trying to find the correct row(s) of data in DatabaseTable associated with the EmailList. Match only works with contiguous ranges so I've created a Select Case block using range.find to search for the email in one of the lists and then using application.match for each case.
When I try to run this code, though, I keep getting the error 'Object variable or With Block not defined' on my Select Case line. As far as I can tell I've defined all the variables and I don't have a with block.
Any ideas why I'm getting this error?
Am I on the right track? Is there a simpler way to go about this?
Using two tables, each on a separate sheet: Database and Email
Each line of the Database contains information for one student with email addresses for two parents
- Headers for the parent emails are MEmail and FEmail - These columns are not next to each other.
I have a separate sub to take all unique emails from these two lists and put them into one list on my email list (Header= Email)
In the event that there are duplicates (i.e. multiple siblings in the school) I need to combine all the student names from Database table into one cell on Email table. I have a sub to do this (Sub JoinCells listed below) which requires the input of rngJoin(The cells to join that I'm trying to determine here) and rngDest(where the combined string goes - which should be a 'simple' match function). It is very likely that the multiple instances would be non contiguous. I suppose I could sort each list first, but I don't know if that would gain me much and it won't solve the problem with the error I'm getting.
The challenge ocurs when trying to find the correct row(s) of data in DatabaseTable associated with the EmailList. Match only works with contiguous ranges so I've created a Select Case block using range.find to search for the email in one of the lists and then using application.match for each case.
When I try to run this code, though, I keep getting the error 'Object variable or With Block not defined' on my Select Case line. As far as I can tell I've defined all the variables and I don't have a with block.
Any ideas why I'm getting this error?
Am I on the right track? Is there a simpler way to go about this?
Rich (BB code):
Sub CBEmail_Click()
Dim Database As ListObject
Dim EmailList As ListObject
Dim dbHeaders As Range
Dim eHeaders As Range
Dim dbrMEmail As Range
Dim dbrFEmail As Range
Dim dbrClass As Range
Dim dbrFullName As Range
Dim erFirst As Range
Dim erLast As Range
Dim erNickname As Range
Dim erEmail As Range
Dim erClass As Range
Dim cell As Range
Dim MtchRng As Range
Dim LookupRng As Range
Dim MatchNum As Integer
Dim dbvar As Variant
Dim evar As Variant
Dim i As Integer
Dim m As Integer
Dim arr As Variant
'declare all range variables in database table
Set Database = ActiveWorkbook.Sheets("database").ListObjects("Database")
Set dbHeaders = Database.HeaderRowRange
dbvar = Application.Match("MEmail", dbHeaders, 0)
Set dbrMEmail = Database.ListColumns(dbvar).Range
dbvar = Application.Match("FEmail", dbHeaders, 0)
Set dbrFEmail = Database.ListColumns(dbvar).Range
dbvar = Application.Match("Class", dbHeaders, 0)
Set dbrClass = Database.ListColumns(dbvar).Range
dbvar = Application.Match("Full Name", dbHeaders, 0)
Set dbrFullName = Database.ListColumns(dbvar).Range
'declare all range variables in email list table
Set EmailList = ActiveWorkbook.Sheets("Email").ListObjects("EmailList")
Set eHeaders = EmailList.HeaderRowRange
evar = Application.Match("First", eHeaders, 0)
Set erFirst = EmailList.ListColumns(evar).Range
evar = Application.Match("Last", eHeaders, 0)
Set erLast = EmailList.ListColumns(evar).Range
evar = Application.Match("Nickname", eHeaders, 0)
Set erNickname = EmailList.ListColumns(evar).Range
evar = Application.Match("Email", eHeaders, 0)
Set erEmail = EmailList.ListColumns(evar).Range
evar = Application.Match("Class", eHeaders, 0)
Set erClass = EmailList.ListColumns(evar).Range
'fill table with emails
' Call Find_Unique_Emails
'Fill first name
'fill last name
'fill nickname (joined 'full name')
'fill class (Joined 'class')
'Find range of cells to join
'On Error GoTo nextStep
For Each cell In Application.Intersect(erEmail, EmailList.DataBodyRange)
'looking in FEmail List for cell value
Select Case dbrFEmail.Find(what:=cell.Value)
Case Nothing
'if not found in FEmail look in MEmail
Select Case dbrMEmail.Find(what:=cell.Value)
Case Nothing
'loop to next cell
End Select
'if found in mEmail list
'Find number of matches
MatchNum = Application.CountIf(dbrMEmail, cell.Value)
'find row number for each
m = Application.Match(cell.Value, dbrMEmail, 0)
'determine rowrange of match
Set MtchRng = Database.ListRows(m).Range
'loop through all matches
For i = 1 To MatchNum
'combine eacch match range into one range
MtchRng = Union(MtchRng, Database.ListRows(m).Range)
Next i
Case Else
'if found in FEMail
'Find number of matches
MatchNum = Application.CountIf(dbrFEmail, cell.Value)
'find row number for each
m = Application.Match(cell.Value, dbrFEmail, 0)
'determine rowrange of match
Set MtchRng = Database.ListRows(m).Range
'loop through all matches
For i = 1 To MatchNum
'combine eacch match range into one range
MtchRng = Union(MtchRng, Database.ListRows(m).Range)
Next i
End Select
Next cell
MsgBox MtchRng.Address
'errmsg: MsgBox Err.Description
End Sub
Sub JoinCells(rngJoin As Range, rngDest As Range)
'joins cells from selected range into the selected cell
'-----range must be a SINGLE column or SINGLE row
Dim test As Integer
Dim JoinType As String
Dim JoinNum As Integer
Dim Temp As String
Dim i As Integer
'count number of rows to determine direction of range
test = rngJoin.Rows.Count
'determine if range is a row or column, number of cells in range to join
Select Case test
Case 1
JoinType = "Columns"
JoinNum = rngJoin.Columns.Count
Case Is > 1
JoinType = "Rows"
JoinNum = test
Case Else
MsgBox "Range to join not selected"
Exit Sub
End Select
'join cell values
Select Case JoinType
Case "Columns"
Temp = rngJoin.Columns(1).Value
For i = 2 To JoinNum
Temp = Temp & "," & rngJoin.Columns(i).Value
Next i
Case "Rows"
Temp = rngJoin.Rows(1).Value
For i = 2 To JoinNum
Temp = Temp & "," & rngJoin.Rows(i).Value
Next i
End Select
'set range value as joined cell values
rngDest.Value = Temp
End Sub
