Hi everyone,
I tried lookup and pivot table/report but could not do what I am trying to
accomplish.
I am able to do it in access by creating a report, but my boss would like it
to be done in excel

I generate an excel sheet like this:

Owner Beneficiary Policy# Company
JC MM 1234 A
JC MMC 1245 B
LisaC MA 654 A
LisaC MA 789 B
Mac BA 254 C
Mac BA 2541 C
Mac BA 2542 C


Is it possible to create group this info in another sheet like this:

Owner: JC
Beneficiary: MM

Policy# Company
1234 A

Owner: JC
Beneficiary: MMC

Policy# Company
1245 B

Owner: LisaC
Beneficiary: MA

Policy# Company
654 A
789 B

Owner: Mac
Beneficiary: BA

Policy# Company
789 B
254 C
2541 C
2542 C

Thank you.

--
Message posted via http://www.officekb.com

I'm not sure how that 789 policy showed up in in LisaC's and Mac's data--I'm
guessing it was a typo in the post.

If that's true, then this seemed to work ok for me:

Option Explicit
Sub testme()

Dim CurWks As Worksheet
Dim RptWks As Worksheet
Dim iRow As Long
Dim oRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Set CurWks = Worksheets("Sheet1")
Set RptWks = Worksheets.Add

With CurWks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
oRow = -1
For iRow = FirstRow To LastRow
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value _
And .Cells(iRow, "B").Value = .Cells(iRow - 1, "B").Value Then
'same group, do nothing special
Else
'different group, do headers
oRow = oRow + 2
RptWks.Cells(oRow, "A").Value _
= "Owner: " & .Cells(iRow, "A").Value

oRow = oRow + 1
RptWks.Cells(oRow, "A").Value _
= "Beneficiary: " & .Cells(iRow, "A").Value

oRow = oRow + 2
RptWks.Cells(oRow, "A").Value = "Policy#"
RptWks.Cells(oRow, "B").Value = "Company"

End If

'do the policy stuff
oRow = oRow + 1
RptWks.Cells(oRow, "A").Value = "'" & .Cells(iRow, "C").Value
RptWks.Cells(oRow, "B").Value = "'" & .Cells(iRow, "D").Value
Next iRow
End With

End Sub

Personally, I'd use the data in the first sheet. I could apply
data|filter|autofilter and see exactly what I wanted. I could do sorts and
other stuff lots easier than the other version.



"ielmrani via OfficeKB.com" wrote:

Hi everyone,
I tried lookup and pivot table/report but could not do what I am trying to
accomplish.
I am able to do it in access by creating a report, but my boss would like it
to be done in excel

I generate an excel sheet like this:

Owner Beneficiary Policy# Company
JC MM 1234 A
JC MMC 1245 B
LisaC MA 654 A
LisaC MA 789 B
Mac BA 254 C
Mac BA 2541 C
Mac BA 2542 C


Is it possible to create group this info in another sheet like this:

Owner: JC
Beneficiary: MM

Policy# Company
1234 A

Owner: JC
Beneficiary: MMC

Policy# Company
1245 B

Owner: LisaC
Beneficiary: MA

Policy# Company
654 A
789 B

Owner: Mac
Beneficiary: BA

Policy# Company
789 B
254 C
2541 C
2542 C

Thank you.

--
Message posted via http://www.officekb.com

--

Dave Peterson

Thank you so much. I'll try it and I'll let you know.

Dave Peterson wrote:
I'm not sure how that 789 policy showed up in in LisaC's and Mac's data--I'm
guessing it was a typo in the post.

If that's true, then this seemed to work ok for me:

Option Explicit
Sub testme()

Dim CurWks As Worksheet
Dim RptWks As Worksheet
Dim iRow As Long
Dim oRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Set CurWks = Worksheets("Sheet1")
Set RptWks = Worksheets.Add

With CurWks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
oRow = -1
For iRow = FirstRow To LastRow
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value _
And .Cells(iRow, "B").Value = .Cells(iRow - 1, "B").Value Then
'same group, do nothing special
Else
'different group, do headers
oRow = oRow + 2
RptWks.Cells(oRow, "A").Value _
= "Owner: " & .Cells(iRow, "A").Value

oRow = oRow + 1
RptWks.Cells(oRow, "A").Value _
= "Beneficiary: " & .Cells(iRow, "A").Value

