Resizing cells to fit text returned by VLOOKUP

I have some VLOOKUPs which sometimes return 50 or 60 characters (they're in 
merged cells). I want the text to wrap, and the cell to enlarge to fit the 
text - Just like it does whan you type it in, but somehow, because it is 
being returned from a formula it just displays the bottom line of the 
wrapped text, and doesn't resize the row.

Anyone know how I cam make it fit?

I'm happy to use a VBA solution if there's no built in way to make it happen 
automatically.

Thanks

M

0
9/3/2009 9:18:39 AM
excel 39879 articles. 2 followers. Follow

10 Replies
2095 Views

Similar Articles

[PageSpeed] 0

Would you mind to limit your request to ONE newsgroup?
Joerg

"Michelle" <mh_londonNOJUNK@hotmail.com> wrote in message 
news:44EB431F-4EDB-4AD1-98CF-774D61B59636@microsoft.com...
>I have some VLOOKUPs which sometimes return 50 or 60 characters (they're in 
>merged cells). I want the text to wrap, and the cell to enlarge to fit the 
>text - Just like it does whan you type it in, but somehow, because it is 
>being returned from a formula it just displays the bottom line of the 
>wrapped text, and doesn't resize the row.
>
> Anyone know how I cam make it fit?
>
> I'm happy to use a VBA solution if there's no built in way to make it 
> happen automatically.
>
> Thanks
>
> M
> 


0
no3581 (99)
9/3/2009 9:33:52 AM
Yes, I do mind. I get lots of stroppy replies if I post similar requests to 
more than one group, and often a question is relevant to more than one 
group - as in this case - you can see how it is relevant to Functions, and 
Misc, but also there might be a VBA solution. So posting to multiple groups 
in such a way that any replies appear in all groups is a generally prefered 
ettiquette.

Why do you have a problem with it? Others have recommended it as the right 
way.

M  :)

"Joerg Mochikun" <no@email.address> wrote in message 
news:h7o2i2$2kh$1@daniel-new.mch.sbs.de...
> Would you mind to limit your request to ONE newsgroup?
> Joerg
>
> "Michelle" <mh_londonNOJUNK@hotmail.com> wrote in message 
> news:44EB431F-4EDB-4AD1-98CF-774D61B59636@microsoft.com...
>>I have some VLOOKUPs which sometimes return 50 or 60 characters (they're 
>>in merged cells). I want the text to wrap, and the cell to enlarge to fit 
>>the text - Just like it does whan you type it in, but somehow, because it 
>>is being returned from a formula it just displays the bottom line of the 
>>wrapped text, and doesn't resize the row.
>>
>> Anyone know how I cam make it fit?
>>
>> I'm happy to use a VBA solution if there's no built in way to make it 
>> happen automatically.
>>
>> Thanks
>>
>> M
>>
>
> 

0
9/3/2009 10:29:17 AM
Sub Macro()
Cells.SpecialCells(xlCellTypeFormulas).WrapText = True
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Michelle" wrote:

> I have some VLOOKUPs which sometimes return 50 or 60 characters (they're in 
> merged cells). I want the text to wrap, and the cell to enlarge to fit the 
> text - Just like it does whan you type it in, but somehow, because it is 
> being returned from a formula it just displays the bottom line of the 
> wrapped text, and doesn't resize the row.
> 
> Anyone know how I cam make it fit?
> 
> I'm happy to use a VBA solution if there's no built in way to make it happen 
> automatically.
> 
> Thanks
> 
> M
> 
0
9/3/2009 11:09:01 AM
No, Thanks, but WrapText is already on for these cells, it's just that the 
row height is not adjusting automatically.

M


"Jacob Skaria" <JacobSkaria@discussions.microsoft.com> wrote in message 
news:385CB93B-8AC8-43C6-90F6-0389931FCE37@microsoft.com...
> Sub Macro()
> Cells.SpecialCells(xlCellTypeFormulas).WrapText = True
> End Sub
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "Michelle" wrote:
>
>> I have some VLOOKUPs which sometimes return 50 or 60 characters (they're 
>> in
>> merged cells). I want the text to wrap, and the cell to enlarge to fit 
>> the
>> text - Just like it does whan you type it in, but somehow, because it is
>> being returned from a formula it just displays the bottom line of the
>> wrapped text, and doesn't resize the row.
>>
>> Anyone know how I cam make it fit?
>>
>> I'm happy to use a VBA solution if there's no built in way to make it 
>> happen
>> automatically.
>>
>> Thanks
>>
>> M
>> 

