Please Help

D

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

Atishoo

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

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
 
D

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 -
Click to expand...

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

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 -
Click to expand...

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,
 
D

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 -
Click to expand...

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

Atishoo

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

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)
Click to expand...

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

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)
Click to expand...

Thanks Simon I will do register ...
 
A

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
 
D

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
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help please 1
Rows to Columns - Diff Size Rows of Data 4
Copy Specific Data 4
Help: Find Matching Cells, If - Then Move Cell With Self 2
How do I setup a cascading combo boxes? 9
Please help me sort then extract the data to new workbooks 7
vLookUp return several columns? 5
Read text into Excel 5

Top