If then statements have for some reason left me dumbfounded!
This is what I need to do in VBA:
Look in Cell A1 for "*Rep Summary", if it exists look in cell D10 for
the reps name and give me that name in cell q10. If "* Rep Summary",
does not exist then return nothing.
Lastly; repeat this down the column Q:Q, until there is no data left in
Column A:A.

Thanks for your help...this one as I stated before as left me with a
definite "DUUUHHHH!"

This formula goes in Q10:

=if(a1="*Rep Summary",d10,"")

Watch out. Once you used "*Rep Summary" and the other time "* Rep Summary".

If a1 can contain other stuff along with "Rep Summary", you could use:

=if(countif(a1,"*rep summary*")0,d10,"")

(the asterisks are wild cards in that last formula.)

And drag it down as far as you need.

Debra Dalgleish has instructions with pictures:
http://contextures.com/xlDataEntry01.html#Mouse

" wrote:

If then statements have for some reason left me dumbfounded!
This is what I need to do in VBA:
Look in Cell A1 for "*Rep Summary", if it exists look in cell D10 for
the reps name and give me that name in cell q10. If "* Rep Summary",
does not exist then return nothing.
Lastly; repeat this down the column Q:Q, until there is no data left in
Column A:A.

Thanks for your help...this one as I stated before as left me with a
definite "DUUUHHHH!"

--

Dave Peterson

Dave,

Thanks for the help...but how would I go about placing this in VBA?
Currently I do this via formulation and when I send out I copy paste
into another workbook. The reason I want to convert to VBA is that I
can send out the actual workbook and stop the double work.
As for your "Watch Out"; I didn't state it plainly. If "* Rep Summary"
does not exist in cell A1, do nothing. Does that help in explaining
what I need it to do?

Thanks,

Hans

This formula:

=if(a1="*Rep Summary",d10,"")

Seems kind of strange to copy down a range. In most cases I've seen, when I
want to copy down a range, the formula refers to cells on the same row--not a
cell 9 rows down.

But you can do this kind of thing:

Option Explicit
sub testme()
dim LastRow as long
with worksheets("Sheet99")
lastrow = .cells(.rows.count,"A").end(xlup).row

.range("q1:Q" & lastrow).formula _
= "=if(a1=""* Rep Summary"",d10,"""")"
end with
end sub

But I ended up with formulas in Q1:Qxxx that looked like:

=IF(A1="* Rep Summary",D10,"")
=IF(A2="* Rep Summary",D11,"")
=IF(A3="* Rep Summary",D12,"")
=IF(A4="* Rep Summary",D13,"")
=IF(A5="* Rep Summary",D14,"")
=IF(A6="* Rep Summary",D15,"")
=IF(A7="* Rep Summary",D16,"")
=IF(A8="* Rep Summary",D17,"")
=IF(A9="* Rep Summary",D18,"")
=IF(A10="* Rep Summary",D19,"")

And that just looks pretty weird to me.


" wrote:

Dave,

Thanks for the help...but how would I go about placing this in VBA?
Currently I do this via formulation and when I send out I copy paste
into another workbook. The reason I want to convert to VBA is that I
can send out the actual workbook and stop the double work.
As for your "Watch Out"; I didn't state it plainly. If "* Rep Summary"
does not exist in cell A1, do nothing. Does that help in explaining
what I need it to do?

Thanks,

Hans

--

Dave Peterson

Dave,
Your right, it is weird...I had a typo. It should have read =if(a1="*
Rep Summary",d1,"").
I have not tried your solution yet.
If I change the typo, will your solution work or is there another
change that I need to make?

Thanks for everything

Hans

You'll have to fix the formula in the code.

But you should save your work before you try it. Then if it doesn't work, you
can close without saving.

" wrote:

Dave,
Your right, it is weird...I had a typo. It should have read =if(a1="*
Rep Summary",d1,"").
I have not tried your solution yet.
If I change the typo, will your solution work or is there another
change that I need to make?

Thanks for everything

Hans

--

Dave Peterson