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:
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!

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

"Dave" wrote:

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:
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!

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") < "" Then
With Sheets("Conditional Letter")
.Range("B13").Value = Sheets("Profiles Research Check List").Range("H9").Value
.Range("B14").Value = " " & 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 = "change the code to the cell for address here" 'Sheets("Profiles Research Check List").Range("H4").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;439646 Wrote:
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:
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!


--
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/sh...d.php?t=121974

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") = "" & 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

"Simon Lloyd" wrote:


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") < "" Then
With Sheets("Conditional Letter")
.Range("B13").Value = Sheets("Profiles Research Check List").Range("H9").Value
.Range("B14").Value = " " & 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 = "change the code to the cell for address here" 'Sheets("Profiles Research Check List").Range("H4").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;439646 Wrote:
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:
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!


--
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/sh...d.php?t=121974

On Aug 3, 10:39*am, Atishoo wrote:
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") = "" & 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



"Simon Lloyd" wrote:

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") < "" Then
* With Sheets("Conditional Letter")
* .Range("B13").Value = Sheets("Profiles Research Check List").Range("H9").Value
* .Range("B14").Value = " " & 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 = "change the code to the cell for address here" 'Sheets("Profiles Research Check List").Range("H4").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;439646 Wrote:
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:
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!

--
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/sh....php?t=121974- Hide quoted text -

- Show quoted text -

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

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 = " " & 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:
On Aug 3, 10:39*am, Atishoo wrote:
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") = "" & 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



"Simon Lloyd" wrote:

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") < "" Then
* With Sheets("Conditional Letter")
* .Range("B13").Value = Sheets("Profiles Research Check
List").Range("H9").Value
* .Range("B14").Value = " " & 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 = "change the code to the cell for address
here" 'Sheets("Profiles Research Check List").Range("H4").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;439646 Wrote:
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:
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!

--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' ('The Code Cage - Microsoft Office Help -
Microsoft Office Discussion' (http://www.thecodecage.com))

------------------------------------------------------------------------
Simon Lloyd's Profile:'The Code Cage Forums - View Profile: Simon
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread:'Please Help - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh....php?t=121974-) Hide quoted
text -

- Show 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)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=121974

On Aug 3, 2:32*pm, Simon Lloyd
wrote:
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 = " " & 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:





On Aug 3, 10:39*am, Atishoo wrote:
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") = "" & 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

"Simon Lloyd" wrote:

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") < "" Then
* With Sheets("Conditional Letter")
* .Range("B13").Value = Sheets("Profiles Research Check
List").Range("H9").Value
* .Range("B14").Value = " " & 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 = "change the code to the cell for address
here" 'Sheets("Profiles Research Check List").Range("H4").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;439646 Wrote:
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:
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!

--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' ('The Code Cage - Microsoft Office Help -
Microsoft Office Discussion' (http://www.thecodecage.com))

------------------------------------------------------------------------
Simon Lloyd's Profile:'The Code Cage Forums - View Profile: Simon
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread:'Please Help - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh....php?t=121974-) Hide quoted
text -

- Show 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)
------------------------------------------------------------------------
Simon Lloyd's Profile:http://www.thecodecage.com/forumz/member.php?userid=1
View this thread:http://www.thecodecage.com/forumz/sh....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,

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

Dave;440101 Wrote:
On Aug 3, 2:32*pm, Simon Lloyd
wrote:
Dave;439974 Wrote:
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

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/sh...d.php?t=121974

On Aug 3, 2:56*pm, Dave wrote:
On Aug 3, 2:32*pm, Simon Lloyd
wrote:





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= " " & 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:

On Aug 3, 10:39*am, Atishoo wrote:
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") = "" & 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

