Prev: Excel VBA 2003: subroutine to reorder conditional formatting rules
Next: Macro not saving as
From: GEdwards on 27 Apr 2010 09:22
I receive a Run-time Error '13': Type mismatch on the "MsgBox" statement
shown below. Before continuing with the use of the "sourceRange" I just want
to ensure
that I am picking up the correct data.

I am even wondering however if the line prior to the MsgBox, "Set
sourceRange..." is actually working.

Any suggestions to correct the type mismatch and display the actual values
within the source range?


Sub DisplayMat()
Dim sourceRange As Range

RowCount = 8
lastrow = Cells(Rows.Count, 3).End(xlUp).Row
If lastrow > 7 Then
For Each qty In Range("A8:A" & lastrow)
If qty <> "" Then
Set sourceRange = Range("A" & RowCount & ":" & "D" & RowCount)
MsgBox sourceRange 'I even tried "MsgBox sourceRange.value"
'Do more stuff here with the sourceRange and output to another WSheet
End If
RowCount = RowCount + 1
Next
End If
End Sub
From: Mike H on 27 Apr 2010 09:40
Hi,

sourceRange is working fine and on the first pass through the loop it is
'Set' to A8:D8 i.e. 4 cells each of which may or may not have a value in. I
say that because the only thing your code actually establishes is that there
is something in column 3 (C) in row 8 or higher.

You're getting a type mismatch because sourceRange doesn't have a default
value but you can test it is working by using sourceRange.address for example

If you change your code to this you can loop thriugh sourcerange and see the
values

Sub DisplayMat()
Dim sourceRange As Range

RowCount = 8
lastrow = Cells(Rows.Count, 3).End(xlUp).Row
If lastrow > 7 Then

For Each qty In Range("A8:A" & lastrow)
If qty <> "" Then
Set sourceRange = Range("A" & RowCount & ":" & "D" & RowCount)
For Each c In sourceRange
MsgBox c.Value 'I even tried "MsgBox sourceRange.value"
Next
'Do more stuff here with the sourceRange and output to another WSheet
End If
RowCount = RowCount + 1
Next
End If
End Sub



--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"GEdwards" wrote:

> I receive a Run-time Error '13': Type mismatch on the "MsgBox" statement
> shown below. Before continuing with the use of the "sourceRange" I just want
> to ensure
> that I am picking up the correct data.
>
> I am even wondering however if the line prior to the MsgBox, "Set
> sourceRange..." is actually working.
>
> Any suggestions to correct the type mismatch and display the actual values
> within the source range?
>
>
> Sub DisplayMat()
> Dim sourceRange As Range
>
> RowCount = 8
> lastrow = Cells(Rows.Count, 3).End(xlUp).Row
> If lastrow > 7 Then
> For Each qty In Range("A8:A" & lastrow)
> If qty <> "" Then
> Set sourceRange = Range("A" & RowCount & ":" & "D" & RowCount)
> MsgBox sourceRange 'I even tried "MsgBox sourceRange.value"
> 'Do more stuff here with the sourceRange and output to another WSheet
> End If
> RowCount = RowCount + 1
> Next
> End If
> End Sub
From: Don Guillett on 27 Apr 2010 09:41
See if this idea helps.

Sub dm()
lastrow = Cells(Rows.Count, 3).End(xlUp).Row
If lastrow > 7 Then
For i = 8 To lastrow
With Cells(i, 1).Resize(, 4)
..Copy 'etc
'.something else

End With
Next i
End If

End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"GEdwards" <GEdwards(a)discussions.microsoft.com> wrote in message
news:3538CC32-E83E-4488-BE37-031E67C086F6(a)microsoft.com...
>I receive a Run-time Error '13': Type mismatch on the "MsgBox" statement
> shown below. Before continuing with the use of the "sourceRange" I just
> want
> to ensure
> that I am picking up the correct data.
>
> I am even wondering however if the line prior to the MsgBox, "Set
> sourceRange..." is actually working.
>
> Any suggestions to correct the type mismatch and display the actual values
> within the source range?
>
>
> Sub DisplayMat()
> Dim sourceRange As Range
>
> RowCount = 8
> lastrow = Cells(Rows.Count, 3).End(xlUp).Row
> If lastrow > 7 Then
> For Each qty In Range("A8:A" & lastrow)
> If qty <> "" Then
> Set sourceRange = Range("A" & RowCount & ":" & "D" & RowCount)
> MsgBox sourceRange 'I even tried "MsgBox sourceRange.value"
> 'Do more stuff here with the sourceRange and output to another
> WSheet
> End If
> RowCount = RowCount + 1
> Next
> End If
> End Sub