oRow = oRow + 2
RptWks.Cells(oRow, "A").Value = "Policy#"
RptWks.Cells(oRow, "B").Value = "Company"

End If

'do the policy stuff
oRow = oRow + 1
RptWks.Cells(oRow, "A").Value = "'" & .Cells(iRow, "C").Value
RptWks.Cells(oRow, "B").Value = "'" & .Cells(iRow, "D").Value
Next iRow
End With

End Sub

Personally, I'd use the data in the first sheet. I could apply
data|filter|autofilter and see exactly what I wanted. I could do sorts and
other stuff lots easier than the other version.

Hi everyone,
I tried lookup and pivot table/report but could not do what I am trying to
[quoted text clipped - 47 lines]
--
Message posted via http://www.officekb.com


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1

Dave,
I used codes in Access but never in excel. How do I make this work?
Please provide me with step by step. thank you.
By the way you're right 789 was not supposed by there, it was a typo.

Dave Peterson wrote:
I'm not sure how that 789 policy showed up in in LisaC's and Mac's data--I'm
guessing it was a typo in the post.

If that's true, then this seemed to work ok for me:

Option Explicit
Sub testme()

Dim CurWks As Worksheet
Dim RptWks As Worksheet
Dim iRow As Long
Dim oRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Set CurWks = Worksheets("Sheet1")
Set RptWks = Worksheets.Add

With CurWks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
oRow = -1
For iRow = FirstRow To LastRow
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value _
And .Cells(iRow, "B").Value = .Cells(iRow - 1, "B").Value Then
'same group, do nothing special
Else
'different group, do headers
oRow = oRow + 2
RptWks.Cells(oRow, "A").Value _
= "Owner: " & .Cells(iRow, "A").Value

oRow = oRow + 1
RptWks.Cells(oRow, "A").Value _
= "Beneficiary: " & .Cells(iRow, "A").Value

oRow = oRow + 2
RptWks.Cells(oRow, "A").Value = "Policy#"
RptWks.Cells(oRow, "B").Value = "Company"

End If

'do the policy stuff
oRow = oRow + 1
RptWks.Cells(oRow, "A").Value = "'" & .Cells(iRow, "C").Value
RptWks.Cells(oRow, "B").Value = "'" & .Cells(iRow, "D").Value
Next iRow
End With

End Sub

Personally, I'd use the data in the first sheet. I could apply
data|filter|autofilter and see exactly what I wanted. I could do sorts and
other stuff lots easier than the other version.

Hi everyone,
I tried lookup and pivot table/report but could not do what I am trying to
[quoted text clipped - 47 lines]
--
Message posted via http://www.officekb.com


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1

Dave,
This is amazing. It works. What I did is go to sheet I right click it and
added the code. I open the code and click F5 to run it. can it be make as
macro I am not sure how to do that.
Thanks

ielmrani wrote:
Dave,
I used codes in Access but never in excel. How do I make this work?
Please provide me with step by step. thank you.
By the way you're right 789 was not supposed by there, it was a typo.

I'm not sure how that 789 policy showed up in in LisaC's and Mac's data--I'm
guessing it was a typo in the post.
[quoted text clipped - 56 lines]
--
Message posted via http://www.officekb.com

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1

This actually belongs in a General module--not behind a worksheet.

You may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

"ielmrani via OfficeKB.com" wrote:

Dave,
This is amazing. It works. What I did is go to sheet I right click it and
added the code. I open the code and click F5 to run it. can it be make as
macro I am not sure how to do that.
Thanks

ielmrani wrote:
Dave,
I used codes in Access but never in excel. How do I make this work?
Please provide me with step by step. thank you.
By the way you're right 789 was not supposed by there, it was a typo.

I'm not sure how that 789 policy showed up in in LisaC's and Mac's data--I'm
guessing it was a typo in the post.
[quoted text clipped - 56 lines]
--
Message posted via http://www.officekb.com

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1

--

Dave Peterson

Thanks Dave,
I only give you a sample of what I was trying to do. Now I am trying to
apply your code to a real sheet. I revised the code but it's not working.
Anytime I run the code it creates a new sheet, can it populate the data in an
existing sheet?
I tried to add new columns but they're not appearing in the sheet when I run
the code.

