Hi all! I've got this macro in Sheet 1 (attached):

Sub abc()
Set rng = Range(Range("B4"), _
Cells(Rows.Count, 2).End(xlUp))
For Each cell In rng
If Not IsEmpty(cell) And _
InStr(1, cell.Value, "total", _
vbTextCompare) = 0 Then
Range("D3").Copy cell.Offset(0, 2)
End If
Next
End Sub

and it basically autofills all cells in column D from D3 downwards
except for those cells with no corresponding value in the B column and
those with "Total". I would just like to modify the macro such that the
autofill stops once it reaches the F cell whose corresponding column B
cell has a value of "Total Group 2". Anyone know how this can be done?
Any help will be greatly appreciated. Thanks!


+-------------------------------------------------------------------+
|Filename: Sheet 1.JPG |
|Download: http://www.excelforum.com/attachment.php?postid=4027 |
+-------------------------------------------------------------------+

--
uberathlete
------------------------------------------------------------------------
uberathlete's Profile: http://www.excelforum.com/member.php...o&userid=28388
View this thread: http://www.excelforum.com/showthread...hreadid=483887

First, you can attach files when you post through excelforum, but most of the
people who connect directly to the newsgroups won't take the time to visit
excelforum to download your file. You may just want to post in plain text.

If this code works for you, then your explanation was sufficient:

Sub abc()
Set rng = Range(Range("B4"), Cells(Rows.Count, 2).End(xlUp))
For Each cell In rng
if lcase(cell.value) = lcase("Total Group 2") then
exit for
end if
If Not IsEmpty(cell) _
And InStr(1, cell.Value, "total", vbTextCompare) = 0 Then
Range("D3").Copy cell.Offset(0, 2)
End If
Next cell
End Sub

I changed some of the indenting--just because I like it that way. It won't
change your output.

uberathlete wrote:

Hi all! I've got this macro in Sheet 1 (attached):

Sub abc()
Set rng = Range(Range("B4"), _
Cells(Rows.Count, 2).End(xlUp))
For Each cell In rng
If Not IsEmpty(cell) And _
InStr(1, cell.Value, "total", _
vbTextCompare) = 0 Then
Range("D3").Copy cell.Offset(0, 2)
End If
Next
End Sub

and it basically autofills all cells in column D from D3 downwards
except for those cells with no corresponding value in the B column and
those with "Total". I would just like to modify the macro such that the
autofill stops once it reaches the F cell whose corresponding column B
cell has a value of "Total Group 2". Anyone know how this can be done?
Any help will be greatly appreciated. Thanks!

+-------------------------------------------------------------------+
|Filename: Sheet 1.JPG |
|Download: http://www.excelforum.com/attachment.php?postid=4027 |
+-------------------------------------------------------------------+

--
uberathlete
------------------------------------------------------------------------
uberathlete's Profile: http://www.excelforum.com/member.php...o&userid=28388
View this thread: http://www.excelforum.com/showthread...hreadid=483887

--

Dave Peterson