I'm curious within a macro, Is there a way of making a range value more
versatile? ie: could the range value reply to a MsgBox? Thus meaning not
having to edit the macro if number of changes say the :A100 is now :A150

It can be more versatile, yes.

You could have something like this I suupose;

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

Dim intRangeEnd as Integer

ReStartRange:

intRangeEnd = MsgBox("Please enter a range end", vbYesNo + vbQuestion,
"Range End")

If Not(IsNumeric(intRangeEnd))
MsgBox "Please enter a numeric value for the range end"
Goto ReStartRange
End If

Range("A1:A" & intRangeEnd ).Select

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

HTH.


"Rob" wrote:

I'm curious within a macro, Is there a way of making a range value more
versatile? ie: could the range value reply to a MsgBox? Thus meaning not
having to edit the macro if number of changes say the :A100 is now :A150

Thanks For the reply, I've tried the formula with or without a slight
modification,
it seems to be doing something but mainly what is bugging me is actually to
enter a numeric value within the msgbox would you explain or clarify what its
doing please .

Thanks Rob

"DaveO" wrote:

It can be more versatile, yes.

You could have something like this I suupose;

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

Dim intRangeEnd as Integer

ReStartRange:

intRangeEnd = MsgBox("Please enter a range end", vbYesNo + vbQuestion,
"Range End")

If Not(IsNumeric(intRangeEnd))
MsgBox "Please enter a numeric value for the range end"
Goto ReStartRange
End If

Range("A1:A" & intRangeEnd ).Select

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

HTH.


"Rob" wrote:

I'm curious within a macro, Is there a way of making a range value more
versatile? ie: could the range value reply to a MsgBox? Thus meaning not
having to edit the macro if number of changes say the :A100 is now :A150

No problems.

I assumed several things;