Please bear with me I am not an expert in excel, I am trying though. Thanks
so much.

Here is what I did so far:

Sub testme()

Dim CurWks As Worksheet
Dim RptWks As Worksheet
Dim iRow As Long
Dim oRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Set CurWks = Worksheets("Sheet1")
Set RptWks = Worksheets.Add

With CurWks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
oRow = -1
For iRow = FirstRow To LastRow
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value _
And .Cells(iRow, "B").Value = .Cells(iRow - 1, "B").Value Then

'same group, do nothing special
Else
'different group, do headers
oRow = oRow + 2
RptWks.Cells(oRow, "A").Value _
= "Owner: " & .Cells(iRow, "A").Value

oRow = oRow + 1
RptWks.Cells(oRow, "A").Value _
= "Beneficiary: " & .Cells(iRow, "A").Value

oRow = oRow + 2
RptWks.Cells(oRow, "A").Value = "Policy#"
RptWks.Cells(oRow, "B").Value = "Company"
RptWks.Cells(oRow, "C").Value = "Effective Date"
RptWks.Cells(oRow, "D").Value = "Face Amount"
RptWks.Cells(oRow, "E").Value = "Cash Value"
RptWks.Cells(oRow, "C").Value = "Surrender Value"
RptWks.Cells(oRow, "C").Value = "Annual Premium"
RptWks.Cells(oRow, "C").Value = "Policy Type"

End If

'do the policy stuff
oRow = oRow + 1
RptWks.Cells(oRow, "A").Value = "'" & .Cells(iRow, "C").Value
RptWks.Cells(oRow, "B").Value = "'" & .Cells(iRow, "D").Value
RptWks.Cells(oRow, "C").Value = "'" & .Cells(iRow, "E").Value
RptWks.Cells(oRow, "D").Value = "'" & .Cells(iRow, "F").Value
RptWks.Cells(oRow, "E").Value = "'" & .Cells(iRow, "G").Value
RptWks.Cells(oRow, "F").Value = "'" & .Cells(iRow, "H").Value
RptWks.Cells(oRow, "G").Value = "'" & .Cells(iRow, "I").Value
RptWks.Cells(oRow, "H").Value = "'" & .Cells(iRow, "J").Value
Next iRow
End With

End Sub



Dave Peterson wrote:
This actually belongs in a General module--not behind a worksheet.