0
9/3/2009 11:26:34 AM
Cells.SpecialCells(xlCellTypeFormulas).Rows.AutoFit

If this post helps click Yes
---------------
Jacob Skaria


"Michelle" wrote:

> No, Thanks, but WrapText is already on for these cells, it's just that the 
> row height is not adjusting automatically.
> 
> M
> 
> 
> "Jacob Skaria" <JacobSkaria@discussions.microsoft.com> wrote in message 
> news:385CB93B-8AC8-43C6-90F6-0389931FCE37@microsoft.com...
> > Sub Macro()
> > Cells.SpecialCells(xlCellTypeFormulas).WrapText = True
> > End Sub
> >
> > If this post helps click Yes
> > ---------------
> > Jacob Skaria
> >
> >
> > "Michelle" wrote:
> >
> >> I have some VLOOKUPs which sometimes return 50 or 60 characters (they're 
> >> in
> >> merged cells). I want the text to wrap, and the cell to enlarge to fit 
> >> the
> >> text - Just like it does whan you type it in, but somehow, because it is
> >> being returned from a formula it just displays the bottom line of the
> >> wrapped text, and doesn't resize the row.
> >>
> >> Anyone know how I cam make it fit?
> >>
> >> I'm happy to use a VBA solution if there's no built in way to make it 
> >> happen
> >> automatically.
> >>
> >> Thanks
> >>
> >> M
> >> 
> 
0
9/3/2009 11:41:01 AM
Thanks but unfortunately that doesn't work either, it would if it were text, 
but because it's coming from a VLOOKUP, for some reason it doesn't work.

I wonder if there's something I've overlooked?

M


"Jacob Skaria" <JacobSkaria@discussions.microsoft.com> wrote in message 
news:ACBB82FC-C62E-4EA7-987D-9F3190E906C8@microsoft.com...
> Cells.SpecialCells(xlCellTypeFormulas).Rows.AutoFit
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "Michelle" wrote:
>
>> No, Thanks, but WrapText is already on for these cells, it's just that 
>> the
>> row height is not adjusting automatically.
>>
>> M
>>
>>
>> "Jacob Skaria" <JacobSkaria@discussions.microsoft.com> wrote in message
>> news:385CB93B-8AC8-43C6-90F6-0389931FCE37@microsoft.com...
>> > Sub Macro()
>> > Cells.SpecialCells(xlCellTypeFormulas).WrapText = True
>> > End Sub
>> >
>> > If this post helps click Yes
>> > ---------------
>> > Jacob Skaria
>> >
>> >
>> > "Michelle" wrote:
>> >
>> >> I have some VLOOKUPs which sometimes return 50 or 60 characters 
>> >> (they're
>> >> in
>> >> merged cells). I want the text to wrap, and the cell to enlarge to fit
>> >> the
>> >> text - Just like it does whan you type it in, but somehow, because it 
>> >> is
>> >> being returned from a formula it just displays the bottom line of the
>> >> wrapped text, and doesn't resize the row.
>> >>
>> >> Anyone know how I cam make it fit?
>> >>
>> >> I'm happy to use a VBA solution if there's no built in way to make it
>> >> happen
>> >> automatically.
>> >>
>> >> Thanks
>> >>
>> >> M
>> >>
>> 

0
9/3/2009 12:26:19 PM
First, working with merged cells is a real pain.  

They don't behave nicely in lots of situations (autofitting row height is just
the tip of the iceberg!)

Second, excel doesn't keep track of what cells are changed because of a
calculation.  So you'd have to look through all the cells with formulas which
could be a pain, too.

Third, there's no easy way to find merged cells.

That said...

