Conditional Autofill

[uberathlete]

Hi all! I've got this problem and it's really bugging me!!

Say, I've got Sheet 1 (attached) and there's some formula (assume it's some VLOOKUP formula) on D3. I basically need some macro that would autofill the formula in D3 down to all the other cells in column D, EXCEPT for those cells with no corresponding value in the B column and those with "Total".

Any help would be greatly appreciated. Thanks!

[Tom Ogilvy]

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

--
Regards,
Tom Ogilvy


"uberathlete" <uberathlete.1y8j6p_1131549022.1986@excelforum-nospam.com>
wrote in message
news:uberathlete.1y8j6p_1131549022.1986@excelforum-nospam.com...
>
> Hi all! I've got this problem and it's really bugging me!!
>
> Say, I've got Sheet 1 (attached) and there's some formula (assume it's
> some VLOOKUP formula) on D3. I basically need some macro that would
> autofill the formula in D3 down to all the other cells in column D,
> EXCEPT for those cells with no corresponding value in the B column and
> those with "Total".
>
> Any help would be greatly appreciated. Thanks!
>
>
> +-------------------------------------------------------------------+
> |Filename: Sheet 1.JPG |
> |Download: http://www.excelforum.com/attachment.php?postid=4018 |
> +-------------------------------------------------------------------+
>
> --
> uberathlete
> ------------------------------------------------------------------------
> uberathlete's Profile:
http://www.excelforum.com/member.php...o&userid=28388
> View this thread: http://www.excelforum.com/showthread...hreadid=483510
>

[uberathlete]

^^ Thanks Tom. I tried it, but there seems to be a syntax error in the 3rd line.

[Tom Ogilvy]

No there isn't, but I broke it into little short baby pieces so you wouldn't
choke <g>

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

--
Regards,
Tom Ogilvy


"uberathlete" <uberathlete.1y8l9p_1131551719.9349@excelforum-nospam.com>
wrote in message
news:uberathlete.1y8l9p_1131551719.9349@excelforum-nospam.com...
>
> ^^ Thanks Tom. I tried it, but there seems to be a syntax error in the
> 3rd line.
>
>
> --
> uberathlete
> ------------------------------------------------------------------------
> uberathlete's Profile:
http://www.excelforum.com/member.php...o&userid=28388
> View this thread: http://www.excelforum.com/showthread...hreadid=483510
>

[uberathlete]

^^ Thanks!