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  |