Jim Rech wrote a macro called AutoFitMergedCellRowHeight:
http://groups.google.com/groups?threadm=e1%241uzL1BHA.1784%40tkmsftngp05

This could be modified to tie into a worksheet_calculate event.

If you want to try...

This goes behind the worksheet that has the merged cells with formulas in it.

Option Explicit
Private Sub Worksheet_Calculate()

    Dim myRng As Range
    Dim myCell As Range
    
    'define your range with the addresses of the
    'merged cells formulas
    'or give it a nice name
    'Set myRng = Me.Range("a1,b3,c7")
    'Set myRng = Me.Range("MyMergedCells")
        
    'or look through all the formulas
    Set myRng = Nothing
    On Error Resume Next
    Set myRng = Me.Cells.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0
    
    If myRng Is Nothing Then
        Exit Sub 'no formulas found
    End If
    
    On Error Resume Next
    Application.EnableEvents = False
    For Each myCell In myRng.Cells
        If myCell.MergeArea.Cells.Count = 1 Then
            'do nothing, not a merged cell
        Else
            Call AutoFitMergedCellRowHeight(ActCell:=myCell)
        End If
    Next myCell
    Application.EnableEvents = True
    On Error GoTo 0

End Sub

You'll want to use what's best for you in this portion:

    'define your range with the addresses of the
    'merged cells formulas
    'or give it a nice name
    'Set myRng = Me.Range("a1,b3,c7")        'Option 1
    'Set myRng = Me.Range("MyMergedCells")   'Option 2
        
    'or look through all the formulas  'Option 3 (next 4 lines)
    Set myRng = Nothing
    On Error Resume Next
    Set myRng = Me.Cells.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0

My preference would be to use the named range (select the cells and then
insert|name|define).  Then I wouldn't be looking through all the formulas with
each calculation.  (If you move cells to a new location (inserting/deleting rows
or columns), you'd have to adjust that list of addresses.)

You can comment the options you don't want--and uncomment the line(s) that you
want to use.

This portion does all the work.  It goes in a General module (Insert|Module
inside the VBE).

Option Explicit
''based on Jim Rech's code
''http://groups.google.com/groups?threadm=e1%241uzL1BHA.1784%40tkmsftngp05
''Simulates row height autofit for a merged cell if the active cell..
''  is merged.
''  has Wrap Text set.
''  includes only 1 row.
''Unlike real autosizing the macro only increases row height
''  (if needed).  It does not reduce row height because another
''   merged cell on the same row may needed a greater height
''   than the active cell.
Sub AutoFitMergedCellRowHeight(ActCell As Range)
    Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
    Dim CurrCell As Range
    Dim ActiveCellWidth As Single, PossNewRowHeight As Single
    If ActCell.MergeCells Then
       With ActCell.MergeArea
            If .Rows.Count = 1 And .WrapText = True Then
                Application.ScreenUpdating = False
                CurrentRowHeight = .RowHeight
                ActiveCellWidth = ActCell.ColumnWidth
                For Each CurrCell In .Cells
                    MergedCellRgWidth _
                         = CurrCell.ColumnWidth + MergedCellRgWidth
                Next CurrCell
                .MergeCells = False
                .Cells(1).ColumnWidth = MergedCellRgWidth
                .EntireRow.AutoFit
                PossNewRowHeight = .RowHeight
                .Cells(1).ColumnWidth = ActiveCellWidth
                .MergeCells = True
                .RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
                                     CurrentRowHeight, PossNewRowHeight)
            End If
        End With
    End If
End Sub

Notice the comment in Jim's code:

''Unlike real autosizing the macro only increases row height
''  (if needed).  It does not reduce row height because another
''   merged cell on the same row may needed a greater height
''   than the active cell.





Michelle wrote:
> 
> I have some VLOOKUPs which sometimes return 50 or 60 characters (they're in
> merged cells). I want the text to wrap, and the cell to enlarge to fit the
> text - Just like it does whan you type it in, but somehow, because it is
> being returned from a formula it just displays the bottom line of the
> wrapped text, and doesn't resize the row.
> 
> Anyone know how I cam make it fit?
> 
> I'm happy to use a VBA solution if there's no built in way to make it happen
> automatically.
> 
> Thanks
> 
> M

