Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba, how to go to Col B in whatever row is active
Hi,
I'm new to VBA but didn't find the help I need online. I'd like some simple code to enable a client to add a new row to a budget cost center. There may be 20 cost centers, each with 4 rows for entering budget information in the budget form I give them. But some cost centers will need more detailed descriptions, thus another row or two may need to be added by the client. I would like for them to be able to click once in any of the 4 rows (to make it the active cell) in a given cost center (the fifth row is a summary row), then either click a button or a execute key combination to add a new row within that cost center. 1. So the first thing the code needs to do is go to Column B while staying in the current row. I don't know how to do the code for this. In Column B every cell has a Cost Center number. B5:B10 might be 64400; B11:B15 might be 63300. 2. So after the code arrives in Col B, I'd like it to move down until it finds the first cell which is not equal to the cell above it. That is where I'll insert the new row, keeping it within the same cost center rows. If anyone can help with some code to do these two steps, I'd really appreciate it! Thank you, Harold |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba, how to go to Col B in whatever row is active
Wouldn't it be simpler to tell them to click the cell in that 4 cell range that isn't equal to the one above and get them to hold Alt then whilst holding it press I then R?, there is no way of determining which cell has the mismatched value i.e should the code look up 3 cells from where they clicked or down 3 cells?, would that be too far? perhaps in to the next cost centre?, maybe a rethink on what you really want to achieve, that said here's some code that will insert a row wherever you click in column B, it goes in the worksheet code module that you are working on Code: -------------------- Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column < 2 Then Exit Sub If Target.Cells.Count 1 Then Exit Sub Selection.EntireRow.Insert End Sub -------------------- Harold Good;344285 Wrote: Hi, I'm new to VBA but didn't find the help I need online. I'd like some simple code to enable a client to add a new row to a budget cost center. There may be 20 cost centers, each with 4 rows for entering budget information in the budget form I give them. But some cost centers will need more detailed descriptions, thus another row or two may need to be added by the client. I would like for them to be able to click once in any of the 4 rows (to make it the active cell) in a given cost center (the fifth row is a summary row), then either click a button or a execute key combination to add a new row within that cost center. 1. So the first thing the code needs to do is go to Column B while staying in the current row. I don't know how to do the code for this. In Column B every cell has a Cost Center number. B5:B10 might be 64400; B11:B15 might be 63300. 2. So after the code arrives in Col B, I'd like it to move down until it finds the first cell which is not equal to the cell above it. That is where I'll insert the new row, keeping it within the same cost center rows. If anyone can help with some code to do these two steps, I'd really appreciate it! Thank you, Harold -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=96330 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba, how to go to Col B in whatever row is active
I prefer to do it with VBA so I can also have it copy cost center number to
the new row, and all formating as well, including merged cells. Ctrl I,R won't do all that. I assumed it would be quite easy with VBA code to go to column B from wherever the Activecell was, and then loop down checking to find the first unequal cost center number. But maybe it isn't that simple. Harold "Simon Lloyd" wrote in message ... Wouldn't it be simpler to tell them to click the cell in that 4 cell range that isn't equal to the one above and get them to hold Alt then whilst holding it press I then R?, there is no way of determining which cell has the mismatched value i.e should the code look up 3 cells from where they clicked or down 3 cells?, would that be too far? perhaps in to the next cost centre?, maybe a rethink on what you really want to achieve, that said here's some code that will insert a row wherever you click in column B, it goes in the worksheet code module that you are working on Code: -------------------- Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column < 2 Then Exit Sub If Target.Cells.Count 1 Then Exit Sub Selection.EntireRow.Insert End Sub -------------------- Harold Good;344285 Wrote: Hi, I'm new to VBA but didn't find the help I need online. I'd like some simple code to enable a client to add a new row to a budget cost center. There may be 20 cost centers, each with 4 rows for entering budget information in the budget form I give them. But some cost centers will need more detailed descriptions, thus another row or two may need to be added by the client. I would like for them to be able to click once in any of the 4 rows (to make it the active cell) in a given cost center (the fifth row is a summary row), then either click a button or a execute key combination to add a new row within that cost center. 1. So the first thing the code needs to do is go to Column B while staying in the current row. I don't know how to do the code for this. In Column B every cell has a Cost Center number. B5:B10 might be 64400; B11:B15 might be 63300. 2. So after the code arrives in Col B, I'd like it to move down until it finds the first cell which is not equal to the cell above it. That is where I'll insert the new row, keeping it within the same cost center rows. If anyone can help with some code to do these two steps, I'd really appreciate it! Thank you, Harold -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=96330 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba, how to go to Col B in whatever row is active
Sub CostCtrNewRow()
Dim myRow As Long myRow = ActiveCell.Row Do Until Cells(myRow, 2) < Cells(myRow + 1, 2) myRow = myRow + 1 Loop Rows(myRow + 1).Insert End Sub "Harold Good" wrote: Hi, I'm new to VBA but didn't find the help I need online. I'd like some simple code to enable a client to add a new row to a budget cost center. There may be 20 cost centers, each with 4 rows for entering budget information in the budget form I give them. But some cost centers will need more detailed descriptions, thus another row or two may need to be added by the client. I would like for them to be able to click once in any of the 4 rows (to make it the active cell) in a given cost center (the fifth row is a summary row), then either click a button or a execute key combination to add a new row within that cost center. 1. So the first thing the code needs to do is go to Column B while staying in the current row. I don't know how to do the code for this. In Column B every cell has a Cost Center number. B5:B10 might be 64400; B11:B15 might be 63300. 2. So after the code arrives in Col B, I'd like it to move down until it finds the first cell which is not equal to the cell above it. That is where I'll insert the new row, keeping it within the same cost center rows. If anyone can help with some code to do these two steps, I'd really appreciate it! Thank you, Harold |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba, how to go to Col B in whatever row is active
Hey, thanks very much, that got me down the right road!
Harold "slarbie" wrote in message ... Sub CostCtrNewRow() Dim myRow As Long myRow = ActiveCell.Row Do Until Cells(myRow, 2) < Cells(myRow + 1, 2) myRow = myRow + 1 Loop Rows(myRow + 1).Insert End Sub "Harold Good" wrote: Hi, I'm new to VBA but didn't find the help I need online. I'd like some simple code to enable a client to add a new row to a budget cost center. There may be 20 cost centers, each with 4 rows for entering budget information in the budget form I give them. But some cost centers will need more detailed descriptions, thus another row or two may need to be added by the client. I would like for them to be able to click once in any of the 4 rows (to make it the active cell) in a given cost center (the fifth row is a summary row), then either click a button or a execute key combination to add a new row within that cost center. 1. So the first thing the code needs to do is go to Column B while staying in the current row. I don't know how to do the code for this. In Column B every cell has a Cost Center number. B5:B10 might be 64400; B11:B15 might be 63300. 2. So after the code arrives in Col B, I'd like it to move down until it finds the first cell which is not equal to the cell above it. That is where I'll insert the new row, keeping it within the same cost center rows. If anyone can help with some code to do these two steps, I'd really appreciate it! Thank you, Harold |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba, how to go to Col B in whatever row is active
If its always check each cell downwards from the activecell how would you know when to stop checking, as i can't see your workbook i can't give an accurate answer, are you checking for an inequality to distinguish the summary row from your data? Most things are possible in VBA it's just we need more information and a clearer picture in order to give you what you need, for further help with it why not join our forums (shown in the link below) it's completely free, if you do join you will have the opportunity to add attachments to your posts so you can add workbooks to better illustrate your problems and get help directly with them. Also if you do join please post in this thread (link found below) so that people who have been following or helping with this query can continue to do so. :) Harold Good;344362 Wrote: I prefer to do it with VBA so I can also have it copy cost center number to the new row, and all formating as well, including merged cells. Ctrl I,R won't do all that. I assumed it would be quite easy with VBA code to go to column B from wherever the Activecell was, and then loop down checking to find the first unequal cost center number. But maybe it isn't that simple. Harold "Simon Lloyd" wrote in message ... Wouldn't it be simpler to tell them to click the cell in that 4 cell range that isn't equal to the one above and get them to hold Alt then whilst holding it press I then R?, there is no way of determining which cell has the mismatched value i.e should the code look up 3 cells from where they clicked or down 3 cells?, would that be too far? perhaps in to the next cost centre?, maybe a rethink on what you really want to achieve, that said here's some code that will insert a row wherever you click in column B, it goes in the worksheet code module that you are working on Code: -------------------- Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column < 2 Then Exit Sub If Target.Cells.Count 1 Then Exit Sub Selection.EntireRow.Insert End Sub -------------------- Harold Good;344285 Wrote: Hi, I'm new to VBA but didn't find the help I need online. I'd like some simple code to enable a client to add a new row to a budget cost center. There may be 20 cost centers, each with 4 rows for entering budget information in the budget form I give them. But some cost centers will need more detailed descriptions, thus another row or two may need to be added by the client. I would like for them to be able to click once in any of the 4 rows (to make it the active cell) in a given cost center (the fifth row is a summary row), then either click a button or a execute key combination to add a new row within that cost center. 1. So the first thing the code needs to do is go to Column B while staying in the current row. I don't know how to do the code for this. In Column B every cell has a Cost Center number. B5:B10 might be 64400; B11:B15 might be 63300. 2. So after the code arrives in Col B, I'd like it to move down until it finds the first cell which is not equal to the cell above it. That is where I'll insert the new row, keeping it within the same cost center rows. If anyone can help with some code to do these two steps, I'd really appreciate it! Thank you, Harold -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('http://www.thecodecage.com' (http://www.thecodecage.com/)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'vba, how to go to Col B in whatever row is active - The Code Cage Forums' (http:/www.thecodecage.com/forumz/showthread.php?t=96330) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=96330 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Row select mode to highlight active row of active cell | Excel Discussion (Misc queries) | |||
run macro although blinking cursor is active in an active cell | Excel Programming | |||
I need to sort an active sheet using the col of the active cell | Excel Programming | |||
how can I return the active row and active column? | Excel Programming | |||
HOW TO COPY 480 ACTIVE E-MAIL ADDRESSES CLM "G" ON AN ACTIVE EXCE. | Excel Discussion (Misc queries) |