Please Help

[Dave]

Hi,

I am using Excel 2003. I have a spreadsheet with the name of
"Profiles Research Check List". It contains the following entries in
rows e4 through e11 (row heading) The data is contained in the cells
infront of the row label


Entity Name: H4
DBA Name: H5
Entity Name City, State Zip: H8,I8,J8
Client Representative Name:: H9
Client Representative Address: H10
Client Representative: City, State Zip: H11,I11,J11


I am taking this information and generating letters from this data to
my clients.The name of the sheet where I am inserting the addresses
is
named "Conditional Letter" . Here is the problem most of the times
the letter is addressed to the Entity Name like such:


Entity Name
Address:
City, State Zip:


But some times only when there is data in the Client Representative
Address field (h10) I have to format the address as below


Client Representative Name:
RE: Entity Name
Client Representative Address:
Client Representative City, State Zip


So I need a macro which will check for data in h10 (Client
Representative Address ) and then if there is data in h10 then
create my address as above in the "Conditional Letter" spreadsheet. I
would like to start the insertion from cell b13 downward. And if
there
is nor information in h10 (Client Representative Address ) then
create my address as the first example in the "Conditional Letter"
spreadsheet


I hope I was clear its kind of hard to explain.


Thanks is advance!

 

[Atishoo]

Hi
if there is no data in H10 where do you get your "address" from for the
first address layout?

 

[Simon Lloyd]