-- 

Dave Peterson
0
petersod (12004)
9/3/2009 12:46:03 PM
Thanks Dave. I have a working solution - really appreciate the time you 
spent putting this together.

M


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:4A9FBA8B.E811ACEA@verizonXSPAM.net...
> First, working with merged cells is a real pain.
>
> They don't behave nicely in lots of situations (autofitting row height is 
> just
> the tip of the iceberg!)
>
> Second, excel doesn't keep track of what cells are changed because of a
> calculation.  So you'd have to look through all the cells with formulas 
> which
> could be a pain, too.
>
> Third, there's no easy way to find merged cells.
>
> That said...
>
> Jim Rech wrote a macro called AutoFitMergedCellRowHeight:
> http://groups.google.com/groups?threadm=e1%241uzL1BHA.1784%40tkmsftngp05
>
> This could be modified to tie into a worksheet_calculate event.
>
> If you want to try...
>
> This goes behind the worksheet that has the merged cells with formulas in 
> it.
>
> Option Explicit
> Private Sub Worksheet_Calculate()
>
>    Dim myRng As Range
>    Dim myCell As Range
>
>    'define your range with the addresses of the
>    'merged cells formulas
>    'or give it a nice name
>    'Set myRng = Me.Range("a1,b3,c7")
>    'Set myRng = Me.Range("MyMergedCells")
>
>    'or look through all the formulas
>    Set myRng = Nothing
>    On Error Resume Next
>    Set myRng = Me.Cells.SpecialCells(xlCellTypeFormulas)
>    On Error GoTo 0
>
>    If myRng Is Nothing Then
>        Exit Sub 'no formulas found
>    End If
>
>    On Error Resume Next
>    Application.EnableEvents = False
>    For Each myCell In myRng.Cells
>        If myCell.MergeArea.Cells.Count = 1 Then
>            'do nothing, not a merged cell
>        Else
>            Call AutoFitMergedCellRowHeight(ActCell:=myCell)
>        End If
>    Next myCell
>    Application.EnableEvents = True
>    On Error GoTo 0
>
> End Sub
>
> You'll want to use what's best for you in this portion:
>
>    'define your range with the addresses of the
>    'merged cells formulas
>    'or give it a nice name
>    'Set myRng = Me.Range("a1,b3,c7")        'Option 1
>    'Set myRng = Me.Range("MyMergedCells")   'Option 2
>
>    'or look through all the formulas  'Option 3 (next 4 lines)
>    Set myRng = Nothing
>    On Error Resume Next
>    Set myRng = Me.Cells.SpecialCells(xlCellTypeFormulas)
>    On Error GoTo 0
>
> My preference would be to use the named range (select the cells and then
> insert|name|define).  Then I wouldn't be looking through all the formulas 
> with
> each calculation.  (If you move cells to a new location 
> (inserting/deleting rows
> or columns), you'd have to adjust that list of addresses.)
>
> You can comment the options you don't want--and uncomment the line(s) that 
> you
> want to use.
>
> This portion does all the work.  It goes in a General module 
> (Insert|Module
> inside the VBE).
>
> Option Explicit
> ''based on Jim Rech's code
> ''http://groups.google.com/groups?threadm=e1%241uzL1BHA.1784%40tkmsftngp05
> ''Simulates row height autofit for a merged cell if the active cell..
> ''  is merged.
> ''  has Wrap Text set.
> ''  includes only 1 row.
> ''Unlike real autosizing the macro only increases row height
> ''  (if needed).  It does not reduce row height because another
> ''   merged cell on the same row may needed a greater height
> ''   than the active cell.
> Sub AutoFitMergedCellRowHeight(ActCell As Range)
>    Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
>    Dim CurrCell As Range
>    Dim ActiveCellWidth As Single, PossNewRowHeight As Single
>    If ActCell.MergeCells Then
>       With ActCell.MergeArea
>            If .Rows.Count = 1 And .WrapText = True Then
>                Application.ScreenUpdating = False
>                CurrentRowHeight = .RowHeight
>                ActiveCellWidth = ActCell.ColumnWidth
>                For Each CurrCell In .Cells
>                    MergedCellRgWidth _
>                         = CurrCell.ColumnWidth + MergedCellRgWidth
>                Next CurrCell
>                .MergeCells = False
>                .Cells(1).ColumnWidth = MergedCellRgWidth
>                .EntireRow.AutoFit
>                PossNewRowHeight = .RowHeight
>                .Cells(1).ColumnWidth = ActiveCellWidth
>                .MergeCells = True
>                .RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
>                                     CurrentRowHeight, PossNewRowHeight)
>            End If
>        End With
>    End If
> End Sub
>
> Notice the comment in Jim's code:
>
> ''Unlike real autosizing the macro only increases row height
> ''  (if needed).  It does not reduce row height because another
> ''   merged cell on the same row may needed a greater height
> ''   than the active cell.
>
>
>
>
>
> Michelle wrote:
>>
>> I have some VLOOKUPs which sometimes return 50 or 60 characters (they're 
>> in
>> merged cells). I want the text to wrap, and the cell to enlarge to fit 
>> the
>> text - Just like it does whan you type it in, but somehow, because it is
>> being returned from a formula it just displays the bottom line of the
>> wrapped text, and doesn't resize the row.
>>
>> Anyone know how I cam make it fit?
>>
>> I'm happy to use a VBA solution if there's no built in way to make it 
>> happen
>> automatically.
>>
>> Thanks
>>
>> M
>
> -- 
>
> Dave Peterson 

