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

0 Members and 1 Guest are viewing this topic.

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