Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating sequential rows
Hello,
I am looking for a macro or other solution which can loop through my worksheet. The worksheet looks as follows: Branch 1 240230 x $22 240239 y $25 240240 z $26 Totals Branch 2 240230 x $30 240231 a $58 240232 b $22 240241 c $18 Totals Branch 3 240229 d $20 240231 a $22 240232 b $23 240240 z $22 Totals I would like it to compare the value of the number in column A with that of the number above/below it. If there is a difference of more than 1, I would like it to insert a row with that number, so that eventually all the numbers are sequential. The inserted row should only have the number, no other values in that row. There should be a minimum (240229) and a maximum (240241) found for all values in column A of sheet 1 and everything in between there for each branch should be filled in. For example: Branch 1 240229 240230 x $22 240231 240232 240233 240234 240235 240236 240237 240238 240239 y $25 240240 z $26 240241 Totals Branch 2 240229 240230 x $30 240231 a $58 240232 b $22 240233 240234 240235 240236 240237 240238 240239 240240 240241 c $18 Totals Branch 3 240229 d $20 240230 240231 a $22 240232 b $23 240233 240234 240235 240236 240237 240238 240239 240240 z $22 240241 Totals Any help would be greatly appreciated! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating sequential rows
Hi,
Backup your workbook first in case the code does not perform exactly as you require. Note: The code is written to run on the Active worksheet. If it does not do exactly as you want then let me know. I have included comments to help you follow what is occurring. Sub Insert_Numbers() Dim rngBranch As Range Dim strBranch As String Dim lngBranchNo As Long Dim rngTarget As Range Dim lngMinValue As Long Dim lngMaxValue As Long 'Edit following 2 lines if start/finish numbers change lngMinValue = 240229 lngMaxValue = 240241 lngBranchNo = 1 Do 'Create a string variable with Branch and Number. strBranch = "Branch " & lngBranchNo 'Find Branch and assign cell to a variable Set rngTarget = Columns("A:A") _ .Find(What:=strBranch, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) If Not rngTarget Is Nothing Then 'Branch Number found 'Ensure first value is at correct starting value If rngTarget.Offset(1).Value lngMinValue Then rngTarget.Offset(1).EntireRow.Insert Set rngTarget = rngTarget.Offset(1) rngTarget.Value = lngMinValue Else Set rngTarget = rngTarget.Offset(1) End If Do While rngTarget.Value < lngMaxValue If Abs(rngTarget.Offset(1).Value - rngTarget.Value) 1 Then rngTarget.Offset(1).EntireRow.Insert Set rngTarget = rngTarget.Offset(1) rngTarget.Value = rngTarget.Offset(-1).Value + 1 Else Set rngTarget = rngTarget.Offset(1) End If Loop Else 'Branch number not found so exit sub Exit Sub End If 'Increment the Branch number to find lngBranchNo = lngBranchNo + 1 Loop End Sub -- Regards, OssieMac "PVT" wrote: Hello, I am looking for a macro or other solution which can loop through my worksheet. The worksheet looks as follows: Branch 1 240230 x $22 240239 y $25 240240 z $26 Totals Branch 2 240230 x $30 240231 a $58 240232 b $22 240241 c $18 Totals Branch 3 240229 d $20 240231 a $22 240232 b $23 240240 z $22 Totals I would like it to compare the value of the number in column A with that of the number above/below it. If there is a difference of more than 1, I would like it to insert a row with that number, so that eventually all the numbers are sequential. The inserted row should only have the number, no other values in that row. There should be a minimum (240229) and a maximum (240241) found for all values in column A of sheet 1 and everything in between there for each branch should be filled in. For example: Branch 1 240229 240230 x $22 240231 240232 240233 240234 240235 240236 240237 240238 240239 y $25 240240 z $26 240241 Totals Branch 2 240229 240230 x $30 240231 a $58 240232 b $22 240233 240234 240235 240236 240237 240238 240239 240240 240241 c $18 Totals Branch 3 240229 d $20 240230 240231 a $22 240232 b $23 240233 240234 240235 240236 240237 240238 240239 240240 z $22 240241 Totals Any help would be greatly appreciated! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating sequential rows
On Feb 21, 5:52*am, OssieMac
wrote: Hi, Backup your workbook first in case the code does not perform exactly as you require. Note: The code is written to run on the Active worksheet. If it does not do exactly as you want then let me know. I have included comments to help you follow what is occurring. Sub Insert_Numbers() Dim rngBranch As Range Dim strBranch As String Dim lngBranchNo As Long Dim rngTarget As Range Dim lngMinValue As Long Dim lngMaxValue As Long 'Edit following 2 lines if start/finish numbers change lngMinValue = 240229 lngMaxValue = 240241 lngBranchNo = 1 Do * * 'Create a string variable with Branch and Number. * * strBranch = "Branch " & lngBranchNo * * 'Find Branch and assign cell to a variable * * Set rngTarget = Columns("A:A") _ * * * * .Find(What:=strBranch, _ * * * * LookIn:=xlFormulas, _ * * * * LookAt:=xlPart, _ * * * * SearchOrder:=xlByColumns, _ * * * * SearchDirection:=xlNext, _ * * * * MatchCase:=False, _ * * * * SearchFormat:=False) * * If Not rngTarget Is Nothing Then * * * * 'Branch Number found * * * * 'Ensure first value is at correct starting value * * * * If rngTarget.Offset(1).Value lngMinValue Then * * * * * * rngTarget.Offset(1).EntireRow.Insert * * * * * * Set rngTarget = rngTarget.Offset(1) * * * * * * rngTarget.Value = lngMinValue * * * * Else * * * * * * Set rngTarget = rngTarget.Offset(1) * * * * End If * * * * Do While rngTarget.Value < lngMaxValue * * * * * * If Abs(rngTarget.Offset(1).Value - rngTarget.Value) 1 Then * * * * * * * * rngTarget.Offset(1).EntireRow.Insert * * * * * * * * Set rngTarget = rngTarget.Offset(1) * * * * * * * * rngTarget.Value = rngTarget.Offset(-1).Value + 1 * * * * * * Else * * * * * * * * Set rngTarget = rngTarget.Offset(1) * * * * * * End If * * * * Loop * * Else * * * * 'Branch number not found so exit sub * * * * Exit Sub * * End If * * 'Increment the Branch number to find * * lngBranchNo = lngBranchNo + 1 Loop End Sub -- Regards, OssieMac Thank you for your help. For some reason it is giving me an error run time error 448, named argument not found? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating sequential rows
Actually, if it would be easier, I can also fix the range to compare
the one column to. Say 240230 is the min and 240249 is the max. Each branch will have 240230 as its first number. What I need is a loop that goes through and will insert rows and also should add the missing number. Something like: 1. Find first occurrence of 240230 2. Go to row below. - If number equals 240231, go to next row. - If number 240231, insert a row and insert the number 240231 in column A. Start over at 240230. 3. Loop through list until 240249 is reached 4. Find second occurrence of 240230 etc There will be a maximum of 15 occurrences of 240230 for the 15 branches. The desired end result is still the same as in the first post. I realize this is probably real basic VBA but I am struggling so any help is greatly appreciated! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating sequential rows
The code works perfectly with the sample data you posted and it produces a
result identical to the desired result you posted so we need to establish what the problem is. Error 448 suggests that you are calling the sub routine from another sub and that you are trying to pass a parameter to it. The code that I provided is not desiged to accept a parameter. If this is what you are doing then post the sample of code you are using to call the subroutine and let me know what the value of parameter is that you are trying to pass to it. Your quote: "Actually, if it would be easier, I can also fix the range to compare the one column to. Say 240230 is the min and 240249 is the max." Just edit the following two lines of code for whatever start and finish values you require. lngMinValue = 240229 lngMaxValue = 240241 Your quote: "What I need is a loop that goes through and will insert rows and also should add the missing number." The code does this. Inserts lines plus the missing number. -- Regards, OssieMac "PVT" wrote: Actually, if it would be easier, I can also fix the range to compare the one column to. Say 240230 is the min and 240249 is the max. Each branch will have 240230 as its first number. What I need is a loop that goes through and will insert rows and also should add the missing number. Something like: 1. Find first occurrence of 240230 2. Go to row below. - If number equals 240231, go to next row. - If number 240231, insert a row and insert the number 240231 in column A. Start over at 240230. 3. Loop through list until 240249 is reached 4. Find second occurrence of 240230 etc There will be a maximum of 15 occurrences of 240230 for the 15 branches. The desired end result is still the same as in the first post. I realize this is probably real basic VBA but I am struggling so any help is greatly appreciated! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating sequential rows
In theory the code looks like it would work perfectly!
Could it have something to do with the fact that I am using Excel version 2000 9.0.3821 SR-1 ? I can't get it to work even on the sample data from a brand new workbook. I am not trying to have it accept a parameter. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating sequential rows
I haven't got xl2000 to test but I am guessing that it is a parameter in the
find code giving a problem. (Probably SearchFormat but you don't need it anyway.) That could also explain the error code you were getting but I did not think of it before. A little extra info in case you are not aware of it because it affects my next statement. A space and an underscore at the end of a line is a line break in an otherwise single line of code. Anyway replace the entire line of find code with the following. Set rngTarget = Columns("A:A") _ .Find(What:=strBranch, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext) -- Regards, OssieMac "PVT" wrote: In theory the code looks like it would work perfectly! Could it have something to do with the fact that I am using Excel version 2000 9.0.3821 SR-1 ? I can't get it to work even on the sample data from a brand new workbook. I am not trying to have it accept a parameter. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating sequential rows
If my previous post does not work and it appears to be failing on the Find,
then perhaps you could do something for me to help solve the problem. Turn on the macro recorder and then find something on the worksheet. (find the word Branch will do.) In the options section of the Find dialog box, ensure Match entire cell contents is un-checked. Turn off the macro recorder then post the actual find code that was generated. I just want to see what parameters are there. -- Regards, OssieMac "PVT" wrote: In theory the code looks like it would work perfectly! Could it have something to do with the fact that I am using Excel version 2000 9.0.3821 SR-1 ? I can't get it to work even on the sample data from a brand new workbook. I am not trying to have it accept a parameter. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating sequential rows
Cells.Find(What:="branch", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False).Activate |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating sequential rows
Awesome, that did it! This is the code I ended up using (the part with
the find function): Set rngTarget = Columns("A:A") _ .Find(What:=strBranch, _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False) I can't thank you enough for your help! |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating sequential rows
Having After:=ActiveCell in this code is prone to problems. If the active
cell happens to be outside the column A:A then the code will fail. Suggest you leave it out of the code and just use the following:- Set rngTarget = Columns("A:A") _ .Find(What:=strBranch, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False) However, having said that, if you are ever using the find and want to guarantee that the code will find the first occurrence (of multiple occurrences) in the specified range then use after the last cell in the specified range because the next cell after the last cell in the specified range in which you are looking is the first cell in the range. (Finding next after the active cell does not find the active cell, even if it meets the criteria, unless it is the only occurrence of the find criteria in the range; it finds the next one after it.) Like I said, ActiveCell is prone to problems so avoid it in the Find method. In your code you could use the following:- Set rngTarget = Columns("A:A") _ .Find(What:=strBranch, _ After:=Cells(Rows.Count, "A"), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False) Where Rows.Count is the last row in the column. Note that it does not mention the Activecell so it does not matter which cell is the active cell. Anyway hope it all works out OK for you and that this little lesson also helps. -- Regards, OssieMac "PVT" wrote: Awesome, that did it! This is the code I ended up using (the part with the find function): Set rngTarget = Columns("A:A") _ .Find(What:=strBranch, _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False) I can't thank you enough for your help! |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating sequential rows
Thank you again, you've been a great help!
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating sequential rows
Do While rngTarget.Value < lngMaxValue
If Abs(rngTarget.Offset(1).Value - rngTarget.Value) 1 Then rngTarget.Offset(1).EntireRow.Insert Set rngTarget = rngTarget.Offset(1) rngTarget.Value = rngTarget.Offset(-1).Value + 1 Else Set rngTarget = rngTarget.Offset(1) End If Loop One additional question regarding the above exerpt out of the code: I noticed that while my starting value is the same for every branch, the ending value may be different. This leads to errors while running the macro. How can I adjust the coding so that the ending value posted is always going to be the last value? For example, for branch 1, the ending value is 240241, but for branch 3 the ending value is 240235. How can I make sure that all the numbers until 240241 get prefilled for all branches? Thanks in advance for any help! |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating sequential rows
The code below uses a different approach for the find. However, under test I
believe that the first code example that I posted does work. You have said that the first number in each branch is always the same so the code below is searching for that number. Edit the following variable in the code to match the first number:- lngMinValue = 240229 Edit the following variable to match the highest number required:- lngMaxValue = 240241 Note that lngMaxValue must be at least the value of the highest number in the entire set of numbers. Sub Insert_Numbers2() Dim rngBranch As Range Dim strBranch As String Dim rngFind As Range Dim rngTarget As Range Dim lngMinValue As Long Dim lngMaxValue As Long Dim strFirstAddr As String 'Edit following 2 lines if start/finish numbers change lngMinValue = 240230 lngMaxValue = 240241 'Find Branch and assign cell to a variable Set rngFind = Columns("A:A") _ .Find(What:=lngMinValue, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext) If Not rngFind Is Nothing Then 'Number found so process 'Save address of the first find strFirstAddr = rngFind.Address Do Set rngTarget = rngFind Do While rngTarget < lngMaxValue If Abs(rngTarget.Offset(1).Value - rngTarget.Value) 1 Then rngTarget.Offset(1).EntireRow.Insert Set rngTarget = rngTarget.Offset(1) rngTarget.Value = rngTarget.Offset(-1).Value + 1 Else Set rngTarget = rngTarget.Offset(1) End If Loop Set rngFind = Columns("A:A").FindNext(rngFind) Loop While Not rngFind Is Nothing _ And rngFind.Address < strFirstAddr Else 'Minimum value not found on first find 'so cease processing Exit Sub End If End Sub -- Regards, OssieMac |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating sequential rows
Thank you again for helping me. While working with the codes, I have
found two issues: 1. In the first code you posted, if the last number is not 240241, it will be a text string (for example 'totals'). Therefore, the following formula seems to give an error: Abs(rngTarget.Offset(1).Value - rngTarget.Value) 1 2. The code in your last post gives an error, could that be because there is no initial search for branch? It's getting there but not quite perfect yet... If you have any further help for me I would greatly appreciate it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
creating formulas for sequential numbers in excel | Excel Worksheet Functions | |||
Creating part # tags using sequential numbering. | Excel Discussion (Misc queries) | |||
Creating sequential variables on the fly | Excel Programming | |||
Creating a list in sequential order | Excel Worksheet Functions | |||
Creating Sequential Numbering Macro for an Quotation template | Excel Programming |