0
9/3/2009 2:14:13 PM
On Thu, 3 Sep 2009 11:29:17 +0100, "Michelle" <mh_londonNOJUNK@hotmail.com>
wrote:

>Yes, I do mind. I get lots of stroppy replies if I post similar requests to 
>more than one group, and often a question is relevant to more than one 
>group - as in this case - you can see how it is relevant to Functions, and 
>Misc, but also there might be a VBA solution. So posting to multiple groups 
>in such a way that any replies appear in all groups is a generally prefered 
>ettiquette.
>
>Why do you have a problem with it? Others have recommended it as the right 
>way.
>
>M  :)
>


Maybe he's confusing *cross-posting* (which you did and is proper) with
*multi-posting*, where you post separate messages to each of the NG's.
--ron
0
ronrosenfeld (3122)
9/3/2009 4:25:31 PM
"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message 
news:4erv95d1s64mcpo5gnae59stm0pm526juk@4ax.com...
> On Thu, 3 Sep 2009 11:29:17 +0100, "Michelle" 
> <mh_londonNOJUNK@hotmail.com>
> wrote:
>
>>Yes, I do mind. I get lots of stroppy replies if I post similar requests 
>>to
>>more than one group, and often a question is relevant to more than one
>>group - as in this case - you can see how it is relevant to Functions, and
>>Misc, but also there might be a VBA solution. So posting to multiple 
>>groups
>>in such a way that any replies appear in all groups is a generally 
>>prefered
>>ettiquette.
>>
>>Why do you have a problem with it? Others have recommended it as the right
>>way.
>>
>>M  :)
>>
>
>
> Maybe he's confusing *cross-posting* (which you did and is proper) with
> *multi-posting*, where you post separate messages to each of the NG's.
> --ron

Yes, I did. Sorry and shame on me.
Joerg


0
no3581 (99)
9/4/2009 1:10:54 AM
Reply:

Similar Artilces:

How do I switch text from capitals to normal?
If I type an email in all CAPS, is there a way to make it into regular form wthout re-typing whole email? Yes, but the method depends on the version of Outlook and the Editor used. chris verbeski wrote: > If I type an email in all CAPS, is there a way to make it into regular form > wthout re-typing whole email? For m, it works like this - select all the text, then hold down shift and tap the F3 key, that toggles the text between lower case, capitals and then capital first letter for every word. It doesnt' work everywhere but works in Word. "Bob I" ...

How do I separate address information from within an Excel cell?
I have a Christmas Card list that was created using a single cell for the whole address. Street, City, Zip/Postal etc. I need to split those components out into separate cells in order to do a proper mail merge. Is there any way to do this. I am not completely unfamiliar with macros but I if that is the solution I would require a fair bit of hand holding. Thanks S. If you actually used commas to separate the fields (and none of the fields contain commas), you could select the column and do: Data|text to columns delimited comma Remember to keep enough open columns to the right s...

