TheSwamp
Code Red => VB(A) => Topic started by: ML on November 19, 2007, 01:22:44 PM
-
I know this is for "ACAD" but I realize a lot of people on here do program with Excel as well; me being one of them
So I was wondering if anyone could answer this question for me?
I have macros, that read from very specific rows however one thing I never looked in to (and now need to) was; if the user inserts a new row, how can my macros dynamically adjust to the insert?
Here is a small example of something I did
Dim i As Integer
On Error Resume Next
'Check for unpaid bills
With Sheets(2).Select
For i = 6 To 22
If Range("N" & i).Value > 0 Then
If MsgBox("Do you want to overwrite last week's bills?", vbQuestion + vbYesNo, "Checking Accounts") = vbYes & vbCrLf _
Then GoTo Continue Else
Range("L6").Select
Exit Sub
End If
Next i
End With
Now if the user inserts a row, my range would now need to be 6 to 23.
Is there a way to do this in VBA?
Thank you
Mark
-
Or if it was written like this:
With Sheets(2).Select
If Range(N6:N22).Value > 0 Then
If MsgBox("Do you want to overwrite last week's bills?", vbQuestion + vbYesNo, "Checking Accounts") = vbYes & vbCrLf _
Then GoTo Continue Else
Range("L6").Select
Exit Sub
End If
Next i
End With
-
Now if the user inserts a row, my range would now need to be 6 to 23.
Is there a way to do this in VBA?
Thank you
Mark
I would probably use a named range: select some cells and click on INSERT -- NAME -- DEFINE and enter a name. And just to test it, add a hyperlink to the newly created range. Then click on the link - you'll see the range of cells highlighted. Now add a new row and then click on the link again. You'll see that the highlighted cells now include the inserted row.
-
Matt
I don't hink that will adjust the macro in VBA.
Plus I don't want to take the chance of the named range changing either.
I need a way to increment the code using rows
For example.
If User Inserts a row
Then row that has a macro using it +1
End If
There is an AllowRowInsert Method but I don't want to do that to the user
They "should" not be limited like that; so I need to think more dynamically
Thank you
Mark
-
Matt
I don't hink that will adjust the macro in VBA.
Plus I don't want to take the chance of the named range changing either.
I need a way to increment the code using rows
For example.
If User Inserts a row
Then row that has a macro using it +1
End If
There is an AllowRowInsert Method but I don't want to do that to the user
They "should" not be limited like that; so I need to think more dynamically
Thank you
Mark
It won't adjust the code - and you don't need it to if you use a named range. And the only way it could get renamed is if someone actually went through the motions of renaming it - if they even knew how to.
Could you post a sample XLS file?
-
Sorry Matt
I respectfully disagree
I do think the code needs to be adjusted and I know it can be.
Possibly if I use a
Do While Loop
Hummmm
I need to look more into it
Thanks
Mark
-
fwiw I have to agree with Matt, named ranges are the way to go here. That's about the only way you can account for users changing/adding/deleting rows.
Changing a named range isn't something you average user would know how to do
-
Hey Hendie
How are you?
Wait a minute; I think I missed something that Matt said
If I name a range and insert a row, the named range will pick up the inserted row; or there is a way to make it do so?
Thank you,
Mark
-
If I name a range and insert a row, the named range will pick up the inserted row...
Yup... The named range will auto-magically detect the inserted row. Give it a shot!
-
Ok
I will try it now Matt
Thanks
Mark
-
Ok
I will try it now Matt
Thanks
Mark
Sure.... NOW you'll try it. :wink:
-
LOL
Now I am saying WTF!!!!!!
My spread sheet is not allowing me to define a name
It has in the past
On Worksheet activate
I set allowinsertrows to = false
I think this has something to do with it; so I need to work that out first Uggggggggggg
Do you have macros that point a cell range?
How are they affected when you insert a row
Thank you,
Mark
-
Do you have macros that point a cell range?
How are they affected when you insert a row
Thank you,
Mark
You don't need a macro. See the attached ZIP file.
-
If you wanted to select the range via code, all you need is this: Range("list_of_items").Select where 'list_of_items' is the name of the range.
-
Hummm
That is interesting
Under the help screen - Range Object,
I found this bit of code
While it is not precisely what I want; it is very close to what I am thinking
Mark
i = 1
For Each a In Selection.Areas
MsgBox "Area " & i & " of the selection contains " & _
a.Rows.Count & " rows."
i = i + 1
Next a
The above code is using the count method but I am thinking of using the row insert method
Mark
-
In my first example I might be able to say,
Pseudo code:
If row is inserted i = i +1
Mark
For i = 6 To 22
If Range("N" & i).Value > 0 Then
If MsgBox("Do you want to overwrite last week's bills?", vbQuestion + vbYesNo, "Checking Accounts") = vbYes & vbCrLf _
Then GoTo Continue Else
Range("L6").Select
Exit Sub
End If
Next i
-
the thing is, if you go down that road, you need to know how many rows have been added/deleted.
With the named range, it doesn't matter.. it'll take care of itself
-
Interesting!
Let me take a look at Matt's example and post back.
Hendie;
Then what happens to your macros? They go unchanged?
My spreadsheet has a lot of automation
Couldn't I say
For Each Row
Then i = i +1
End If
-
Can't you put something after the last row and have your loop
from 6 to EOL
or something like that?
-
THat is what I started out looking to do Greg
Just not sure of the proper method
Mark
-
Matt
I did look at your example; that is really cool and good to know; however, the link still either needs to be manually clicked or done through code.
I did record the macro as I ran through it --->
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Rows("3:3").Select
Selection.Insert Shift:=xlDown
Rows("5:5").Select
Selection.Insert Shift:=xlDown
Rows("7:7").Select
Selection.Insert Shift:=xlDown
Rows("9:9").Select
Selection.Insert Shift:=xlDown
Rows("11:11").Select
Selection.Insert Shift:=xlDown
Rows("13:13").Select
Selection.Insert Shift:=xlDown
Rows("15:15").Select
Selection.Insert Shift:=xlDown
Rows("16:17").Select
Selection.Insert Shift:=xlDown
Now, the one definite advantage of this method is that I can undo which I can not do (or know how to do) after running an Excel macro.
It is like grabbing a selection set in ACAD
Thank you again
I will play with it a bit more
Mark
-
This will find the last row that has any text in it. Might be of some help to you.
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[a1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End If
So then it would be 6 to LastRow
-
Obviously we could address this all in code
Including updating the range after an insert
Hummmmm
I need to take a closer look
Mark
-
Nice Matt.
Your's defines the last row and right now you have a wild card character.
-
It would be great if I can catch the insert row event (hummmmmmmmm)
I don't think there is such an event however, there is a worksheet change event - DING DING DING
Again, pseudo code:
On Worksheet change
For each row in worksheet
If a row is inserted or Rowinserted = True
Each range in macro +1
End If
Next Row
So if the range is N6, it will now become N7
That would be dynamic :)
Mark
-
It would be great if I can catch the insert row event (hummmmmmmmm)
I don't think there is such an event however, there is a worksheet change event - DING DING DING
Again, pseudo code:
On Worksheet change
For each row in worksheet
If a row is inserted or Rowinserted = True
Each range in macro +1
End If
Next Row
So if the range is N6, it will now become N7
That would be dynamic :)
Mark
This seems to make it more difficult then it needs to be.
-
It would be great if I can catch the insert row event (hummmmmmmmm)
I don't think there is such an event however, there is a worksheet change event - DING DING DING
Again, pseudo code:
On Worksheet change
For each row in worksheet
If a row is inserted or Rowinserted = True
Each range in macro +1
End If
Next Row
So if the range is N6, it will now become N7
That would be dynamic :)
Mark
Why are you making this more difficult than it needs to be? Just use the range and call it a day? (unless I'm missing something??)
-
It would be great if I can catch the insert row event (hummmmmmmmm)
I don't think there is such an event however, there is a worksheet change event - DING DING DING
Again, pseudo code:
On Worksheet change
For each row in worksheet
If a row is inserted or Rowinserted = True
Each range in macro +1
End If
Next Row
So if the range is N6, it will now become N7
That would be dynamic :)
Mark
Why are you making this more difficult than it needs to be? Just use the range and call it a day? (unless I'm missing something??)
He had trouble when trying to do this.
A couple posts back.
-
OK, we'll do
Thank you everyone
Mark
-
Hey guys
I did play with the named range quite a bit.
I saw how inserting a row with a named range will work with formulas and that is great to know,
still I am using a lot of VBA in this spread sheet.
I have this bit of code in the worksheet calculate event
Private Sub Worksheet_Calculate()
'Extra Cash Cells in Row 5 = Extra Cash Cells in Row 127
Range("C5").Value = Range("C127")
Range("D5").Value = Range("D127")
Range("E5").Value = Range("E127")
Range("F5").Value = Range("F127")
Range("G5").Value = Range("G127")
Well, even with a named range, inserting rows etc., it still will not pick up the change.
So, if you inserted a row a 127, then the cell C5 will become blank.
I am open to suggestions but I still believe that this will involve some dynamic coding of some sort
Thank you
Mark
-
Hey guys
I did play with the named range quite a bit.
I saw how inserting a row with a named range will work with formulas and that is great to know,
still I am using a lot of VBA in this spread sheet.
I have this bit of code in the worksheet calculate event
Private Sub Worksheet_Calculate()
'Extra Cash Cells in Row 5 = Extra Cash Cells in Row 127
Range("C5").Value = Range("C127")
Range("D5").Value = Range("D127")
Range("E5").Value = Range("E127")
Range("F5").Value = Range("F127")
Range("G5").Value = Range("G127")
Well, even with a named range, inserting rows etc., it still will not pick up the change.
So, if you inserted a row a 127, then the cell C5 will become blank.
I am open to suggestions but I still believe that this will involve some dynamic coding of some sort
Thank you
Mark
Change the hard-coded ranges to Named ranges and you should be all set.
-
Hi Matt
Actually
I did do that but that even became un necessary
I just simply told C5 to Sum C127, creating a link between the two cell and so and that resolved it.
There is no need to even use VBA or The Calculate Event in this case as 127 has a formula in it.
I may use that approach in another spot on the worksheet
Still, what intrigues me is this:
When we insert a row, the formulas dynamically adjusts, so why can't VBA code do this?
Mark
-
You are definitely right
It is easier to do something with a named range then to address individual cell (array) ranges.
I guess if we compared it to ACAD, it is like a selection set in ACAD; once we have a selection, we can filter and do some things.
I haven't had much time but I was also looking at the re size method.
Possibly something like:
With named range
If row.resize (row.count +1) = True
Then do something
End If
End With
Understand, I have may be 3 different scenarios where this method needs to be employed; not just the one example that I posted which was easily taken care of.
Also, I don't have a problem posting the spread sheet except there is some personal financial info in there that I would have to first strip out.
Sound like I am gettting closer?
Thank you,
Mark
-
You are definitely right
It is easier to do something with a named range then to address individual cell (array) ranges.
Told you so! :wink:
Keep at it!!
-
Hey Matt
Definitely; named ranges are nice and much more dynamic.
I am now trying to figure out how to get to each row or cell in a named range.
Also,
If you name a range Sh1bills
With this bit of code:
Sub Sh1bills()
Set rng1 = Range("Sh1bills")
Set rng1 = rng1.Resize(rng1.Rows.Count + 1)
MsgBox rng1.Rows.Count
You can have the msgbox return the number of rows in the range.
If you insert or delete a row in the range and run the macro again, it will return the new row count.
This is a good start but I need a way to say, if row is inserted or deleted, then
Do something
Any ideas?
Also, would you put that in the change event?
Thank you,
Mark
-
This is a good start but I need a way to say, if row is inserted or deleted, then
Do something
Any ideas?
This might be of some help: http://www.dailydoseofexcel.com/archives/2006/08/21/capture-deleted-rows/
-
Cool!
It looks good
I will have to look closer as soon as I have time
Thanks Matt!
Mark
-
Cool!
It looks good
I will have to look closer as soon as I have time
Thanks Matt!
Mark
No prob, Bob!...erm... Mark! Sorry... been watching WAAAAY too much 'Bob the Builder' lately... because of.... the... kids.
Yeah... that's it! The kids! :roll:
-
Hey! Do the kids know VBA? :)
-
Hey! Do the kids know VBA? :)
No, but they're well-versed in 'Cars', 'Sesame Street', 'The Muppet Show' (can't believe I got 'em hooked on THAT), 'Wow! Wow! Wubbzy!' and 'Oobi'.
-
Hey, that's cool
How about I go watch Kermit the Frog with them and you finish this for me? :)