This should do what you need although you didnt supply a range (cell
where the entity address is loacted but you can change that in the code

Code
-------------------
Sub create_address(
If Sheets("Profiles Research Check List").Range("H10") <> "" The
With Sheets("Conditional Letter"
.Range("B13").Value = Sheets("Profiles Research Check List").Range("H9").Valu
.Range("B14").Value = "Re: " & Sheets("Profiles Research Check List").Range("H4").Valu
.Range("B15").Value = Sheets("Profiles Research Check List").Range("H9").Valu
.Range("B16").Value = Sheets("Profiles Research Check List").Range("H10").Valu
.Range("B17").Value = Sheets("Profiles Research Check List").Range("H11").Value & ", "
& Sheets("Profiles Research Check List").Range("I11").Value & ", "
& Sheets("Profiles Research Check List").Range("J11").Valu
End Wit
Els
With Sheets("Conditional Letter"
.Range("B13").Value = Sheets("Profiles Research Check List").Range("H4").Valu
.Range("B14").Value = "change the code to the cell for address here" 'Sheets("Profiles Research Check List").Range("H4").Valu
.Range("B15").Value = Sheets("Profiles Research Check List").Range("H8").Value & ", "
& Sheets("Profiles Research Check List").Range("I8").Value & ", "
& Sheets("Profiles Research Check List").Range("J8").Valu
End Wit
End I
End Su

-------------------

Hi

I am using Excel 2003. I have a spreadsheet with the name o
"Profiles Research Check List". It contains the following entries i
rows e4 through e11 (row heading) The data is contained in the cell
infront of the row labe


Entity Name: H
DBA Name: H
Entity Name City, State Zip: H8,I8,J
Client Representative Name:: H
Client Representative Address: H1
Client Representative: City, State Zip: H11,I11,J1


I am taking this information and generating letters from this data t
my clients.The name of the sheet where I am inserting the addresse
i
named "Conditional Letter" . Here is the problem most of the time
the letter is addressed to the Entity Name like such


Entity Nam
Address
City, State Zip


But some times only when there is data in the Client Representativ
Address field (h10) I have to format the address as belo


Client Representative Name
RE: Entity Nam
Client Representative Address
Client Representative City, State Zi


So I need a macro which will check for data in h10 (Clien
Representative Address ) and then if there is data in h10 the
create my address as above in the "Conditional Letter" spreadsheet.
would like to start the insertion from cell b13 downward. And i
ther
is nor information in h10 (Client Representative Address ) the
create my address as the first example in the "Conditional Letter
spreadshee


I hope I was clear its kind of hard to explain


Thanks is advance

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[Atishoo]

I did a similar project to yours I found it worth keeping all the ranges
seperate so its easy to alter when the format of the worksheet alters (which
I chnage around often)

Set D = Worksheets("conditional letter")
Set E = Worksheets("Profiles Research Check List")

If Range("H10").Value = "" Then
D.Range("B13") = E.Range("H4")
D.Range("B14") = "Cell containing Address Info"
D.Range("B15") = E.Range("H8")
D.Range("C15") = E.Range("I8")
D.Range("D15") = E.Range("J8")
Else
D.Range("B13") = E.Range("H9")
D.Range("B14") = "Re:" & E.Range("H4")
D.Range("B15") = E.Range("H10")
D.Range("B16") = E.Range("H11")
D.Range("C16") = E.Range("I11")
D.Range("D16") = E.Range("J11")
End If

 

[Dave]

I did a similar project to yours I found it worth keeping all the ranges
seperate so its easy to alter when the format of the worksheet alters (which
I chnage around often)

Set D = Worksheets("conditional letter")
Set E = Worksheets("Profiles Research Check List")

    If Range("H10").Value = "" Then
        D.Range("B13") = E.Range("H4")
        D.Range("B14") = "Cell containing Address Info"
        D.Range("B15") = E.Range("H8")
        D.Range("C15") = E.Range("I8")
        D.Range("D15") = E.Range("J8")
        Else
        D.Range("B13") = E.Range("H9")
        D.Range("B14") = "Re:" & E.Range("H4")
        D.Range("B15") = E.Range("H10")
        D.Range("B16") = E.Range("H11")
        D.Range("C16") = E.Range("I11")
        D.Range("D16") = E.Range("J11")
    End If







- Show quoted text -

Thanks guys if if there is no data in H10 where do you get your
"address" from H9.

 

[Simon Lloyd]

Try this
Code
-------------------
Sub create_address(
If Sheets("Profiles Research Check List").Range("H10") <> "" The
With Sheets("Conditional Letter"
.Range("B13").Value = Sheets("Profiles Research Check List").Range("H9").Valu
.Range("B14").Value = "Re: " & Sheets("Profiles Research Check List").Range("H4").Valu
.Range("B15").Value = Sheets("Profiles Research Check List").Range("H9").Valu
.Range("B16").Value = Sheets("Profiles Research Check List").Range("H10").Valu
.Range("B17").Value = Sheets("Profiles Research Check List").Range("H11").Value & ", "
& Sheets("Profiles Research Check List").Range("I11").Value & ", "
& Sheets("Profiles Research Check List").Range("J11").Valu
End Wit
Els
With Sheets("Conditional Letter"
.Range("B13").Value = Sheets("Profiles Research Check List").Range("H4").Valu
.Range("B14").Value = Sheets("Profiles Research Check List").Range("H9").Valu
.Range("B15").Value = Sheets("Profiles Research Check List").Range("H8").Value & ", "
& Sheets("Profiles Research Check List").Range("I8").Value & ", "
& Sheets("Profiles Research Check List").Range("J8").Valu
End Wit
End I
End Su
-------------------

(http://www.thecodecage.com/forumz/showthread.php?t=121974-) Hide quoted
text -

Thanks guys if if there is no data in H10 where do you get your
"address" from H9.

--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)

 

[Dave]

Try this:
Code:
--------------------
    Sub create_address()
  If Sheets("Profiles Research Check List").Range("H10") <> "" Then
  With Sheets("Conditional Letter")
  .Range("B13").Value = Sheets("Profiles Research Check List").Range("H9").Value
  .Range("B14").Value = "Re: " & Sheets("Profiles Research Check List").Range("H4").Value
  .Range("B15").Value = Sheets("Profiles Research Check List").Range("H9").Value
  .Range("B16").Value = Sheets("Profiles Research Check List").Range("H10").Value
  .Range("B17").Value = Sheets("Profiles Research Check List").Range("H11").Value & ", " _
  & Sheets("Profiles Research Check List").Range("I11").Value & ", " _
  & Sheets("Profiles Research Check List").Range("J11").Value
  End With
  Else
  With Sheets("Conditional Letter")
  .Range("B13").Value = Sheets("Profiles Research Check List").Range("H4").Value
  .Range("B14").Value = Sheets("Profiles Research Check List").Range("H9").Value
  .Range("B15").Value = Sheets("Profiles Research Check List").Range("H8").Value & ", " _
  & Sheets("Profiles Research Check List").Range("I8").Value & ", " _
  & Sheets("Profiles Research Check List").Range("J8").Value
  End With
  End If
  End Sub
--------------------
Dave;439974 Wrote:







--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile:http://www.thecodecage.com/forumz/member.php?userid=1
View this thread:http://www.thecodecage.com/forumz/showthread.php?t=121974- Hide quoted text -

- Show quoted text -

Thanks again I copied the code in my Conditional Letter and when I run
it I am getting "subscript out of range" error message. thanks again
Simon,

 

[Simon Lloyd]

If you are getting subscript out of range error and i assume its erro
9, then its because either there is a typo in the code on the shee
names or your worksheets aren't named the same as the code or there is
typo in them or leading or trailing spaces or the sheet simply doesn'
exist

On Aug 3, 2:32*pm, Simon Lloyd <[email protected]
wrote
Thanks again I copied the code in my Conditional Letter and when I ru
it I am getting "subscript out of range" error message. thanks agai
Simon

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[Dave]

Thanks again I copied the code in my Conditional Letter and when I run
it  I am getting "subscript out of range" error message.  thanks again
Simon,- Hide quoted text -

- Show quoted text -

hi guys , finaly I have the code working correctly now I have a
question on what is the best way to fire the macro. Ideally i will
like it to fire as soon as someone enters text in (Profiles Research
Check List").Range("H10"). But I do not know if this is possible. I
am open to any suggestions thanks again for all your help and sorry
for the bother!!

 

[Simon Lloyd]

Put this in the Profiles Research Check List workshee
Code
-------------------
Private Sub Worksheet_Change(ByVal Target As Range
If Target.Cells.Count > 1 Then Exit Su
If Target.Address <> "$H$10" Then Exit Su
If Me.Range("H10") <> "" The
With Sheets("Conditional Letter"
.Range("B13").Value = Me.Range("H9").Valu
.Range("B14").Value = "Re: " & Me.Range("H4").Valu
.Range("B15").Value = Me.Range("H9").Valu
.Range("B16").Value = Me.Range("H10").Valu
.Range("B17").Value = Me.Range("H11").Value & ", "
& Me.Range("I11").Value & ", "
& Me.Range("J11").Valu
End Wit
Els
With Sheets("Conditional Letter"
.Range("B13").Value = Me.Range("H4").Valu
.Range("B14").Value = Me.Range("H9").Valu
.Range("B15").Value = Me.Range("H8").Value & ", "
& Me.Range("I8").Value & ", "
& Me.Range("J8").Valu
End Wit
End I
End Su
-------------------
*How to Save a Worksheet Event Macro*
1. *Copy* the macro using *CTRL+C* keys.
2. Open your Workbook and *Right Click* on the *Worksheet's Name Tab
for the Worksheet the macro will run on.
3. *Left Click* on *View Code* in the pop up menu.
4. *Paste* the macro code using *CTRL+V*
5. Make any custom changes to the macro if needed at this time.
6. *Save* the macro in your Workbook using *CTRL+S*

hi guys , finaly I have the code working correctly now I have
question on what is the best way to fire the macro. Ideally i wil
like it to fire as soon as someone enters text in (Profiles Researc
Check List").Range("H10"). But I do not know if this is possible.
am open to any suggestions thanks again for all your help and sorr
for the bother!

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[Atishoo]

Its only a short sub I would just trigger with selection change or worksheet
selection change

 

[Dave]

Put this in the Profiles Research Check List worksheet
Code:
--------------------
    Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Cells.Count > 1 Then Exit Sub
  If Target.Address <> "$H$10" Then Exit Sub
  If Me.Range("H10") <> "" Then
  With Sheets("Conditional Letter")
  .Range("B13").Value = Me.Range("H9").Value
  .Range("B14").Value = "Re: " & Me.Range("H4").Value
  .Range("B15").Value = Me.Range("H9").Value
  .Range("B16").Value = Me.Range("H10").Value
  .Range("B17").Value = Me.Range("H11").Value & ", " _
  & Me.Range("I11").Value & ", " _
  & Me.Range("J11").Value
  End With
  Else
  With Sheets("Conditional Letter")
  .Range("B13").Value = Me.Range("H4").Value
  .Range("B14").Value = Me.Range("H9").Value
  .Range("B15").Value = Me.Range("H8").Value & ", " _
  & Me.Range("I8").Value & ", " _
  & Me.Range("J8").Value
  End With
  End If
  End Sub
--------------------
*How to Save a Worksheet Event Macro*
1. *Copy* the macro using *CTRL+C* keys.
2. Open your Workbook and *Right Click* on the *Worksheet's Name Tab*
for the Worksheet the macro will run on.
3. *Left Click* on *View Code* in the pop up menu.
4. *Paste* the macro code using *CTRL+V*
5. Make any custom changes to the macro if needed at this time.
6. *Save* the macro in your Workbook using *CTRL+S*


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)

Thanks again Simon, as I am implenting this macro I am constantly
discovering issues so here arre two more points for your
consideration:

1, Every time an address is written to Conditional Letter range
b13:b17 I must clear the old address already in this range. So how do
I do that? Adding extra code to your macro of course

2, Now I am also going to be adding Conditional Letter 2, Conditional
Letter 2 , Conditional Letter 3 upto 9 more Conditional Letter. So
how do I do that? Adding extra code to your macro again.

thanks again You are brilliant !!

 

[Simon Lloyd]

For further help with it why not join our forums (shown in the lin
below) it's completely free, if you do join you will have th
opportunity to add attachments to your posts so you can add workbooks t
better illustrate your problems and get help directly with them. Also i
you do join please post in this thread (link found below) so that peopl
who have been following or helping with this query can continue to d
so. :

Thanks again Simon, as I am implenting this macro I am constantl
discovering issues so here arre two more points for you
consideration

1, Every time an address is written to Conditional Letter rang
b13:b17 I must clear the old address already in this range. So how d
I do that? Adding extra code to your macro of cours

2, Now I am also going to be adding Conditional Letter 2, Conditiona
Letter 2 , Conditional Letter 3 upto 9 more Conditional Letter. S
how do I do that? Adding extra code to your macro again

thanks again You are brilliant !

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[Dave]

For further help with it why not join our forums (shown in the link
below) it's completely free, if you do join you will have the
opportunity to add attachments to your posts so you can add workbooks to
better illustrate your problems and get help directly with them. Also if
you do join please post in this thread (link found below) so that people
who have been following or helping with this query can continue to do
so.






--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)

Thanks Simon I will do register ...

 

[David]

Its good to Join the CodeCage community. Thanks Simon.

I still need help with my post at the thread below.

 

[Simon Lloyd]

Its good to Join the CodeCage community. Thanks Simon

I still need help with my post at the thread below.

im mobile at the moment but will take a look when im back at my p

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[Atishoo]

Hi Dave
If your sub only fires when data is entered into H10 then surely you will
only get an address formed on your second worksheet when there is a client
representative, when there is no data in H10 the sub wont fire at all so
there will be no address created for those without a representative!
You could put it in as a worksheet change so when the user selects the
conditional letter worksheet to view the addresses the sub will fire to
update them!
You can easily remove the extra data by adding = "" for the extra cells

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Set D = Worksheets("conditional letter")
Set E = Worksheets("Profiles Research Check List")

If Range("H10").Value = "" Then
D.Range("B13") = E.Range("H4")
D.Range("B14") = E.Range("H9")
D.Range("B15") = E.Range("H8") & ","
D.Range("C15") = E.Range("I8") & ","
D.Range("D15") = E.Range("J8")
D.Range("C16") = ""
D.Range("D16") = ""
Else
D.Range("B13") = E.Range("H9")
D.Range("B14") = "Re:" & E.Range("H4")
D.Range("B15") = E.Range("H10")
D.Range("C15") =""
D.Range("B16") = E.Range("H11") & ","
D.Range("C16") = E.Range("I11") & ","
D.Range("D16") = E.Range("J11")
End If
End Sub

 

[Simon Lloyd]

Davi
Thanks for taking the time to join this great forum, w
have, today, just introduced Member Only forums, why not make a pos
there with this query including a sample workbook, i will also copy som
of this thread to it for the sake of continuity - and we will get yo
sorted asap

If you need further help or assistance then use ony of the Contact U
links found at the top and bottom of every forum

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[Dave]

Hi Dave
If your sub only fires when data is entered into H10 then surely you will
only get an address formed on your second worksheet when there is a client
representative, when there is no data in H10 the sub wont fire at all so
there will be no address created for those without a representative!
You could put it in as a worksheet change so when the user selects the
conditional letter worksheet to view the addresses the sub will fire to
update them!
You can easily remove the extra data by adding = "" for the extra cells

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Set D = Worksheets("conditional letter")
Set E = Worksheets("Profiles Research Check List")

    If Range("H10").Value = "" Then
        D.Range("B13") = E.Range("H4")
        D.Range("B14") = E.Range("H9")
        D.Range("B15") = E.Range("H8") & ","
        D.Range("C15") = E.Range("I8") & ","
        D.Range("D15") = E.Range("J8")
        D.Range("C16") = ""
        D.Range("D16") = ""
        Else
        D.Range("B13") = E.Range("H9")
        D.Range("B14") = "Re:" & E.Range("H4")
        D.Range("B15") = E.Range("H10")
        D.Range("C15") =""
        D.Range("B16") = E.Range("H11") & ","
        D.Range("C16") = E.Range("I11") & ","
        D.Range("D16") = E.Range("J11")
    End If
End Sub

Please explain how to "conditional letter worksheet " Additionally can
you guide me on the following two points

1, Every time an address is written to Conditional Letter range
b13:b17 I must clear the old address already in this range. So
how do
I do that? Adding extra code to your macro of course


2, Now I am also going to be adding Conditional Letter 2,
Conditional
Letter 2 , Conditional Letter 3 upto 9 more Conditional Letter.
So
how do I do that? Adding extra code to your macro again.

thanks so much

 

[Simon Lloyd]

David, please log in to The Code Cage to this thread and supply a sampl
workbook so i can help you directly with that
Attachments.

To upload a workbook, click reply then add your few words, scroll dow
past the submit button and you will see the Manage Attachments button
this is where you get to add files for upload, if you have any troubl
please use this link or the one at the bottom of th
any page.

Please explain how to "conditional letter worksheet " Additionally ca
you guide me on the following two point

1, Every time an address is written to Conditional Letter rang
b13:b17 I must clear the old address already in this range. S
how d
I do that? Adding extra code to your macro of cours


2, Now I am also going to be adding Conditional Letter 2
Conditiona
Letter 2 , Conditional Letter 3 upto 9 more Conditional Letter
S
how do I do that? Adding extra code to your macro again

thanks so muc

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com