1. The range columns were static, so in this instance only column 'A' was
being looked at.
2. You did not wish to specify a start cell as well. (Ie. the rabge was
always fixed to 'A1:A{X}' where {X} is the thing that is collected in the
message box.

As I made these assumptions I only wanted a numeric value as the range end
as I know the rest and a non-numeric value would cause an error.

The part of the code which checks what has been entered is...

-------------------------------
If Not(IsNumeric(intRangeEnd))
-------------------------------

Basically it's checking whats been entered.

Sorry if I've tried telling you how to suck an egg here.

The code can be altered to allow more flexibility if needed. Tell me what
you;d liek to try and do, and I'll point you in the right direction.

HTH.

"Rob" wrote:

Thanks For the reply, I've tried the formula with or without a slight
modification,
it seems to be doing something but mainly what is bugging me is actually to
enter a numeric value within the msgbox would you explain or clarify what its
doing please .

Thanks Rob

"DaveO" wrote:

It can be more versatile, yes.

You could have something like this I suupose;

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

Dim intRangeEnd as Integer

ReStartRange:

intRangeEnd = MsgBox("Please enter a range end", vbYesNo + vbQuestion,
"Range End")

If Not(IsNumeric(intRangeEnd))
MsgBox "Please enter a numeric value for the range end"
Goto ReStartRange
End If

Range("A1:A" & intRangeEnd ).Select

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

HTH.


"Rob" wrote:

I'm curious within a macro, Is there a way of making a range value more
versatile? ie: could the range value reply to a MsgBox? Thus meaning not
having to edit the macro if number of changes say the :A100 is now :A150

Hi DaveO,

That was quick, In general I am trying to insert a row of numbers up to a
range in the last avaliable (blank) colunm, this is for Termination purpose,
as the original output report files contains duplicate & missing terminals,

The duplicate part had been sorted, what I'm attempting to do now is to fill
in missing terminals, with the last column I would use the reference to paste
the existing terminals to it respective place.

The Range value ie: (A1:A120) would be needed here.

As you've bought the alternative up just numbers. Thinking aloud here, as so
far there are quite a few macros that contains a loop system which naturally
has a counter value (number of times to loop) could this be altered via
msgBox also. To be honest I'm not sure if I would use it here, probably
because wouldn't want other users to mess it up. It would only be functional
by myself as a reminder.

Thanks for your assistance.

Look forward to your reply.



"DaveO" wrote:

No problems.

I assumed several things;

1. The range columns were static, so in this instance only column 'A' was
being looked at.
2. You did not wish to specify a start cell as well. (Ie. the rabge was
always fixed to 'A1:A{X}' where {X} is the thing that is collected in the
message box.

As I made these assumptions I only wanted a numeric value as the range end
as I know the rest and a non-numeric value would cause an error.

The part of the code which checks what has been entered is...

-------------------------------
If Not(IsNumeric(intRangeEnd))
-------------------------------

Basically it's checking whats been entered.

Sorry if I've tried telling you how to suck an egg here.

The code can be altered to allow more flexibility if needed. Tell me what
you;d liek to try and do, and I'll point you in the right direction.

HTH.

"Rob" wrote:

Thanks For the reply, I've tried the formula with or without a slight
modification,
it seems to be doing something but mainly what is bugging me is actually to
enter a numeric value within the msgbox would you explain or clarify what its
doing please .

Thanks Rob

"DaveO" wrote:

It can be more versatile, yes.

You could have something like this I suupose;

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

Dim intRangeEnd as Integer

ReStartRange:

intRangeEnd = MsgBox("Please enter a range end", vbYesNo + vbQuestion,
"Range End")

If Not(IsNumeric(intRangeEnd))
MsgBox "Please enter a numeric value for the range end"
Goto ReStartRange
End If

Range("A1:A" & intRangeEnd ).Select

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

HTH.


"Rob" wrote:

I'm curious within a macro, Is there a way of making a range value more
versatile? ie: could the range value reply to a MsgBox? Thus meaning not
having to edit the macro if number of changes say the :A100 is now :A150

No problems Rob.

I'm getting a little lost here and don't really understand what you're
asking. However if you;re askig what I think, then I hope this helps.

In response to the loop, it could all be automatically within the code or
you could let it loop a number of times based on a MsgBox. However, I'd
prefer to loop through the cells, check to see when the data either starts or
ends and then use that as my range, rather than have to have a user interface.

As an example...

If column 'B' had all of the Terminals you wished to paste assuming it was
the whole column

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

dim intCounter as integer

do while len(range("B" & intCounter).Text) < 0
intCounter = intCounter + 1
loop

intCounter = intCounter - 1

Range("B1:B" & intCounter).select
selection.copy

Range("A1:A" & intCounter).select
selection.paste

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

I hope this gives you an idea of what I mean.

If this doesn't help, could you explain a little more please and I'll see
what I can do to help.

HTH.

"Rob" wrote:

Hi DaveO,

That was quick, In general I am trying to insert a row of numbers up to a
range in the last avaliable (blank) colunm, this is for Termination purpose,
as the original output report files contains duplicate & missing terminals,

The duplicate part had been sorted, what I'm attempting to do now is to fill
in missing terminals, with the last column I would use the reference to paste
the existing terminals to it respective place.

The Range value ie: (A1:A120) would be needed here.

As you've bought the alternative up just numbers. Thinking aloud here, as so
far there are quite a few macros that contains a loop system which naturally
has a counter value (number of times to loop) could this be altered via
msgBox also. To be honest I'm not sure if I would use it here, probably
because wouldn't want other users to mess it up. It would only be functional
by myself as a reminder.

Thanks for your assistance.

Look forward to your reply.



"DaveO" wrote:

No problems.

I assumed several things;

1. The range columns were static, so in this instance only column 'A' was
being looked at.
2. You did not wish to specify a start cell as well. (Ie. the rabge was
always fixed to 'A1:A{X}' where {X} is the thing that is collected in the
message box.

As I made these assumptions I only wanted a numeric value as the range end
as I know the rest and a non-numeric value would cause an error.

The part of the code which checks what has been entered is...

-------------------------------
If Not(IsNumeric(intRangeEnd))
-------------------------------

Basically it's checking whats been entered.

Sorry if I've tried telling you how to suck an egg here.

The code can be altered to allow more flexibility if needed. Tell me what
you;d liek to try and do, and I'll point you in the right direction.

HTH.

"Rob" wrote:

Thanks For the reply, I've tried the formula with or without a slight
modification,
it seems to be doing something but mainly what is bugging me is actually to
enter a numeric value within the msgbox would you explain or clarify what its
doing please .

Thanks Rob

"DaveO" wrote:

It can be more versatile, yes.

You could have something like this I suupose;

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

Dim intRangeEnd as Integer

ReStartRange:

intRangeEnd = MsgBox("Please enter a range end", vbYesNo + vbQuestion,
"Range End")

If Not(IsNumeric(intRangeEnd))
MsgBox "Please enter a numeric value for the range end"
Goto ReStartRange
End If

Range("A1:A" & intRangeEnd ).Select

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

HTH.


"Rob" wrote:

I'm curious within a macro, Is there a way of making a range value more
versatile? ie: could the range value reply to a MsgBox? Thus meaning not
having to edit the macro if number of changes say the :A100 is now :A150