Counting empty cells within a range of cells #2
A sheet can have a variable range of adjacent cells each containing the value =1 The range defines the perimeter of, or surrounds a group of empty cells however outside the perimeter, the remaining cells on the sheet are empty as well. The task is to count the empty cells that are surrounded by the perimeter of 1�s: To do this manually, I use the @countif (range=0) function however it requires that I go into the worksheet and select the range manually. I would like to do this by formula or macro. The knowns are: 1) The location of the start/end cell of the range 2) That the next cell in ...

Ordering by number and text
I use a report to print out checklists that in the detail section have item number to delineate each checklist item. The item numbers as an example are 1-1, 1-2, 1-2a, 1-2b, or 2-1, 2-2, 2-3, 2-3a, 2-3b and so on. The report is grouped by checklist section and the grouping works perfectly but when the report is printed the item numbers are ordered in this order 1-1, 1-10, 1-11, 1-2, 1-3, 1-3a, 1-3b, 1-4, 1-5, 1-6, 1-7, 1-9. Obviously I want the the order to be in proper numerical sequence where 1-10 comes after 1-9. but even replacing the '-' with a decimal point doesn't ...

help
I have some texts files, which i want to read in each line, and then write back each line to a new text file. So for example, I want to read in the 2 lines below: "C:\Data\=D3=EA=D2=B9=D0=C7=BF=D5",6,10,3,3,8 "",0,0,0,318.592,83.04552 I store each line of data in the following vector: std::vector<CString> FileData; So FileData[0] =3D ""C:\Data\=D3=EA=D2=B9=D0=C7=BF=D5",6,10,3,3,8" FileData[1] =3D """,0,0,0,318.592,83.04552" (the data is in the vector correctly) Then I go to write the data to a new text file... FILE ...

Display cell attributes/format
Is there any way of examining the cell attributes/formatting on a worksheet other than individually with Format | Cell? I would assume so but can't find it. Thanks Brian Tozer Not in versions after and including xl97. -- Regards, Tom Ogilvy Brian Tozer <briantoz@ihug.co.nz> wrote in message news:bs3261$duo$1@lust.ihug.co.nz... > Is there any way of examining the cell attributes/formatting on a worksheet > other than individually with Format | Cell? > I would assume so but can't find it. > > Thanks > Brian Tozer > > ...

Dynamic text
Hi, I would like to add a serial number to a publication I'm designing. Is there a way to get a different number (sequential or otherwise) on a publication? Thanks Brian W Mail merge, create a data file. Read the help files, it is the way to all knowledge. -- Mary Sauer MS MVP http://dgl.microsoft.com/ http://mvps.org/msauer/ "Brian W" <brianw@gold_death_2_spam_rush.com> wrote in message news:eFVmhdGUDHA.2008@TK2MSFTNGP11.phx.gbl... > Hi, > > I would like to add a serial number to a publication I'm designing. Is there > a way to get a different number...

Time Format to Text Output
Hi I run Excel 2K I have a series of times that I download from the mainframe. (these are in a date format) EXAMPLE 23/09/2009 6:07:00 AM 22/09/2009 9:22:00 PM 22/09/2009 7:40:00 PM etc etc I am in need of a formula that looks at these times, and based on their values, displays a particular piece of text. (in this case "DAY", "AFT", "NIGHT") Example Any time between the following: 7:20:00 AM to 3:19:00 PM should display the word DAY Any time between the following: 3:20:00 PM to 11:19:00PM should display the word AFT Any time between the following: 1...

"Messaging interface has returned an unknown error"
I'm running Outlook 02 on Win XP Pro SP2. I think this may have been caused by the DST time zone update utility, but I'm not certain. Here's the situation. If I try to open a calendar item I get the following error: "Can't open this item. The messaging interface has returned an unknown error. If the problem persists, restart Outlook." If I try to create a new message or appointment from the quick start toolbar, or clicking a mailto: link on a web page: "The operation failed." If I click the "to" "cc" or "bcc" button while...

Opening Text Files and Re-Saving Them
Hi. I am trying to write some code which will allow a user to select a folder and then will open all of the text files in that folder and re-save them as excel files. I have sorted out the code to enable the user to select the folder they wish to use, but am looking some assistance on the looping to enable each of the text files to be opened and re-saved. Can anyone suggest how I might do this. Thanks. If the text files are all the same, you could record a macro that Opens, saves as an excel file, and closes the file. Then that recorded macro could be modified to open...

Limiting The Amount of Text
Hi, Is there a way I can limit the amount of Text I can enter into th cells of a selected column? For example, when listing on eBay, the description bar on eBay i limited to 40 characters. Since I list items on eBay with Excel, i would save me a lot of time if I were able to lock the cells in m "Description" column to 40 characters instead of going back an counting all the characters in those cells. Is this doable ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum....

Formula Cell Error
Hi all, i have a problem with the following code, would appreciate if anyone can give me some help on this. Basically everything works fine except for this line: Cells(i, 12).FormulaR1C1 = "=SUM(J" & StartCount & ":J" & EndCount & ")" The cell will reflect "=SUM('J2':'J3')" instead of "=SUM(J2:J3)". --------------------------------------------------------------------------------------------- Sub Test() For i = 2 To 100 Step 1 Cells(i, 1).Select If Cells(i, 1).Interior.ColorI...

why does excel add 0's to a cell?
when I add numbers to a cell, excel adds tree zero's to the number. Is this a bug in excel? How can I fix this problem? Check the number format for the cell. Format it as General. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Kathryn" <Kathryn@discussions.microsoft.com> wrote in message news:174D91D7-B8A2-4058-9A80-C47D33D369E6@microsoft.com... > when I add numbers to a cell, excel adds tree zero's to the > number. Is this a > bug in excel? How can I fix this problem? Kathryn, If Chip's su...

Html Tags for Specific Words in a Cell
Hi Everyone, I have problem putting up html tags to every specific word in a cell. Let's say this praise. [QUOTE]JUAN: Honey, bakit MALI ang pinaglalagyan mong DALIRI ng WEDDING RING natin? MRS: Siyempre para TERNO! Mali din naman ang NAPANGASAWA ko eh![/ QUOTE] The above is a conversation and I'd like to put "<b>" and "</b>" at the beginning and end to make the talking character's name in bold format when i uploaded it in the internet. I target this result: [QUOTE]<b>JUAN</b>: Honey, bakit MALI ang pinaglalagyan mong DALIRI ng WEDDI...

Merge Cells I can't find
I was trying to hide a column, and got a message that I couldn't, because cells in another column were involved. I don't see those cells, and "merge" is turned off. Any suggestions for finding them? Ed This quick macro will remove all merges on the current sheet: Sub RemoveMerges() Cells.UnMerge End Sub You can, of course define a range instead. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "Ed Kearns" wrote: > I was trying to hide a column, and got a message that I couldn't...

RMS 1.x Dongle Return
Is there an expected date that the dongle from 1.x must be returned? -- John M. 6 months after v3.0 is released. ;) "John M." wrote: > Is there an expected date that the dongle from 1.x must be returned? > > -- > > John M. > > > This is a multi-part message in MIME format. ------=_NextPart_000_099F_01C75566.EF856370 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable John M, They've told us a _reasonable_ timeframe, whatever that means. I'm = guessing 6 months before the phone c...

Can I convert footnote numbers to text
I'm working with a document where the author has used the automated endnote facility in Word. Since this is about to be translated into Quark for typesetting, I need the endnote marks and the numbers with the endnotes to be real numbers, not endnote marks -- Quark will not read them as numbers. I was sure I did the conversion using ActiveDocument.ConvertNumbersToText in Visual Basic once before, but I can't seem to make it work. Help! There are 20 chapters with about 40 endnotes each. ActiveDocument.ConvertNumbersToText only works with paragraph numbering (and LISTNUM fi...

Cell Formula reference to cell Based On third Cell Content
I want to build a formula in one cell that calls a second based on the numeric value in a third. Specifically, Say the frst cell is F10. I want it to look up the number in F1, and then if F1=1, F10=E9*something if F1=2, F10=D9*something if F1=3, F10=C9* something etc. ...

reference value in a cell
I have these values in sheets. Sheet2!A1 = 5 Sheet3!A1 = 7 Now, I put Sheet1!A1 = "Sheet2" which can be varied to "Sheet3" or "Sheet4" etc. User has to provide the name of the sheet here in Sheet1!A1. The problem is what formula to put in Sheet1!A3 so that it checks the value in Sheet1!A1 and depending on sheet name, takes the value of A1 of that sheet. Ofcourse it is wrong, but, something like this [Sheet1!A1]![A1] i.e. if I write Sheet1!A1 = "Sheet3", the value in Sheet1!A3 becomes =7. Any help is appreciated. Try this: =INDIRECT(A1&"...

POS Extended Amount Flips to Positive on a return when changed?
Microsoft Retail Manager Version 1.3.1002 Hi, I recently had a problem balancing when i noticed that a return i processed was incorrectly journaled. Generating a detailed sales report also illustrated the same error. The quantity sold was -1, the sold price was -ve but the total sale was +ve, whereas when correct the quantity sold is -1 the sold price is +ve and the total sale is -ve. How This Happened: In POS I processed the return in what I thought was the usual manner. I pressed the return button and then because the item being returned had a reduced value I changed the extended price th...

Wrap text in cell
It seems that wrap text and autofit row width and row height have a limit. Any idea where to change this limit? I have quite a large text in a cell and when I do wrap text and autofit, then parts of the text get cut of. The text is still there, but it is not shown in the cell and it is cut off when printing. You can enter 32767 characters in a cell. In Excel 2003 and earlier, only 1024 of these will be visible or printed. I believe Excel 2007 will show 8192 characters. If you add an Alt + Enter linefeed every 100 or so characters you can increase this 1024 limit. ...

Actively select cells/ranges/sheets and Paste Link
Hello: What I am trying to do is select the cells or range on a sheet that I am interested in, then paste link on a different sheet. All the selections should be variable (I mean not be hardcoded). Thanks, pl_hlp Something like this: Sub YetAnotherTry() Dim r1 As Range, r2 As Range Set r1 = Application.InputBox(prompt:="select copy area", Type:=8) ady1 = r1.Address Sheets("Sheet2").Select Set r2 = Application.InputBox(prompt:="select destination cell", Type:=8) ady2 = r2.Address Sheets("sheet1").Select Range(ady1).Select Se...

zero value in cell
I have a table of engineering calculations. The same formula in each cell operation on variables in the row and column headers. i use the MIN function to evaluate two formulas. Then I take the minimum result and operate on it outside of hte MIN function. I'm getting zero values displaying in my table. When I pick a particular cell, and then pick the formula icon in the editor, the dialog box displays the correct reults for each of the two items and the formula overall. Why don't these results show in my table? -- Arthur I'm guessing your numbers are actually Text....

Excel 2003 / Send To / Mail Recipient / body text is removed
When a user is working in Excel 2003 and hits File.. Send To.. Mail Recipient.. it takes the spreadsheet and places it in an Outlook 2003 email as an Attachment. If they type any text into the body of the email it is replaced with <<...>> How can I change this ? If you don't get a good reply here, you may want to post in one of the .Outlook newsgroups. jmaynard2 wrote: > > When a user is working in Excel 2003 and hits File.. Send To.. Mail > Recipient.. it takes the spreadsheet and places it in an Outlook 2003 email > as an Attachment. > > If they typ...

Advanced Vlookup
Hi I have a spreadsheet which has customer names and data about them such as country name , state name and city name. It also has details about type of purchases made. In another spreadsheet i want to call certain other data about the customer using the filter criteria. But i dont want to use auto filters. In cell a1 of this second spreadsheet i type the country/state/town name. In cell b1 of this second spreadsheet i type the type of purchase. Now these purchase types also have sub classifications. E.g Electronic gadgets can be a classififcation and dvd player , cell phones etc can be su...