You may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Dave,
This is amazing. It works. What I did is go to sheet I right click it and
[quoted text clipped - 16 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1

This one works great. I just want to know how to add the data to an existing
sheet instead of creating a new one everytime I run it. thanks

Sub testme()

Dim CurWks As Worksheet
Dim RptWks As Worksheet
Dim iRow As Long
Dim oRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Set CurWks = Worksheets("Sheet1")
Set RptWks = Worksheets.Add

With CurWks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
oRow = -1
For iRow = FirstRow To LastRow
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value _
And .Cells(iRow, "B").Value = .Cells(iRow - 1, "B").Value Then

'same group, do nothing special
Else
'different group, do headers
oRow = oRow + 2
RptWks.Cells(oRow, "A").Value _
= "Owner: " & .Cells(iRow, "A").Value

oRow = oRow + 1
RptWks.Cells(oRow, "A").Value _
= "Beneficiary: " & .Cells(iRow, "A").Value

oRow = oRow + 2
RptWks.Cells(oRow, "A").Value = "Policy#"
RptWks.Cells(oRow, "B").Value = "Company"
RptWks.Cells(oRow, "C").Value = "Effective Date"
RptWks.Cells(oRow, "D").Value = "Face Amount"
RptWks.Cells(oRow, "E").Value = "Cash Value"
RptWks.Cells(oRow, "F").Value = "Surrender Value"
RptWks.Cells(oRow, "G").Value = "Annual Premium"
RptWks.Cells(oRow, "H").Value = "Policy Type"

End If

'do the policy stuff
oRow = oRow + 1
RptWks.Cells(oRow, "A").Value = "'" & .Cells(iRow, "C").Value
RptWks.Cells(oRow, "B").Value = "'" & .Cells(iRow, "D").Value
RptWks.Cells(oRow, "C").Value = "'" & .Cells(iRow, "E").Value
RptWks.Cells(oRow, "D").Value = "'" & .Cells(iRow, "F").Value
RptWks.Cells(oRow, "E").Value = "'" & .Cells(iRow, "G").Value
RptWks.Cells(oRow, "F").Value = "'" & .Cells(iRow, "H").Value
RptWks.Cells(oRow, "G").Value = "'" & .Cells(iRow, "I").Value
RptWks.Cells(oRow, "H").Value = "'" & .Cells(iRow, "J").Value
Next iRow
End With

End Sub


ielmrani wrote:
Thanks Dave,
I only give you a sample of what I was trying to do. Now I am trying to
apply your code to a real sheet. I revised the code but it's not working.
Anytime I run the code it creates a new sheet, can it populate the data in an
existing sheet?
I tried to add new columns but they're not appearing in the sheet when I run
the code.

Please bear with me I am not an expert in excel, I am trying though. Thanks
so much.

Here is what I did so far:

Sub testme()

Dim CurWks As Worksheet
Dim RptWks As Worksheet
Dim iRow As Long
Dim oRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Set CurWks = Worksheets("Sheet1")
Set RptWks = Worksheets.Add

With CurWks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
oRow = -1
For iRow = FirstRow To LastRow
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value _
And .Cells(iRow, "B").Value = .Cells(iRow - 1, "B").Value Then

'same group, do nothing special
Else
'different group, do headers
oRow = oRow + 2
RptWks.Cells(oRow, "A").Value _
= "Owner: " & .Cells(iRow, "A").Value

oRow = oRow + 1
RptWks.Cells(oRow, "A").Value _
= "Beneficiary: " & .Cells(iRow, "A").Value

oRow = oRow + 2
RptWks.Cells(oRow, "A").Value = "Policy#"
RptWks.Cells(oRow, "B").Value = "Company"
RptWks.Cells(oRow, "C").Value = "Effective Date"
RptWks.Cells(oRow, "D").Value = "Face Amount"
RptWks.Cells(oRow, "E").Value = "Cash Value"
RptWks.Cells(oRow, "C").Value = "Surrender Value"
RptWks.Cells(oRow, "C").Value = "Annual Premium"
RptWks.Cells(oRow, "C").Value = "Policy Type"

End If

'do the policy stuff
oRow = oRow + 1
RptWks.Cells(oRow, "A").Value = "'" & .Cells(iRow, "C").Value
RptWks.Cells(oRow, "B").Value = "'" & .Cells(iRow, "D").Value
RptWks.Cells(oRow, "C").Value = "'" & .Cells(iRow, "E").Value
RptWks.Cells(oRow, "D").Value = "'" & .Cells(iRow, "F").Value
RptWks.Cells(oRow, "E").Value = "'" & .Cells(iRow, "G").Value
RptWks.Cells(oRow, "F").Value = "'" & .Cells(iRow, "H").Value
RptWks.Cells(oRow, "G").Value = "'" & .Cells(iRow, "I").Value
RptWks.Cells(oRow, "H").Value = "'" & .Cells(iRow, "J").Value
Next iRow
End With

End Sub

This actually belongs in a General module--not behind a worksheet.

[quoted text clipped - 6 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1

What do you want to do with any existing data that's in that existing sheet?

This:
Set RptWks = Worksheets.Add
could be changed to:
Set RptWks = Worksheets("SomeExistingSheetNameHere")

And then to find the next available row on that sheet:

This:
oRow = -1
becomes
with rptwks
orow = .cells(.rows.count,"A").end(xlup).row
end with

Later, the code is adding 2 to the row number, so you should still be double
spaced.




"ielmrani via OfficeKB.com" wrote:

Thanks Dave,
I only give you a sample of what I was trying to do. Now I am trying to
apply your code to a real sheet. I revised the code but it's not working.
Anytime I run the code it creates a new sheet, can it populate the data in an
existing sheet?
I tried to add new columns but they're not appearing in the sheet when I run
the code.

Please bear with me I am not an expert in excel, I am trying though. Thanks
so much.

Here is what I did so far:

Sub testme()

Dim CurWks As Worksheet
Dim RptWks As Worksheet
Dim iRow As Long
Dim oRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Set CurWks = Worksheets("Sheet1")
Set RptWks = Worksheets.Add

With CurWks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
oRow = -1
For iRow = FirstRow To LastRow
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value _
And .Cells(iRow, "B").Value = .Cells(iRow - 1, "B").Value Then

'same group, do nothing special
Else
'different group, do headers
oRow = oRow + 2
RptWks.Cells(oRow, "A").Value _
= "Owner: " & .Cells(iRow, "A").Value

oRow = oRow + 1
RptWks.Cells(oRow, "A").Value _
= "Beneficiary: " & .Cells(iRow, "A").Value

oRow = oRow + 2
RptWks.Cells(oRow, "A").Value = "Policy#"
RptWks.Cells(oRow, "B").Value = "Company"
RptWks.Cells(oRow, "C").Value = "Effective Date"
RptWks.Cells(oRow, "D").Value = "Face Amount"
RptWks.Cells(oRow, "E").Value = "Cash Value"
RptWks.Cells(oRow, "C").Value = "Surrender Value"
RptWks.Cells(oRow, "C").Value = "Annual Premium"
RptWks.Cells(oRow, "C").Value = "Policy Type"

End If

'do the policy stuff
oRow = oRow + 1
RptWks.Cells(oRow, "A").Value = "'" & .Cells(iRow, "C").Value
RptWks.Cells(oRow, "B").Value = "'" & .Cells(iRow, "D").Value
RptWks.Cells(oRow, "C").Value = "'" & .Cells(iRow, "E").Value
RptWks.Cells(oRow, "D").Value = "'" & .Cells(iRow, "F").Value
RptWks.Cells(oRow, "E").Value = "'" & .Cells(iRow, "G").Value
RptWks.Cells(oRow, "F").Value = "'" & .Cells(iRow, "H").Value
RptWks.Cells(oRow, "G").Value = "'" & .Cells(iRow, "I").Value
RptWks.Cells(oRow, "H").Value = "'" & .Cells(iRow, "J").Value
Next iRow
End With

End Sub

Dave Peterson wrote:
This actually belongs in a General module--not behind a worksheet.

You may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Dave,
This is amazing. It works. What I did is go to sheet I right click it and
[quoted text clipped - 16 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1

--

Dave Peterson

thanks Dave. I have to tell you that I posted this question in other forum
and no one gave me the answer. It's really working .

Dave Peterson wrote:
What do you want to do with any existing data that's in that existing sheet?

This:
Set RptWks = Worksheets.Add
could be changed to:
Set RptWks = Worksheets("SomeExistingSheetNameHere")

And then to find the next available row on that sheet:

This:
oRow = -1
becomes
with rptwks
orow = .cells(.rows.count,"A").end(xlup).row
end with

Later, the code is adding 2 to the row number, so you should still be double
spaced.

Thanks Dave,
I only give you a sample of what I was trying to do. Now I am trying to
[quoted text clipped - 81 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1

Glad it does what you want.

"ielmrani via OfficeKB.com" wrote:

thanks Dave. I have to tell you that I posted this question in other forum
and no one gave me the answer. It's really working .

Dave Peterson wrote:
What do you want to do with any existing data that's in that existing sheet?

This:
Set RptWks = Worksheets.Add
could be changed to:
Set RptWks = Worksheets("SomeExistingSheetNameHere")

And then to find the next available row on that sheet:

This:
oRow = -1
becomes
with rptwks
orow = .cells(.rows.count,"A").end(xlup).row
end with

Later, the code is adding 2 to the row number, so you should still be double
spaced.

Thanks Dave,
I only give you a sample of what I was trying to do. Now I am trying to
[quoted text clipped - 81 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1

--

Dave Peterson

I made it work except for 2 things.
One:

when I run it these columns are not formatted in accounting format.

for example:
Face Amount Annual Premium Amount

2000000 3500000.32 6000000

I would like the amount to be this way

2,000,000 and 3,500,000.32 and 6,000,000

Two:

The last 3 lines always end with null data. Like this:

Owner:
Beneficiary

Company Policy # etc

No data just headings. How do I get rid of these lines?

Thanks

Dave Peterson wrote:
Glad it does what you want.

thanks Dave. I have to tell you that I posted this question in other forum
and no one gave me the answer. It's really working .
[quoted text clipped - 27 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1

Record a macro when you change the formatting for those fields/columns. Include
that at the bottom of your existing macro.

And I'd bet you had something (a space character) in that last row. Clean up
your data and try again.

"ielmrani via OfficeKB.com" wrote:

I made it work except for 2 things.
One:

when I run it these columns are not formatted in accounting format.

for example:
Face Amount Annual Premium Amount

2000000 3500000.32 6000000

I would like the amount to be this way

2,000,000 and 3,500,000.32 and 6,000,000

Two:

The last 3 lines always end with null data. Like this:

Owner:
Beneficiary

Company Policy # etc

No data just headings. How do I get rid of these lines?

Thanks

Dave Peterson wrote:
Glad it does what you want.

thanks Dave. I have to tell you that I posted this question in other forum
and no one gave me the answer. It's really working .
[quoted text clipped - 27 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1

--

Dave Peterson

The reason I could not format it is because apostrophe got added to the
beginning of each number.

Dave Peterson wrote:
Record a macro when you change the formatting for those fields/columns. Include
that at the bottom of your existing macro.

And I'd bet you had something (a space character) in that last row. Clean up
your data and try again.

I made it work except for 2 things.
One:
[quoted text clipped - 34 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1

I used the apostrophe so that any leading 0's wouldn't be lost from the policy
number. You can remove it/them if you don't want them.

"ielmrani via OfficeKB.com" wrote:

The reason I could not format it is because apostrophe got added to the
beginning of each number.

Dave Peterson wrote:
Record a macro when you change the formatting for those fields/columns. Include
that at the bottom of your existing macro.

And I'd bet you had something (a space character) in that last row. Clean up
your data and try again.

I made it work except for 2 things.
One:
[quoted text clipped - 34 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1

--

Dave Peterson

Thanks Dave. I removed them from the columns with the numbers and it worked.
One more thing but I am going to try to add myself first. I want to add a
label on top of the last 2 columns.

Like this:

Surrender Value
Amount Date

Surrender Value
Amount Date

Surrender Value
Amount Date

etc..

Dave Peterson wrote:
I used the apostrophe so that any leading 0's wouldn't be lost from the policy
number. You can remove it/them if you don't want them.

The reason I could not format it is because apostrophe got added to the
beginning of each number.
[quoted text clipped - 14 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1

In my original code, this did the headers:

oRow = oRow + 2
RptWks.Cells(oRow, "A").Value = "Policy#"
RptWks.Cells(oRow, "B").Value = "Company"

I'd suggest you change it to look like:

oRow = oRow + 2
RptWks.Cells(oRow, "A").Value = "Policy#"
RptWks.Cells(oRow, "B").Value = "Company"
rptwks.cells(orow, "C").value = "Surrender" & vblf & "Amount"
rptwks.cells(orow, "D").value = "Surrender" & vblf & "Date"

You may want to include code to wraptext and autofit the columns (record a macro
to see the syntax).

"ielmrani via OfficeKB.com" wrote:

Thanks Dave. I removed them from the columns with the numbers and it worked.
One more thing but I am going to try to add myself first. I want to add a
label on top of the last 2 columns.

Like this:

Surrender Value
Amount Date

Surrender Value
Amount Date

Surrender Value
Amount Date

etc..

Dave Peterson wrote:
I used the apostrophe so that any leading 0's wouldn't be lost from the policy
number. You can remove it/them if you don't want them.

The reason I could not format it is because apostrophe got added to the
beginning of each number.
[quoted text clipped - 14 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1

--

Dave Peterson

Great. Thanks

Dave Peterson wrote:
In my original code, this did the headers:

oRow = oRow + 2
RptWks.Cells(oRow, "A").Value = "Policy#"
RptWks.Cells(oRow, "B").Value = "Company"

I'd suggest you change it to look like:

oRow = oRow + 2
RptWks.Cells(oRow, "A").Value = "Policy#"
RptWks.Cells(oRow, "B").Value = "Company"
rptwks.cells(orow, "C").value = "Surrender" & vblf & "Amount"
rptwks.cells(orow, "D").value = "Surrender" & vblf & "Date"

You may want to include code to wraptext and autofit the columns (record a macro
to see the syntax).

Thanks Dave. I removed them from the columns with the numbers and it worked.
One more thing but I am going to try to add myself first. I want to add a
[quoted text clipped - 25 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1


--
Message posted via http://www.officekb.com