Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I query the 'indent' level of text in a cell?
This is part of the format - cell - alignment setup.
There are many things that can be queried using an Excel function. But how can I query the indent level? I have tried/search for hours...please help! Thank you. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I query the 'indent' level of text in a cell?
Activecell.IndentLevel
-- Jim "NJDevil" wrote in message ... This is part of the format - cell - alignment setup. There are many things that can be queried using an Excel function. But how can I query the indent level? I have tried/search for hours...please help! Thank you. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I query the 'indent' level of text in a cell?
How about
Len(yourcell) - Trim(yourcell) The result would be the number of leading spaces provided you have no other irregular spacing in the cell. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I query the 'indent' level of text in a cell?
Hi JE, I haev not gotten it to work. It return a zero no matter if it's
indented or not. I will try to figure it out later. For now one of the other posts (macro in VBA) works. Thaks very much for helping me in any case. I very much appreciate it! NJDevil "JE McGimpsey" wrote: Make Jim's solution into a UDF. As you're not familiar with UDFs, read David McRitchie's "Getting Started with Macros and User Defined Functions": http://www.mvps.org/dmcritchie/excel/getstarted.htm Public Function IndentLevel(Ref As Range) As Long Application.Volatile IndentLevel = Ref.IndentLevel End Function Call from the worksheet as B1: =IndentLevel(A1) to find the indent level of cell A1. Note that even with the Application.Volatile command, which makes the function recalculate whenever the worksheet is recalculated, changing the indent level doesn't fire a worksheet calculation, so you'll need to use F9 to be sure it's accurate after changing an indent level. In article , "NJDevil" wrote: Thanks for the reply. However, I don't know how to use that. It sounds like I need to do some programming to get the info I need. Can you PLEASE provide me with some code (or how ever you would use it) so that I may integrate that solution into my spreadsheet? I have spend a lot of time on this spreadheet and this is the one thing that I really need to make it do what I need! Once I have the code, how do I invoke it in Excel? Sorry for being sucha newby but I'm not an Excel programmer. I really wish I could just invoke a function, but the '=CELL' function does not let me query the indent length. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I query the 'indent' level of text in a cell?
Dave,
Halleluiah! I like the first one and I will use it. Thank you so much! One more thing, can you tell me how I can invoke the macro without all the mouse clicks to take me to the 'run' button in the macro setup? Is there a way to associate a key combination (e.g. ctl+I) to execute the desired macro? Thanks fror the big help! NJDevil "Dave Peterson" wrote: Since you're working with the activecell, you'll be working with the activesheet--so you don't need that variable for the worksheet. Code in a general module that has ranges that are unqualified will refer to the activesheet. And since you're activating cells, you don't need to keep track of rows and columns This may do what you want: Option Explicit Sub query_indent() Cells(2, 1).Select Do While Trim(ActiveCell.Value) < "" ActiveCell.Offset(0, 2).Value = ActiveCell.IndentLevel ActiveCell.Offset(1, 0).Select Loop End Sub Another way to do it with selecting or activating: Option Explicit Sub query_indent2() Dim wrkSheet As Worksheet Dim myRng As Range Dim myCell As Range Set wrkSheet = Worksheets("sheet1") With wrkSheet Set myRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) For Each myCell In myRng.Cells With myCell .Offset(0, 2).Value = .IndentLevel End With Next myCell End With End Sub But this actually checks the cells in A2 through the last used cell in column A. This portion: .Cells(.Rows.Count, "A").End(xlUp) is like selecting A65536 and then hitting the End key followed by the UpArrow. NJDevil wrote: Thanks JE. I've been reading how to define and setup a amcro from some info on the web... http://msdn.microsoft.com/library/de.../odc_super.asp I think I'm on the right track. Can you have a look at this and make some adjustments to get it to work? Basically Im just trying to put the indent level in a separate column. THANKS FOR YOUR HELP! Sub query_indent() Dim indent_length Dim Row Dim Col 'Not sure if I need all three Dim statements above. 'Select the active worksheet Set wrkSheet = ActiveWorkbook.ActiveSheet 'indent_length = ActiveCell.IndentLevel 'Start on the second row since the top row is the header 'Range("C2").Value = indent_length 'Start at A2 (i.e. Row 2, Col A) Row = 2 Col = 1 Contents = ActiveCell.Address(Row, Col).Cell.Value 'It does not like the above statement... can I use Row and Col variables? 'If not, how can I increment through it? Do While Contents < " " indent_length = ActiveCell.IndentLevel 'Now place the indent length in Col C of the same row ActiveCell.Address(Row, Col + 2).Cell.Select ActiveCell.Value = indent_length 'Increment to the next row Row = Row + 1 'Grab the data in the next cell (next row, same col) ActiveCell.Address(Row, Col).Cell.Select Loop End Sub I guess you can think of the above as psuedo code, but if you can help me get it right I would certainly appreciate it! Thank you again. It's 1;10am here (NJ) and I'm going to call it a night. Cheers! "JE McGimpsey" wrote: Make Jim's solution into a UDF. As you're not familiar with UDFs, read David McRitchie's "Getting Started with Macros and User Defined Functions": http://www.mvps.org/dmcritchie/excel/getstarted.htm Public Function IndentLevel(Ref As Range) As Long Application.Volatile IndentLevel = Ref.IndentLevel End Function Call from the worksheet as B1: =IndentLevel(A1) to find the indent level of cell A1. Note that even with the Application.Volatile command, which makes the function recalculate whenever the worksheet is recalculated, changing the indent level doesn't fire a worksheet calculation, so you'll need to use F9 to be sure it's accurate after changing an indent level. In article , "NJDevil" wrote: Thanks for the reply. However, I don't know how to use that. It sounds like I need to do some programming to get the info I need. Can you PLEASE provide me with some code (or how ever you would use it) so that I may integrate that solution into my spreadsheet? I have spend a lot of time on this spreadheet and this is the one thing that I really need to make it do what I need! Once I have the code, how do I invoke it in Excel? Sorry for being sucha newby but I'm not an Excel programmer. I really wish I could just invoke a function, but the '=CELL' function does not let me query the indent length. -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I query the 'indent' level of text in a cell?
I liked the second one <bg.
Start up excel. Load the file with the macro. Tools|macro|macros... Select your macro from the list. Click options Assign the shortcut key you want to use. Click ok to exit that dialog. Click Cancel to get out of the next dialog. Don't forget to save your workbook with your change. NJDevil wrote: Dave, Halleluiah! I like the first one and I will use it. Thank you so much! One more thing, can you tell me how I can invoke the macro without all the mouse clicks to take me to the 'run' button in the macro setup? Is there a way to associate a key combination (e.g. ctl+I) to execute the desired macro? Thanks fror the big help! NJDevil "Dave Peterson" wrote: Since you're working with the activecell, you'll be working with the activesheet--so you don't need that variable for the worksheet. Code in a general module that has ranges that are unqualified will refer to the activesheet. And since you're activating cells, you don't need to keep track of rows and columns This may do what you want: Option Explicit Sub query_indent() Cells(2, 1).Select Do While Trim(ActiveCell.Value) < "" ActiveCell.Offset(0, 2).Value = ActiveCell.IndentLevel ActiveCell.Offset(1, 0).Select Loop End Sub Another way to do it with selecting or activating: Option Explicit Sub query_indent2() Dim wrkSheet As Worksheet Dim myRng As Range Dim myCell As Range Set wrkSheet = Worksheets("sheet1") With wrkSheet Set myRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) For Each myCell In myRng.Cells With myCell .Offset(0, 2).Value = .IndentLevel End With Next myCell End With End Sub But this actually checks the cells in A2 through the last used cell in column A. This portion: .Cells(.Rows.Count, "A").End(xlUp) is like selecting A65536 and then hitting the End key followed by the UpArrow. NJDevil wrote: Thanks JE. I've been reading how to define and setup a amcro from some info on the web... http://msdn.microsoft.com/library/de.../odc_super.asp I think I'm on the right track. Can you have a look at this and make some adjustments to get it to work? Basically Im just trying to put the indent level in a separate column. THANKS FOR YOUR HELP! Sub query_indent() Dim indent_length Dim Row Dim Col 'Not sure if I need all three Dim statements above. 'Select the active worksheet Set wrkSheet = ActiveWorkbook.ActiveSheet 'indent_length = ActiveCell.IndentLevel 'Start on the second row since the top row is the header 'Range("C2").Value = indent_length 'Start at A2 (i.e. Row 2, Col A) Row = 2 Col = 1 Contents = ActiveCell.Address(Row, Col).Cell.Value 'It does not like the above statement... can I use Row and Col variables? 'If not, how can I increment through it? Do While Contents < " " indent_length = ActiveCell.IndentLevel 'Now place the indent length in Col C of the same row ActiveCell.Address(Row, Col + 2).Cell.Select ActiveCell.Value = indent_length 'Increment to the next row Row = Row + 1 'Grab the data in the next cell (next row, same col) ActiveCell.Address(Row, Col).Cell.Select Loop End Sub I guess you can think of the above as psuedo code, but if you can help me get it right I would certainly appreciate it! Thank you again. It's 1;10am here (NJ) and I'm going to call it a night. Cheers! "JE McGimpsey" wrote: Make Jim's solution into a UDF. As you're not familiar with UDFs, read David McRitchie's "Getting Started with Macros and User Defined Functions": http://www.mvps.org/dmcritchie/excel/getstarted.htm Public Function IndentLevel(Ref As Range) As Long Application.Volatile IndentLevel = Ref.IndentLevel End Function Call from the worksheet as B1: =IndentLevel(A1) to find the indent level of cell A1. Note that even with the Application.Volatile command, which makes the function recalculate whenever the worksheet is recalculated, changing the indent level doesn't fire a worksheet calculation, so you'll need to use F9 to be sure it's accurate after changing an indent level. In article , "NJDevil" wrote: Thanks for the reply. However, I don't know how to use that. It sounds like I need to do some programming to get the info I need. Can you PLEASE provide me with some code (or how ever you would use it) so that I may integrate that solution into my spreadsheet? I have spend a lot of time on this spreadheet and this is the one thing that I really need to make it do what I need! Once I have the code, how do I invoke it in Excel? Sorry for being sucha newby but I'm not an Excel programmer. I really wish I could just invoke a function, but the '=CELL' function does not let me query the indent length. -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I query the 'indent' level of text in a cell?
Hello again Dave,
I got it to work, but now I'd like to enhance it. What I'd like to do is to put my cursor back in the cell I was in before I invoked the macro. This should be very easy to do, but I don't know how. Psuedo code would go something like this: mycell = location of the active cell. {peform the rest of the macro here} activecell = mycell I would greatly appreciate if you would please provide some code to do it. Also, is there a way to popup a message that says 'Indent Recalculated"? THANK YOU! NJ Devil "Dave Peterson" wrote: I liked the second one <bg. Start up excel. Load the file with the macro. Tools|macro|macros... Select your macro from the list. Click options Assign the shortcut key you want to use. Click ok to exit that dialog. Click Cancel to get out of the next dialog. Don't forget to save your workbook with your change. NJDevil wrote: Dave, Halleluiah! I like the first one and I will use it. Thank you so much! One more thing, can you tell me how I can invoke the macro without all the mouse clicks to take me to the 'run' button in the macro setup? Is there a way to associate a key combination (e.g. ctl+I) to execute the desired macro? Thanks fror the big help! NJDevil "Dave Peterson" wrote: Since you're working with the activecell, you'll be working with the activesheet--so you don't need that variable for the worksheet. Code in a general module that has ranges that are unqualified will refer to the activesheet. And since you're activating cells, you don't need to keep track of rows and columns This may do what you want: Option Explicit Sub query_indent() Cells(2, 1).Select Do While Trim(ActiveCell.Value) < "" ActiveCell.Offset(0, 2).Value = ActiveCell.IndentLevel ActiveCell.Offset(1, 0).Select Loop End Sub Another way to do it with selecting or activating: Option Explicit Sub query_indent2() Dim wrkSheet As Worksheet Dim myRng As Range Dim myCell As Range Set wrkSheet = Worksheets("sheet1") With wrkSheet Set myRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) For Each myCell In myRng.Cells With myCell .Offset(0, 2).Value = .IndentLevel End With Next myCell End With End Sub But this actually checks the cells in A2 through the last used cell in column A. This portion: .Cells(.Rows.Count, "A").End(xlUp) is like selecting A65536 and then hitting the End key followed by the UpArrow. NJDevil wrote: Thanks JE. I've been reading how to define and setup a amcro from some info on the web... http://msdn.microsoft.com/library/de.../odc_super.asp I think I'm on the right track. Can you have a look at this and make some adjustments to get it to work? Basically Im just trying to put the indent level in a separate column. THANKS FOR YOUR HELP! Sub query_indent() Dim indent_length Dim Row Dim Col 'Not sure if I need all three Dim statements above. 'Select the active worksheet Set wrkSheet = ActiveWorkbook.ActiveSheet 'indent_length = ActiveCell.IndentLevel 'Start on the second row since the top row is the header 'Range("C2").Value = indent_length 'Start at A2 (i.e. Row 2, Col A) Row = 2 Col = 1 Contents = ActiveCell.Address(Row, Col).Cell.Value 'It does not like the above statement... can I use Row and Col variables? 'If not, how can I increment through it? Do While Contents < " " indent_length = ActiveCell.IndentLevel 'Now place the indent length in Col C of the same row ActiveCell.Address(Row, Col + 2).Cell.Select ActiveCell.Value = indent_length 'Increment to the next row Row = Row + 1 'Grab the data in the next cell (next row, same col) ActiveCell.Address(Row, Col).Cell.Select Loop End Sub I guess you can think of the above as psuedo code, but if you can help me get it right I would certainly appreciate it! Thank you again. It's 1;10am here (NJ) and I'm going to call it a night. Cheers! "JE McGimpsey" wrote: Make Jim's solution into a UDF. As you're not familiar with UDFs, read David McRitchie's "Getting Started with Macros and User Defined Functions": http://www.mvps.org/dmcritchie/excel/getstarted.htm Public Function IndentLevel(Ref As Range) As Long Application.Volatile IndentLevel = Ref.IndentLevel End Function Call from the worksheet as B1: =IndentLevel(A1) to find the indent level of cell A1. Note that even with the Application.Volatile command, which makes the function recalculate whenever the worksheet is recalculated, changing the indent level doesn't fire a worksheet calculation, so you'll need to use F9 to be sure it's accurate after changing an indent level. In article , "NJDevil" wrote: Thanks for the reply. However, I don't know how to use that. It sounds like I need to do some programming to get the info I need. Can you PLEASE provide me with some code (or how ever you would use it) so that I may integrate that solution into my spreadsheet? I have spend a lot of time on this spreadheet and this is the one thing that I really need to make it do what I need! Once I have the code, how do I invoke it in Excel? Sorry for being sucha newby but I'm not an Excel programmer. I really wish I could just invoke a function, but the '=CELL' function does not let me query the indent length. -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I query the 'indent' level of text in a cell?
That's what the second suggestion did...
But you could use: Option Explicit Sub query_indent() Dim CurCell As Range Dim CurSelection As Range Set CurCell = ActiveCell Set CurSelection = Selection Cells(2, 1).Select Do While Trim(ActiveCell.Value) < "" ActiveCell.Offset(0, 2).Value = ActiveCell.IndentLevel ActiveCell.Offset(1, 0).Select Loop CurSelection.Select CurCell.Activate End Sub (I still like that second suggestion!) NJDevil wrote: Hello again Dave, I got it to work, but now I'd like to enhance it. What I'd like to do is to put my cursor back in the cell I was in before I invoked the macro. This should be very easy to do, but I don't know how. Psuedo code would go something like this: mycell = location of the active cell. {peform the rest of the macro here} activecell = mycell I would greatly appreciate if you would please provide some code to do it. Also, is there a way to popup a message that says 'Indent Recalculated"? THANK YOU! NJ Devil "Dave Peterson" wrote: I liked the second one <bg. Start up excel. Load the file with the macro. Tools|macro|macros... Select your macro from the list. Click options Assign the shortcut key you want to use. Click ok to exit that dialog. Click Cancel to get out of the next dialog. Don't forget to save your workbook with your change. NJDevil wrote: Dave, Halleluiah! I like the first one and I will use it. Thank you so much! One more thing, can you tell me how I can invoke the macro without all the mouse clicks to take me to the 'run' button in the macro setup? Is there a way to associate a key combination (e.g. ctl+I) to execute the desired macro? Thanks fror the big help! NJDevil "Dave Peterson" wrote: Since you're working with the activecell, you'll be working with the activesheet--so you don't need that variable for the worksheet. Code in a general module that has ranges that are unqualified will refer to the activesheet. And since you're activating cells, you don't need to keep track of rows and columns This may do what you want: Option Explicit Sub query_indent() Cells(2, 1).Select Do While Trim(ActiveCell.Value) < "" ActiveCell.Offset(0, 2).Value = ActiveCell.IndentLevel ActiveCell.Offset(1, 0).Select Loop End Sub Another way to do it with selecting or activating: Option Explicit Sub query_indent2() Dim wrkSheet As Worksheet Dim myRng As Range Dim myCell As Range Set wrkSheet = Worksheets("sheet1") With wrkSheet Set myRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) For Each myCell In myRng.Cells With myCell .Offset(0, 2).Value = .IndentLevel End With Next myCell End With End Sub But this actually checks the cells in A2 through the last used cell in column A. This portion: .Cells(.Rows.Count, "A").End(xlUp) is like selecting A65536 and then hitting the End key followed by the UpArrow. NJDevil wrote: Thanks JE. I've been reading how to define and setup a amcro from some info on the web... http://msdn.microsoft.com/library/de.../odc_super.asp I think I'm on the right track. Can you have a look at this and make some adjustments to get it to work? Basically Im just trying to put the indent level in a separate column. THANKS FOR YOUR HELP! Sub query_indent() Dim indent_length Dim Row Dim Col 'Not sure if I need all three Dim statements above. 'Select the active worksheet Set wrkSheet = ActiveWorkbook.ActiveSheet 'indent_length = ActiveCell.IndentLevel 'Start on the second row since the top row is the header 'Range("C2").Value = indent_length 'Start at A2 (i.e. Row 2, Col A) Row = 2 Col = 1 Contents = ActiveCell.Address(Row, Col).Cell.Value 'It does not like the above statement... can I use Row and Col variables? 'If not, how can I increment through it? Do While Contents < " " indent_length = ActiveCell.IndentLevel 'Now place the indent length in Col C of the same row ActiveCell.Address(Row, Col + 2).Cell.Select ActiveCell.Value = indent_length 'Increment to the next row Row = Row + 1 'Grab the data in the next cell (next row, same col) ActiveCell.Address(Row, Col).Cell.Select Loop End Sub I guess you can think of the above as psuedo code, but if you can help me get it right I would certainly appreciate it! Thank you again. It's 1;10am here (NJ) and I'm going to call it a night. Cheers! "JE McGimpsey" wrote: Make Jim's solution into a UDF. As you're not familiar with UDFs, read David McRitchie's "Getting Started with Macros and User Defined Functions": http://www.mvps.org/dmcritchie/excel/getstarted.htm Public Function IndentLevel(Ref As Range) As Long Application.Volatile IndentLevel = Ref.IndentLevel End Function Call from the worksheet as B1: =IndentLevel(A1) to find the indent level of cell A1. Note that even with the Application.Volatile command, which makes the function recalculate whenever the worksheet is recalculated, changing the indent level doesn't fire a worksheet calculation, so you'll need to use F9 to be sure it's accurate after changing an indent level. In article , "NJDevil" wrote: Thanks for the reply. However, I don't know how to use that. It sounds like I need to do some programming to get the info I need. Can you PLEASE provide me with some code (or how ever you would use it) so that I may integrate that solution into my spreadsheet? I have spend a lot of time on this spreadheet and this is the one thing that I really need to make it do what I need! Once I have the code, how do I invoke it in Excel? Sorry for being sucha newby but I'm not an Excel programmer. I really wish I could just invoke a function, but the '=CELL' function does not let me query the indent length. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I query the 'indent' level of text in a cell?
Perfect! Thanks so much for the help and the quick reply.
This is fantastic! NJDevil "Dave Peterson" wrote: That's what the second suggestion did... But you could use: Option Explicit Sub query_indent() Dim CurCell As Range Dim CurSelection As Range Set CurCell = ActiveCell Set CurSelection = Selection Cells(2, 1).Select Do While Trim(ActiveCell.Value) < "" ActiveCell.Offset(0, 2).Value = ActiveCell.IndentLevel ActiveCell.Offset(1, 0).Select Loop CurSelection.Select CurCell.Activate End Sub (I still like that second suggestion!) NJDevil wrote: Hello again Dave, I got it to work, but now I'd like to enhance it. What I'd like to do is to put my cursor back in the cell I was in before I invoked the macro. This should be very easy to do, but I don't know how. Psuedo code would go something like this: mycell = location of the active cell. {peform the rest of the macro here} activecell = mycell I would greatly appreciate if you would please provide some code to do it. Also, is there a way to popup a message that says 'Indent Recalculated"? THANK YOU! NJ Devil "Dave Peterson" wrote: I liked the second one <bg. Start up excel. Load the file with the macro. Tools|macro|macros... Select your macro from the list. Click options Assign the shortcut key you want to use. Click ok to exit that dialog. Click Cancel to get out of the next dialog. Don't forget to save your workbook with your change. NJDevil wrote: Dave, Halleluiah! I like the first one and I will use it. Thank you so much! One more thing, can you tell me how I can invoke the macro without all the mouse clicks to take me to the 'run' button in the macro setup? Is there a way to associate a key combination (e.g. ctl+I) to execute the desired macro? Thanks fror the big help! NJDevil "Dave Peterson" wrote: Since you're working with the activecell, you'll be working with the activesheet--so you don't need that variable for the worksheet. Code in a general module that has ranges that are unqualified will refer to the activesheet. And since you're activating cells, you don't need to keep track of rows and columns This may do what you want: Option Explicit Sub query_indent() Cells(2, 1).Select Do While Trim(ActiveCell.Value) < "" ActiveCell.Offset(0, 2).Value = ActiveCell.IndentLevel ActiveCell.Offset(1, 0).Select Loop End Sub Another way to do it with selecting or activating: Option Explicit Sub query_indent2() Dim wrkSheet As Worksheet Dim myRng As Range Dim myCell As Range Set wrkSheet = Worksheets("sheet1") With wrkSheet Set myRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) For Each myCell In myRng.Cells With myCell .Offset(0, 2).Value = .IndentLevel End With Next myCell End With End Sub But this actually checks the cells in A2 through the last used cell in column A. This portion: .Cells(.Rows.Count, "A").End(xlUp) is like selecting A65536 and then hitting the End key followed by the UpArrow. NJDevil wrote: Thanks JE. I've been reading how to define and setup a amcro from some info on the web... http://msdn.microsoft.com/library/de.../odc_super.asp I think I'm on the right track. Can you have a look at this and make some adjustments to get it to work? Basically Im just trying to put the indent level in a separate column. THANKS FOR YOUR HELP! Sub query_indent() Dim indent_length Dim Row Dim Col 'Not sure if I need all three Dim statements above. 'Select the active worksheet Set wrkSheet = ActiveWorkbook.ActiveSheet 'indent_length = ActiveCell.IndentLevel 'Start on the second row since the top row is the header 'Range("C2").Value = indent_length 'Start at A2 (i.e. Row 2, Col A) Row = 2 Col = 1 Contents = ActiveCell.Address(Row, Col).Cell.Value 'It does not like the above statement... can I use Row and Col variables? 'If not, how can I increment through it? Do While Contents < " " indent_length = ActiveCell.IndentLevel 'Now place the indent length in Col C of the same row ActiveCell.Address(Row, Col + 2).Cell.Select ActiveCell.Value = indent_length 'Increment to the next row Row = Row + 1 'Grab the data in the next cell (next row, same col) ActiveCell.Address(Row, Col).Cell.Select Loop End Sub I guess you can think of the above as psuedo code, but if you can help me get it right I would certainly appreciate it! Thank you again. It's 1;10am here (NJ) and I'm going to call it a night. Cheers! "JE McGimpsey" wrote: Make Jim's solution into a UDF. As you're not familiar with UDFs, read David McRitchie's "Getting Started with Macros and User Defined Functions": http://www.mvps.org/dmcritchie/excel/getstarted.htm Public Function IndentLevel(Ref As Range) As Long Application.Volatile IndentLevel = Ref.IndentLevel End Function Call from the worksheet as B1: =IndentLevel(A1) to find the indent level of cell A1. Note that even with the Application.Volatile command, which makes the function recalculate whenever the worksheet is recalculated, changing the indent level doesn't fire a worksheet calculation, so you'll need to use F9 to be sure it's accurate after changing an indent level. In article , "NJDevil" wrote: Thanks for the reply. However, I don't know how to use that. It sounds like I need to do some programming to get the info I need. Can you PLEASE provide me with some code (or how ever you would use it) so that I may integrate that solution into my spreadsheet? I have spend a lot of time on this spreadheet and this is the one thing that I really need to make it do what I need! Once I have the code, how do I invoke it in Excel? Sorry for being sucha newby but I'm not an Excel programmer. I really wish I could just invoke a function, but the '=CELL' function does not let me query the indent length. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I query the 'indent' level of text in a cell?
JE,
After getting the macro to work and refined, I tried your way (after learning more about UDFs). It works great. Yes, F9 must be used, but it's very stratight forward and the cell is sort of self documenting. Thank you! NJDevil "JE McGimpsey" wrote: Make Jim's solution into a UDF. As you're not familiar with UDFs, read David McRitchie's "Getting Started with Macros and User Defined Functions": http://www.mvps.org/dmcritchie/excel/getstarted.htm Public Function IndentLevel(Ref As Range) As Long Application.Volatile IndentLevel = Ref.IndentLevel End Function Call from the worksheet as B1: =IndentLevel(A1) to find the indent level of cell A1. Note that even with the Application.Volatile command, which makes the function recalculate whenever the worksheet is recalculated, changing the indent level doesn't fire a worksheet calculation, so you'll need to use F9 to be sure it's accurate after changing an indent level. In article , "NJDevil" wrote: Thanks for the reply. However, I don't know how to use that. It sounds like I need to do some programming to get the info I need. Can you PLEASE provide me with some code (or how ever you would use it) so that I may integrate that solution into my spreadsheet? I have spend a lot of time on this spreadheet and this is the one thing that I really need to make it do what I need! Once I have the code, how do I invoke it in Excel? Sorry for being sucha newby but I'm not an Excel programmer. I really wish I could just invoke a function, but the '=CELL' function does not let me query the indent length. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I query the 'indent' level of text in a cell?
JE,
Can you tell me where the UDF is stored? I can't find it in the macro list where it was created, I don't have the function button available nor can I seem to find that either (i.e. f* button). Thanks. NJDevil "NJDevil" wrote: JE, After getting the macro to work and refined, I tried your way (after learning more about UDFs). It works great. Yes, F9 must be used, but it's very stratight forward and the cell is sort of self documenting. Thank you! NJDevil "JE McGimpsey" wrote: Make Jim's solution into a UDF. As you're not familiar with UDFs, read David McRitchie's "Getting Started with Macros and User Defined Functions": http://www.mvps.org/dmcritchie/excel/getstarted.htm Public Function IndentLevel(Ref As Range) As Long Application.Volatile IndentLevel = Ref.IndentLevel End Function Call from the worksheet as B1: =IndentLevel(A1) to find the indent level of cell A1. Note that even with the Application.Volatile command, which makes the function recalculate whenever the worksheet is recalculated, changing the indent level doesn't fire a worksheet calculation, so you'll need to use F9 to be sure it's accurate after changing an indent level. In article , "NJDevil" wrote: Thanks for the reply. However, I don't know how to use that. It sounds like I need to do some programming to get the info I need. Can you PLEASE provide me with some code (or how ever you would use it) so that I may integrate that solution into my spreadsheet? I have spend a lot of time on this spreadheet and this is the one thing that I really need to make it do what I need! Once I have the code, how do I invoke it in Excel? Sorry for being sucha newby but I'm not an Excel programmer. I really wish I could just invoke a function, but the '=CELL' function does not let me query the indent length. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use conditional formatting to set text height and indent. | New Users to Excel | |||
Indent text in cell | Excel Discussion (Misc queries) | |||
INDENT TEXT IN A CELL | Excel Discussion (Misc queries) | |||
Filter text in a column by its Indent, to remove certain text | Excel Discussion (Misc queries) | |||
Two level web query? | Excel Programming |