I'm working on a program that copies each line of a source sheet, and
depending on the first cell in the row, pastes it into one of 2 or 3
other tables on a different sheet. A userform (uf1021Mid) asks the
user for the info needed, including the location on the source sheet
of the first row of data, via a refedit.

The code so far looks like this:

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
If rExtrFromStrt Is Nothing Then
Exit Sub 'user hit cancel
End If

If cbHdr = True Then
'MsgBox "true"
bHdr = True

End If
uf1021Mid.Hide

'On Error GoTo 0 'Is this neccessary?

End Sub

Problem is, I'm getting a type mismatch error on the marked line of
code, and can't figure out why. The variables are publicly declared so
they continue to exist when control goes back to the original macro in
the module. I want the range rExtrFromStrt to be the first cell in the
range uf1020Mid.rColStart so I can test it's value later. So why is
this a type mismatch?

As always, thanks!

Dave,

To refer to the first cell in uf1020Mid.rColStart, change 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 program that copies each line of a source sheet, and
depending on the first cell in the row, pastes it into one of 2 or 3
other tables on a different sheet. A userform (uf1021Mid) asks the
user for the info needed, including the location on the source sheet
of the first row of data, via a refedit.

The code so far looks like this:

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
If rExtrFromStrt Is Nothing Then
Exit Sub 'user hit cancel
End If

If cbHdr = True Then
'MsgBox "true"
bHdr = True

End If
uf1021Mid.Hide

'On Error GoTo 0 'Is this neccessary?

End Sub

Problem is, I'm getting a type mismatch error on the marked line of
code, and can't figure out why. The variables are publicly declared so
they continue to exist when control goes back to the original macro in
the module. I want the range rExtrFromStrt to be the first cell in the
range uf1020Mid.rColStart so I can test it's value later. So why is
this a type mismatch?

As always, thanks!

I don't see a lot of difference in the answer Vergel gave you that the ones
you got in the previous posting. If must be that using the cell address of
("A1") just won't work by itself because Excel and VBA does not recognize
that format as a range in the context that you have applied it.

"davegb" wrote:

I'm working on a program that copies each line of a source sheet, and
depending on the first cell in the row, pastes it into one of 2 or 3
other tables on a different sheet. A userform (uf1021Mid) asks the
user for the info needed, including the location on the source sheet
of the first row of data, via a refedit.

The code so far looks like this:

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
If rExtrFromStrt Is Nothing Then
Exit Sub 'user hit cancel
End If

If cbHdr = True Then
'MsgBox "true"
bHdr = True

End If
uf1021Mid.Hide

'On Error GoTo 0 'Is this neccessary?

End Sub

Problem is, I'm getting a type mismatch error on the marked line of
code, and can't figure out why. The variables are publicly declared so
they continue to exist when control goes back to the original macro in
the module. I want the range rExtrFromStrt to be the first cell in the
range uf1020Mid.rColStart so I can test it's value later. So why is
this a type mismatch?

As always, thanks!

On Apr 25, 2:22 pm, JLGWhiz wrote:
I don't see a lot of difference in the answer Vergel gave you that the ones
you got in the previous posting. If must be that using the cell address of
("A1") just won't work by itself because Excel and VBA does not recognize
that format as a range in the context that you have applied it.



"davegb" wrote:
I'm working on a program that copies each line of a source sheet, and
depending on the first cell in the row, pastes it into one of 2 or 3
other tables on a different sheet. A userform (uf1021Mid) asks the
user for the info needed, including the location on the source sheet
of the first row of data, via a refedit.

The code so far looks like this:

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
If rExtrFromStrt Is Nothing Then
Exit Sub 'user hit cancel
End If

If cbHdr = True Then
'MsgBox "true"
bHdr = True

End If
uf1021Mid.Hide

'On Error GoTo 0 'Is this neccessary?

End Sub

Problem is, I'm getting a type mismatch error on the marked line of
code, and can't figure out why. The variables are publicly declared so
they continue to exist when control goes back to the original macro in
the module. I want the range rExtrFromStrt to be the first cell in the
range uf1020Mid.rColStart so I can test it's value later. So why is
this a type mismatch?

As always, thanks!- Hide quoted text -

- Show quoted text -

Thank you both for your replies.

I have to apologize for posting this question more than once
yesterday. Apparently Google's NG servers were having problems, and
the first time I posted, I wanted over an hour and it didn't show, so
I posted again. Of course, they both made it through the Google maze
eventually.

As to the question. I figured it out. The line should have been:

Set rExtrFromStrt = uf1021Mid.rColStart.range("A1")

Just needed to add the .range to clarify it.