Prev: Excel VBA 2003: subroutine to reorder conditional formatting rules
Next: Macro not saving as
From: Mike H on 27 Apr 2010 09:50
Your welcome and thank you for the feedback
--
Mike

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


"GEdwards" wrote:

> 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
From: GEdwards on 27 Apr 2010 10:35
Thanks Jacob, this works well too by displaying the range contents all in 1
line.

"Jacob Skaria" wrote:

> 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
First  |  Prev  | 
Pages: 1 2
Prev: Excel VBA 2003: subroutine to reorder conditional formatting rules
Next: Macro not saving as