Hi Paul,
Thanks its almost all what I need, but only one additional thing required
that is when ever I run a macro the out put alwasy with next row.
I mean
1st time in output sheet row 1
2 time in output sheet row2 like this it should go with next row rather then
in the same row. Can this be done? If yes it would be great help. Let me
know if possilble
Thanks
Puttu
On Mon, Mar 29, 2010 at 1:25 AM, paulwillekens <
paul.johan.willek...@gmail.com> wrote:
> Hi Puttu,
>
> Here it comes... Remark how I "unmerge" "merged fields" before
> grabbing the content... and then I "remerge" it...
>
> Let me know if this helped you out...
>
> Greetings
>
> Paul Willekens
> '=======================================================================
> Sub Normalize()
>
> Dim cAddress
> Dim cBusiness
> Dim cCity
> Dim cCompany
> Dim cContact
> Dim cState
>
> Dim nRow
>
> nRow = 4
>
> 'company name
> Sheets("Input Data").Select
> Cells(1, 1).Select
> cCompany = Selection.Value
> Sheets("Output").Select
> Cells(nRow, 1).Value = cCompany
>
> 'address
> Sheets("Input Data").Select
> Range("B12:C12").MergeCells = False
> Cells(12, 2).Select
> cAddress = Selection.Value
> Range("B12:C12").MergeCells = True
>
> Range("B13:C13").MergeCells = False
> Cells(13, 2).Select
> cAddress = cAddress & vbCrLf & Selection.Value
> Range("B13:C13").MergeCells = True
>
> Range("B14:C14").MergeCells = False
> Cells(14, 2).Select
> cCity = Selection.Value
> cAddress = cAddress & " " & cCity
> cCity = Left(cCity, (Len(cCity) - InStrRev(cCity, " ")))
> Range("B14:C14").MergeCells = True
>
> Range("B15:C15").MergeCells = False
> Cells(15, 2).Select
> cState = Selection.Value
> cAddress = cAddress & " " & cState
> cState = Trim(Replace(cState, "India", ""))
> Range("B15:C15").MergeCells = True
>
> Sheets("Output").Select
> Cells(nRow, 2).Value = cAddress
>
> 'city
> Cells(nRow, 3).Value = cCity
>
> 'state
> Cells(nRow, 4).Value = cState
>
> 'contact person
> Sheets("Input Data").Select
> Range("D11:D15").MergeCells = False
> Cells(11, 4).Select
> cContact = Selection.Value
> cContact = Trim(Replace(cContact, "Contact Person:", ""))
> Range("D11:D15").MergeCells = True
>
> Sheets("Output").Select
> Cells(nRow, 10).Value = cContact
>
> 'business details
> Sheets("Input Data").Select
> Range("B8:D8").MergeCells = False
> Cells(8, 2).Select
> cBusiness = Selection.Value
> Range("B8:D8").MergeCells = True
>
> Sheets("Output").Select
> Cells(nRow, 11).Value = cBusiness
>
> End Sub
> '=======================================================================
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our Facebook Group @
> http://www.facebook.com/group.php?gid=287779555678
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> HELP US GROW !!
>
> We reach over 6,800 subscribers worldwide and receive many nice notes about
> the learning and support from the group.Let friends and co-workers know they
> can subscribe to group at
> http://groups.google.com/group/excel-macros/subscribe
>
> To unsubscribe from this group, send email to excel-macros+
> unsubscribegooglegroups.com or reply to this email with the words "REMOVE
> ME" as the subject.
>
--
Putta
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
To unsubscribe from this group, send email to
excel-macros+unsubscribegooglegroups.com or reply to this email with the words
"REMOVE ME" as the subject.