"Simon Lloyd" wrote:

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") < "" Then
* With Sheets("Conditional Letter")
* .Range("B13").Value= Sheets("Profiles Research Check
List").Range("H9").Value
* .Range("B14").Value= " " & 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= "change the code to the cell for address
here" 'Sheets("Profiles Research Check List").Range("H4").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;439646 Wrote:
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:
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!

--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' ('The Code Cage - Microsoft Office Help -
Microsoft Office Discussion' (http://www.thecodecage.com))

------------------------------------------------------------------------
Simon Lloyd's Profile:'The Code Cage Forums - View Profile: Simon
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread:'Please Help - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh....php?t=121974-) Hide quoted
text -

- Show 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)
------------------------------------------------------------------------
Simon Lloyd's Profile:http://www.thecodecage.com/forumz/member.php?userid=1
View this thread:http://www.thecodecage.com/forumz/sh...?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,- 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!!

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 = " " & 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*
Dave;440161 Wrote:
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

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/sh...d.php?t=121974

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

"Dave" wrote:

On Aug 3, 2:56 pm, Dave wrote:
On Aug 3, 2:32 pm, Simon Lloyd
wrote:





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= " " & 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:

On Aug 3, 10:39*am, Atishoo wrote:
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") = "" & 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

"Simon Lloyd" wrote:

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") < "" Then
* With Sheets("Conditional Letter")
* .Range("B13").Value= Sheets("Profiles Research Check
List").Range("H9").Value
* .Range("B14").Value= " " & 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= "change the code to the cell for address
here" 'Sheets("Profiles Research Check List").Range("H4").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;439646 Wrote:
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:
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!

--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' ('The Code Cage - Microsoft Office Help -
Microsoft Office Discussion' (http://www.thecodecage.com))

------------------------------------------------------------------------
Simon Lloyd's Profile:'The Code Cage Forums - View Profile: Simon
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread:'Please Help - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh....php?t=121974-) Hide quoted
text -

- Show 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)
------------------------------------------------------------------------
Simon Lloyd's Profile:http://www.thecodecage.com/forumz/member.php?userid=1
View this thread:http://www.thecodecage.com/forumz/sh...?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,- 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!!

On Aug 4, 3:13*am, Simon Lloyd
wrote:
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 = " " & 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*
Dave;440161 Wrote:

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

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/sh...d.php?t=121974

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 !!

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. :)

Dave;441093 Wrote:
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

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/sh...d.php?t=121974

On Aug 4, 12:36*pm, Simon Lloyd
wrote:
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. :)

Dave;441093 Wrote:

*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

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/sh...d.php?t=121974

Thanks Simon I will do register ...

Its good to Join the CodeCage community. Thanks Simon.

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


--
David
------------------------------------------------------------------------
David's Profile: http://www.thecodecage.com/forumz/member.php?userid=630
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=121974

David;441583 Wrote:
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 pc


--
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/sh...d.php?t=121974

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") = "" & 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

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


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


--
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/sh...d.php?t=121974

On Aug 5, 8:04*am, Atishoo wrote:
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") = "" & 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

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

To upload a workbook, click reply then add your few words, scroll down
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 trouble
please use this link or the one at the bottom of the
any page.
Dave;445061 Wrote:
On Aug 5, 8:04*am, Atishoo wrote:
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") = "" & 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

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/sh...d.php?t=121974

Simon Lloyd;445398 Wrote:
David, please log in to The Code Cage to this thread and supply a sample
workbook so i can help you directly with that.
Attachments.

To upload a workbook, click reply then add your few words, scroll down
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 trouble
please use this link or the one at the bottom of the
any page.


Simon,

I am loading the file: test Ver 8.xls

thanks again


+-------------------------------------------------------------------+
|Filename: test_Ver_8.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=207|
+-------------------------------------------------------------------+

--
David
------------------------------------------------------------------------
David's Profile: http://www.thecodecage.com/forumz/member.php?userid=630
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=121974

Thank You all fr helping , I have it figured out now, thanks agian for
all your help and guidance.
thanks again


--
David
------------------------------------------------------------------------
David's Profile: http://www.thecodecage.com/forumz/member.php?userid=630
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=121974

Glad You are sorted!


--
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/sh...d.php?t=121974