Author Topic: Excel and VBA Question  (Read 5782 times)

0 Members and 1 Guest are viewing this topic.

ML

  • Guest
Excel and VBA Question
« 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
Code: [Select]
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

ML

  • Guest
Re: Excel and VBA Question
« Reply #1 on: November 19, 2007, 01:24:50 PM »

Or if it was written like this:

Code: [Select]
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



Guest

  • Guest
Re: Excel and VBA Question
« Reply #2 on: November 19, 2007, 02:10:17 PM »
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.

ML

  • Guest
Re: Excel and VBA Question
« Reply #3 on: November 19, 2007, 02:18:05 PM »

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


Guest

  • Guest
Re: Excel and VBA Question
« Reply #4 on: November 19, 2007, 02:21:30 PM »

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?

ML

  • Guest
Re: Excel and VBA Question
« Reply #5 on: November 19, 2007, 02:26:52 PM »

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

hendie

  • Guest
Re: Excel and VBA Question
« Reply #6 on: November 19, 2007, 02:38:26 PM »
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

ML

  • Guest
Re: Excel and VBA Question
« Reply #7 on: November 19, 2007, 02:48:44 PM »

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

Guest

  • Guest
Re: Excel and VBA Question
« Reply #8 on: November 19, 2007, 03:03:26 PM »
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!

ML

  • Guest
Re: Excel and VBA Question
« Reply #9 on: November 19, 2007, 03:30:59 PM »

Ok

I will try it now Matt

Thanks

Mark

Guest

  • Guest
Re: Excel and VBA Question
« Reply #10 on: November 19, 2007, 03:58:05 PM »

Ok

I will try it now Matt

Thanks

Mark

Sure.... NOW you'll try it.   :wink:

ML

  • Guest
Re: Excel and VBA Question
« Reply #11 on: November 19, 2007, 04:03:54 PM »

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

Guest

  • Guest
Re: Excel and VBA Question
« Reply #12 on: November 19, 2007, 04:12:27 PM »
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.

Guest

  • Guest
Re: Excel and VBA Question
« Reply #13 on: November 19, 2007, 04:15:55 PM »
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.

ML

  • Guest
Re: Excel and VBA Question
« Reply #14 on: November 19, 2007, 04:21:55 PM »

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

Code: [Select]
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

ML

  • Guest
Re: Excel and VBA Question
« Reply #15 on: November 19, 2007, 04:24:36 PM »

In my first example I might be able to say,

Pseudo code:
If row is inserted i = i +1

Mark

Code: [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

hendie

  • Guest
Re: Excel and VBA Question
« Reply #16 on: November 19, 2007, 04:37:33 PM »
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

ML

  • Guest
Re: Excel and VBA Question
« Reply #17 on: November 19, 2007, 04:48:20 PM »

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

Greg B

  • Seagull
  • Posts: 12334
  • Tell me a Joke!
Re: Excel and VBA Question
« Reply #18 on: November 19, 2007, 04:51:34 PM »
Can't you put something after the last row and have your loop

from 6 to EOL

or something like that?

ML

  • Guest
Re: Excel and VBA Question
« Reply #19 on: November 19, 2007, 04:53:09 PM »

THat is what I started out looking to do Greg
Just not sure of the proper method

Mark

ML

  • Guest
Re: Excel and VBA Question
« Reply #20 on: November 19, 2007, 04:58:47 PM »

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 --->

Code: [Select]
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

Guest

  • Guest
Re: Excel and VBA Question
« Reply #21 on: November 19, 2007, 04:59:25 PM »
This will find the last row that has any text in it.  Might be of some help to you.

Code: [Select]
   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

ML

  • Guest
Re: Excel and VBA Question
« Reply #22 on: November 19, 2007, 04:59:35 PM »
Obviously we could address this all in code
Including updating the range after an insert

Hummmmm

I need to take a closer look

Mark

Greg B

  • Seagull
  • Posts: 12334
  • Tell me a Joke!
Re: Excel and VBA Question
« Reply #23 on: November 19, 2007, 05:05:12 PM »
Nice Matt.

Your's defines the last row and right now you have a wild card character.


ML

  • Guest
Re: Excel and VBA Question
« Reply #24 on: November 19, 2007, 05:17:09 PM »

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

Greg B

  • Seagull
  • Posts: 12334
  • Tell me a Joke!
Re: Excel and VBA Question
« Reply #25 on: November 19, 2007, 05:23:30 PM »

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.

Guest

  • Guest
Re: Excel and VBA Question
« Reply #26 on: November 20, 2007, 08:26:19 AM »

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??)

Greg B

  • Seagull
  • Posts: 12334
  • Tell me a Joke!
Re: Excel and VBA Question
« Reply #27 on: November 20, 2007, 09:02:02 AM »

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.

ML

  • Guest
Re: Excel and VBA Question
« Reply #28 on: November 20, 2007, 09:29:48 AM »

OK, we'll do

Thank you everyone

Mark

ML

  • Guest
Re: Excel and VBA Question
« Reply #29 on: November 27, 2007, 02:27:11 PM »

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

Code: [Select]
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


Guest

  • Guest
Re: Excel and VBA Question
« Reply #30 on: November 28, 2007, 12:06:46 PM »

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

Code: [Select]
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.

ML

  • Guest
Re: Excel and VBA Question
« Reply #31 on: November 28, 2007, 12:11:12 PM »
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


ML

  • Guest
Re: Excel and VBA Question
« Reply #32 on: November 30, 2007, 04:03:21 PM »

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:

Code: [Select]
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

Guest

  • Guest
Re: Excel and VBA Question
« Reply #33 on: December 04, 2007, 09:12:37 AM »

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!!

ML

  • Guest
Re: Excel and VBA Question
« Reply #34 on: December 04, 2007, 03:55:12 PM »

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:
Code: [Select]
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

Guest

  • Guest
Re: Excel and VBA Question
« Reply #35 on: December 04, 2007, 04:22:23 PM »
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/

ML

  • Guest
Re: Excel and VBA Question
« Reply #36 on: December 04, 2007, 04:43:54 PM »


Cool!

It looks good
I will have to look closer as soon as I have time

Thanks Matt!

Mark

Guest

  • Guest
Re: Excel and VBA Question
« Reply #37 on: December 04, 2007, 04:46:19 PM »


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:

ML

  • Guest
Re: Excel and VBA Question
« Reply #38 on: December 04, 2007, 04:51:03 PM »

Hey! Do the kids know VBA? :)

Guest

  • Guest
Re: Excel and VBA Question
« Reply #39 on: December 04, 2007, 04:53:45 PM »

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'.

ML

  • Guest
Re: Excel and VBA Question
« Reply #40 on: December 04, 2007, 04:59:37 PM »

Hey, that's cool
How about I go watch Kermit the Frog with them and you finish this for me? :)