Cell refs and VBA

[DavidObeid]

A quick question.

If I write a formula in a cell (say A1) (with or without absolute
references) that uses a range of cells for its argument (eg
=sum(a2:a5)) and I delete a row (say row 3) from my sheet (using
Alt+E+D+R), then my formula will automatically adjust to show the range
altered to a2:a4.

My question is, if I have VBA code written for a sheet, can Excel make
modifications to the VBA code automatically if a row deletion/insertion
takes place? If so, how do I turn this option on?

My code (and coding ability) is very simple:

Public Sub DataCollectionForSpotthedot()
Range("c9").Select
ActiveCell.Value = InputBox("Enter the name of the task")
Range("b13").Select
ActiveCell.Value = InputBox("Enter the 'out of' mark for the task")
Range("A12").Select
ActiveCell.Value = InputBox("Enter the lower cut off for marks you want
displayed on your graph")
Range("A15").Select
End Sub

Regards,

Dave

 

[John Wilson]

David,

can Excel make modifications to the VBA code
automatically if a row deletion/insertion takes place?

Unfortunately, no.

But there are workarounds....

If you name the range you can refer to that "range" in your
VBA code.
You could use functions like Range("A1").end(xlDown)
to look for the row before the first blank cell below A1.
You could use functions like Range("A65536").end(xlUp)
to look for the last unblank cell below A1.
You can redefine a range within VBA too.

There are surely other ways too depending on how your
worksheet is set up.

John

A quick question.

If I write a formula in a cell (say A1) (with or without absolute
references) that uses a range of cells for its argument (eg
=sum(a2:a5)) and I delete a row (say row 3) from my sheet (using
Alt+E+D+R), then my formula will automatically adjust to show the range
altered to a2:a4.

My question is, if I have VBA code written for a sheet, can Excel make
modifications to the VBA code automatically if a row deletion/insertion
takes place? If so, how do I turn this option on?

My code (and coding ability) is very simple:

Public Sub DataCollectionForSpotthedot()
Range("c9").Select
ActiveCell.Value = InputBox("Enter the name of the task")
Range("b13").Select
ActiveCell.Value = InputBox("Enter the 'out of' mark for the task")
Range("A12").Select
ActiveCell.Value = InputBox("Enter the lower cut off for marks you want
displayed on your graph")
Range("A15").Select
End Sub

Regards,

Dave


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to

creating financial statements

 

[raymondsum]

Dave,

You want to fix input data in C9, B13 and A12, no matter row deletion /
insertion takes place.
If i dont misunderstand your request, you can try the follwoing,
otherwise, you forget it or tell me more detail.

Application.Goto Reference:="R9C3"
ActiveCell.Value = InputBox("Enter the name of the task")
Application.Goto Reference:="R13C2"
ActiveCell.Value = InputBox("Enter the 'out of' mark for the task")
Application.Goto Reference:="R12C1"
ActiveCell.Value = InputBox("Enter the lower cut off for marks you want
displayed on your graph")
Range("A15").Select

the above macro will fixed the input box in C9, B13, A12.

HTH

Raymond

 

[DavidObeid]

Raymond,

I don't want to fix where the inputs go. I want the VBA to be able t
handle row deletions/insertions so that the inputs go in the correc
place RELATIVE to other cells in the sheet.

Thanks anyhow.

:

 

[raymondsum]

Dave,

Your original inout box in cell C9, B13, A12 and then after deletion 3
rows, for example, the input box in C6, B10, A9. Is it correct?

Raymond

 

[John Wilson]

Raymond,

It can't be done.
If you write VBA code such that Range("A12").Select
Range("A12") will be selected (regardless of what's
been moved, sorted, copied, inserted, deleted, etc.)
There is nothing that you can do on a worksheet that will
modify the VBA code.
YAHOO!! (You Always Have Other Options), some of which
I gave you in my original post.

John

Raymond,

I don't want to fix where the inputs go. I want the VBA to be able to
handle row deletions/insertions so that the inputs go in the correct
place RELATIVE to other cells in the sheet.

Thanks anyhow.




------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to

creating financial statements

 

[raymondsum]

John,

Thank you for your advise.

Raymond

 

[Stephen Bullen]

Hi Dave,

My question is, if I have VBA code written for a sheet, can Excel make
modifications to the VBA code automatically if a row deletion/insertion
takes place? If so, how do I turn this option on?

The usual technique is to give the cells a name, then refer to that name in code. When adding/removing rows, Excel will keep the
name updated. To give a cell a name, select it, then type in the name in the 'Name box' on the left hand side of the formula bar and
press Enter.

Once you've done that, you can refer to those names in your code

My code (and coding ability) is very simple:

For what it's worth, you rarely need to select things to work with them:

Public Sub DataCollectionForSpotthedot()

Range("TaskName").Value = InputBox("Enter the name of the task")

Range("OutOfMark").Value = InputBox("Enter the 'out of' mark for the task")

Range("LowerCutOff").Value = InputBox("Enter the lower cut off for marks you want
displayed on your graph")

End Sub

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk

For the most timely access to the newsgroups, use Outlook Express to connect to msnews.microsoft.com

 

[DavidObeid]

Stephen,

Thankyou so much! I can't get over how helpful this forum is.

If I ever get really proficient at excel I hope I can be as helpful t
others as people here are to me.

Regards,

Dav