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