I'm working on a macro to extract the lines of a spreadsheet and
create separate tables based on the first column in the original
spreadsheet. After some testing to make sure the right kind of data is
selected, a userform (uf1021mid) is shown and gets the type of
extraction the user wants and where the start of the data range to be
extracted is (via a rededit). I test to make sure the input is valid,
then go back to the main code.

The code below is in the userform module and after the user clicks OK,
saves the variables (all declared publicly so they'll be there when
control returns to the main macro). One of these is the cell where the
extraction range starts, rExtrFromStrt. But I keep getting a type
mismatch error on trying to save the first cell in the range as a
range.

Sub OKButton_Click()
Dim rFndCell As Range
Dim lStrDif As Long

If btnTop10BOS Then lTop = 10
If btnTop21BOS Then lTop = 21
If btnTop10MidBOS Then lTop = 3

'On Error Resume Next
If lTop = 0 Then
MsgBox "Please select the type of extraction (i.e., Top 10, BOS)
you want."
Exit Sub
End If

If reDataStrt = "" Then
MsgBox "Please select the range where the first county, " _
& "Adams, data is located."
Exit Sub
End If

Set uf1021Mid.rColStart = Range(reDataStrt.Text)

Set rFndCell = uf1021Mid.rColStart.Rows(1).Find(What:="Adams", _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If rFndCell Is Nothing Then
MsgBox "The first row of data should include Adams County. " _
& "Please select the correct row."
Exit Sub
End If

s1stCtyName = rFndCell.Value

'If UCase(s1stCtyName) < "ADAMS" Then
If UCase(s1stCtyName) Like "*ADAMS" Then
lStrDif = Len(s1stCtyName) - 5
s1stCtyName = Right(s1stCtyName, Len(s1stCtyName) - lStrDif)
Else
If MsgBox("No ADAMS county found in county list!",
vbRetryCancel) _
= vbCancel Then
Exit Sub
Else
Application.ScreenUpdating = True

End If

End If

'End If
With uf1021Mid.rColStart
lLastCol = .Columns(.Columns.Count).Column
End With

Set rExtrFromStrt = uf1021Mid.rColStart("A1") <----------- TYPE
MISMATCH ERROR
If rExtrFromStrt Is Nothing Then
Exit Sub 'user hit cancel
End If

Since the program has already accepted uf1021mid.rColStart as a range,
and "A1" is the first cell in that range, and rExtrFromStrt is
declared as a range, why is this a type mismatch? Any suggestions.

As always, thanks!

"A1" by itself isnt a range. Try Range("A1")

"davegb" wrote:

I'm working on a macro to extract the lines of a spreadsheet and
create separate tables based on the first column in the original
spreadsheet. After some testing to make sure the right kind of data is
selected, a userform (uf1021mid) is shown and gets the type of
extraction the user wants and where the start of the data range to be
extracted is (via a rededit). I test to make sure the input is valid,
then go back to the main code.

The code below is in the userform module and after the user clicks OK,
saves the variables (all declared publicly so they'll be there when
control returns to the main macro). One of these is the cell where the
extraction range starts, rExtrFromStrt. But I keep getting a type
mismatch error on trying to save the first cell in the range as a
range.

Sub OKButton_Click()
Dim rFndCell As Range
Dim lStrDif As Long

If btnTop10BOS Then lTop = 10
If btnTop21BOS Then lTop = 21
If btnTop10MidBOS Then lTop = 3

'On Error Resume Next
If lTop = 0 Then
MsgBox "Please select the type of extraction (i.e., Top 10, BOS)
you want."
Exit Sub
End If

If reDataStrt = "" Then
MsgBox "Please select the range where the first county, " _
& "Adams, data is located."
Exit Sub
End If

Set uf1021Mid.rColStart = Range(reDataStrt.Text)

Set rFndCell = uf1021Mid.rColStart.Rows(1).Find(What:="Adams", _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If rFndCell Is Nothing Then
MsgBox "The first row of data should include Adams County. " _
& "Please select the correct row."
Exit Sub
End If

s1stCtyName = rFndCell.Value

'If UCase(s1stCtyName) < "ADAMS" Then
If UCase(s1stCtyName) Like "*ADAMS" Then
lStrDif = Len(s1stCtyName) - 5
s1stCtyName = Right(s1stCtyName, Len(s1stCtyName) - lStrDif)
Else
If MsgBox("No ADAMS county found in county list!",
vbRetryCancel) _
= vbCancel Then
Exit Sub
Else
Application.ScreenUpdating = True

End If

End If

'End If
With uf1021Mid.rColStart
lLastCol = .Columns(.Columns.Count).Column
End With

Set rExtrFromStrt = uf1021Mid.rColStart("A1") <----------- TYPE
MISMATCH ERROR
If rExtrFromStrt Is Nothing Then
Exit Sub 'user hit cancel
End If

Since the program has already accepted uf1021mid.rColStart as a range,
and "A1" is the first cell in that range, and rExtrFromStrt is
declared as a range, why is this a type mismatch? Any suggestions.

As always, thanks!

Dave,

try changing this line

Set rExtrFromStrt = uf1021Mid.rColStart("A1")

to this

Set rExtrFromStrt = uf1021Mid.rColStart(1, 1)



--
Hope that helps.

Vergel Adriano


"davegb" wrote:

I'm working on a macro to extract the lines of a spreadsheet and
create separate tables based on the first column in the original
spreadsheet. After some testing to make sure the right kind of data is
selected, a userform (uf1021mid) is shown and gets the type of
extraction the user wants and where the start of the data range to be
extracted is (via a rededit). I test to make sure the input is valid,
then go back to the main code.

The code below is in the userform module and after the user clicks OK,
saves the variables (all declared publicly so they'll be there when
control returns to the main macro). One of these is the cell where the
extraction range starts, rExtrFromStrt. But I keep getting a type
mismatch error on trying to save the first cell in the range as a
range.

Sub OKButton_Click()
Dim rFndCell As Range
Dim lStrDif As Long

If btnTop10BOS Then lTop = 10
If btnTop21BOS Then lTop = 21
If btnTop10MidBOS Then lTop = 3

'On Error Resume Next
If lTop = 0 Then
MsgBox "Please select the type of extraction (i.e., Top 10, BOS)
you want."
Exit Sub
End If

If reDataStrt = "" Then
MsgBox "Please select the range where the first county, " _
& "Adams, data is located."
Exit Sub
End If

Set uf1021Mid.rColStart = Range(reDataStrt.Text)

Set rFndCell = uf1021Mid.rColStart.Rows(1).Find(What:="Adams", _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If rFndCell Is Nothing Then
MsgBox "The first row of data should include Adams County. " _
& "Please select the correct row."
Exit Sub
End If

s1stCtyName = rFndCell.Value

'If UCase(s1stCtyName) < "ADAMS" Then
If UCase(s1stCtyName) Like "*ADAMS" Then
lStrDif = Len(s1stCtyName) - 5
s1stCtyName = Right(s1stCtyName, Len(s1stCtyName) - lStrDif)
Else
If MsgBox("No ADAMS county found in county list!",
vbRetryCancel) _
= vbCancel Then
Exit Sub
Else
Application.ScreenUpdating = True

End If

End If

'End If
With uf1021Mid.rColStart
lLastCol = .Columns(.Columns.Count).Column
End With

Set rExtrFromStrt = uf1021Mid.rColStart("A1") <----------- TYPE
MISMATCH ERROR
If rExtrFromStrt Is Nothing Then
Exit Sub 'user hit cancel
End If

Since the program has already accepted uf1021mid.rColStart as a range,
and "A1" is the first cell in that range, and rExtrFromStrt is
declared as a range, why is this a type mismatch? Any suggestions.

As always, thanks!

On Apr 25, 12:50 pm, Vergel Adriano
wrote:
Dave,

try changing this line

Set rExtrFromStrt = uf1021Mid.rColStart("A1")

to this

Set rExtrFromStrt = uf1021Mid.rColStart(1, 1)

--
Hope that helps.

Vergel Adriano



"davegb" wrote:
I'm working on a macro to extract the lines of a spreadsheet and
create separate tables based on the first column in the original
spreadsheet. After some testing to make sure the right kind of data is
selected, a userform (uf1021mid) is shown and gets the type of
extraction the user wants and where the start of the data range to be
extracted is (via a rededit). I test to make sure the input is valid,
then go back to the main code.

The code below is in the userform module and after the user clicks OK,
saves the variables (all declared publicly so they'll be there when
control returns to the main macro). One of these is the cell where the
extraction range starts, rExtrFromStrt. But I keep getting a type
mismatch error on trying to save the first cell in the range as a
range.

Sub OKButton_Click()
Dim rFndCell As Range
Dim lStrDif As Long

If btnTop10BOS Then lTop = 10
If btnTop21BOS Then lTop = 21
If btnTop10MidBOS Then lTop = 3

'On Error Resume Next
If lTop = 0 Then
MsgBox "Please select the type of extraction (i.e., Top 10, BOS)
you want."
Exit Sub
End If

If reDataStrt = "" Then
MsgBox "Please select the range where the first county, " _
& "Adams, data is located."
Exit Sub
End If

Set uf1021Mid.rColStart = Range(reDataStrt.Text)

Set rFndCell = uf1021Mid.rColStart.Rows(1).Find(What:="Adams", _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If rFndCell Is Nothing Then
MsgBox "The first row of data should include Adams County. " _
& "Please select the correct row."
Exit Sub
End If

s1stCtyName = rFndCell.Value

'If UCase(s1stCtyName) < "ADAMS" Then
If UCase(s1stCtyName) Like "*ADAMS" Then
lStrDif = Len(s1stCtyName) - 5
s1stCtyName = Right(s1stCtyName, Len(s1stCtyName) - lStrDif)
Else
If MsgBox("No ADAMS county found in county list!",
vbRetryCancel) _
= vbCancel Then
Exit Sub
Else
Application.ScreenUpdating = True

End If

End If

'End If
With uf1021Mid.rColStart
lLastCol = .Columns(.Columns.Count).Column
End With

Set rExtrFromStrt = uf1021Mid.rColStart("A1") <----------- TYPE
MISMATCH ERROR
If rExtrFromStrt Is Nothing Then
Exit Sub 'user hit cancel
End If

Since the program has already accepted uf1021mid.rColStart as a range,
and "A1" is the first cell in that range, and rExtrFromStrt is
declared as a range, why is this a type mismatch? Any suggestions.

As always, thanks!- Hide quoted text -

- Show quoted text -

Thanks for your replies! And sorry that I multiple-posted, Google was
having trouble with their servers yesterday and didn't post my
messages until hours after I sent them.

And the winna is....

Brotha Lee. I figured out the same thing later yesterday.