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

0 Members and 1 Guest are viewing this topic.

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: 12417
  • 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: 12417
  • 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: 12417
  • 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: 12417
  • 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