From: Jacob Skaria on 27 Apr 2010 09:46
sourceRange is a range object and cannot be displayed in Msgbox...You need to
loop through the values like the below

Dim cell As range, strMsg As String
Set sourceRange = Range("A" & RowCount & ":" & "D" & RowCount)
For each cell in sourceRange
strMsg = strMsg & "," & cell.Text
Next
Msgbox strMsg


--
Jacob (MVP - Excel)


"GEdwards" wrote:

> I receive a Run-time Error '13': Type mismatch on the "MsgBox" statement
> shown below. Before continuing with the use of the "sourceRange" I just want
> to ensure
> that I am picking up the correct data.
>
> I am even wondering however if the line prior to the MsgBox, "Set
> sourceRange..." is actually working.
>
> Any suggestions to correct the type mismatch and display the actual values
> within the source range?
>
>
> Sub DisplayMat()
> Dim sourceRange As Range
>
> RowCount = 8
> lastrow = Cells(Rows.Count, 3).End(xlUp).Row
> If lastrow > 7 Then
> For Each qty In Range("A8:A" & lastrow)
> If qty <> "" Then
> Set sourceRange = Range("A" & RowCount & ":" & "D" & RowCount)
> MsgBox sourceRange 'I even tried "MsgBox sourceRange.value"
> 'Do more stuff here with the sourceRange and output to another WSheet
> End If
> RowCount = RowCount + 1
> Next
> End If
> End Sub
From: GEdwards on 27 Apr 2010 09:47
This small change works great.

Many thanks to you Mike.

"Mike H" wrote:

> Hi,
>
> sourceRange is working fine and on the first pass through the loop it is
> 'Set' to A8:D8 i.e. 4 cells each of which may or may not have a value in. I
> say that because the only thing your code actually establishes is that there
> is something in column 3 (C) in row 8 or higher.
>
> You're getting a type mismatch because sourceRange doesn't have a default
> value but you can test it is working by using sourceRange.address for example
>
> If you change your code to this you can loop thriugh sourcerange and see the
> values
>
> Sub DisplayMat()
> Dim sourceRange As Range
>
> RowCount = 8
> lastrow = Cells(Rows.Count, 3).End(xlUp).Row
> If lastrow > 7 Then
>
> For Each qty In Range("A8:A" & lastrow)
> If qty <> "" Then
> Set sourceRange = Range("A" & RowCount & ":" & "D" & RowCount)
> For Each c In sourceRange
> MsgBox c.Value 'I even tried "MsgBox sourceRange.value"
> Next
> 'Do more stuff here with the sourceRange and output to another WSheet
> End If
> RowCount = RowCount + 1
> Next
> End If
> End Sub
>
>
>
> --
> Mike
>
> When competing hypotheses are otherwise equal, adopt the hypothesis that
> introduces the fewest assumptions while still sufficiently answering the
> question.
>
>
> "GEdwards" wrote:
>
> > I receive a Run-time Error '13': Type mismatch on the "MsgBox" statement
> > shown below. Before continuing with the use of the "sourceRange" I just want
> > to ensure
> > that I am picking up the correct data.
> >
> > I am even wondering however if the line prior to the MsgBox, "Set
> > sourceRange..." is actually working.
> >
> > Any suggestions to correct the type mismatch and display the actual values
> > within the source range?
> >
> >
> > Sub DisplayMat()
> > Dim sourceRange As Range
> >
> > RowCount = 8
> > lastrow = Cells(Rows.Count, 3).End(xlUp).Row
> > If lastrow > 7 Then
> > For Each qty In Range("A8:A" & lastrow)
> > If qty <> "" Then
> > Set sourceRange = Range("A" & RowCount & ":" & "D" & RowCount)
> > MsgBox sourceRange 'I even tried "MsgBox sourceRange.value"
> > 'Do more stuff here with the sourceRange and output to another WSheet
> > End If
> > RowCount = RowCount + 1
> > Next
> > End If
> > End Sub
 |  Next  |  Last
Pages: 1 2
Prev: Excel VBA 2003: subroutine to reorder conditional formatting rules
Next: Macro not saving as