|
Range Value ie: (A1:A100) The Option of?
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 |
Range Value ie: (A1:A100) The Option of?
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 |
Range Value ie: (A1:A100) The Option of?
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 |
Range Value ie: (A1:A100) The Option of?
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 |
Range Value ie: (A1:A100) The Option of?
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 |
Range Value ie: (A1:A100) The Option of?
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 |
Range Value ie: (A1:A100) The Option of?
Sorry didn't make myself understood,
Below is an example which would also be applied to column "F" with a starting value of 1, notice I've used "xlFillSeries & the value range is 13 but could go to 200" not sure if it's best to be done by loop system. Anyway this information is then stored onto a blank worksheet. next step would be to goto worksheet that contains the terminals that has all the other info. such as cables etc. From here I would compare the 2 worksheet (row by row) if first value in column "F" equals the same the blank worksheet then to copy that row into the blank worksheet. My first macro didn't do it this way, at the time didn't have the loop system knowlege. It was done in this format "If Range E1< "1" then Row ("1:1).select Select.Insert Shift=xlDown, then entered the value range into E1 etc. this had to generate hundreds of macros to make this work ie per row. Range("M1").Select ActiveCell.FormulaR1C1 = "1001" Range("M1").Select Selection.AutoFill Destination:=Range("M2:M13"), Type:=xlFillSeries Range("M1:M13").Select Hope this is more of Help? Of to lunch now will be back in an hour "DaveO" wrote: 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 |
Range Value ie: (A1:A100) The Option of?
OK, so taking your code, and assuming that column N holds data for all of the
rows we're interested in... (If not column 'N' then replace it with a column letter that does.) --------------------------------------- dim intCounter as integer do while len(range("N" & intCounter).Text) < 0 intCounter = intCounter + 1 loop intCounter = intCounter - 1 Range("M1").select selection.copy Range("M2:M" & intCounter).select selection.paste --------------------------------------- Does this help? "Rob" wrote: Sorry didn't make myself understood, Below is an example which would also be applied to column "F" with a starting value of 1, notice I've used "xlFillSeries & the value range is 13 but could go to 200" not sure if it's best to be done by loop system. Anyway this information is then stored onto a blank worksheet. next step would be to goto worksheet that contains the terminals that has all the other info. such as cables etc. From here I would compare the 2 worksheet (row by row) if first value in column "F" equals the same the blank worksheet then to copy that row into the blank worksheet. My first macro didn't do it this way, at the time didn't have the loop system knowlege. It was done in this format "If Range E1< "1" then Row ("1:1).select Select.Insert Shift=xlDown, then entered the value range into E1 etc. this had to generate hundreds of macros to make this work ie per row. Range("M1").Select ActiveCell.FormulaR1C1 = "1001" Range("M1").Select Selection.AutoFill Destination:=Range("M2:M13"), Type:=xlFillSeries Range("M1:M13").Select Hope this is more of Help? Of to lunch now will be back in an hour "DaveO" wrote: 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 |
Range Value ie: (A1:A100) The Option of?
I'm a little lost now, where does the value range come in? I am beginning to
think maybe to use a list box? As the when the Value Range is entered, this would entered into a cell, outside the working (pasting or instalation of terminal) range. Then maybe using a MsgBox Range("N1").Value or cell reference then using one of these codes, to install therminal ranges required in Column "F" & "M". I had thought off this a few days ago but was trying to avoid cell function into the worksheet. It just occured to me, where the confusion might be. In column N (contains Counta Formula) which counts the number of terminals within that work sheet, which could be incorrect. So a query "Is this a correct number of terminals?" If it "True" is then simply Copy from old worksheet & paste into new worksheet paste followed by installation of the terminal block in Column "m" (currently doesn't know how many terminals to install). If it's false somehow is to add correct number of terminal, just a thought something like Range value ("N") + Range value ("O") = total value. With this info the system then goes to new wooksheet & installs teriminal into "F & M" prior to paste from old worksheet. The comparison mentioned earlier, is if old worksheet is supposed to have 3 terminal for instant, but terminals avaliable are 1 & 3 terminal 2 is missing, but is avaliable in the new worksheet, so terminals 1 & 3 are pasted to it's respective place Is pasting a good way? As in the past have had crashed the system, used too much memory space, does saving worksheet at intervals clear memory space? The function codes you've sent me, am I To just copy & paste into a sub macro? As it comes up with compile error or sub function not defined? Sorry about this, hope not to be to much bother. "DaveO" wrote: OK, so taking your code, and assuming that column N holds data for all of the rows we're interested in... (If not column 'N' then replace it with a column letter that does.) --------------------------------------- dim intCounter as integer do while len(range("N" & intCounter).Text) < 0 intCounter = intCounter + 1 loop intCounter = intCounter - 1 Range("M1").select selection.copy Range("M2:M" & intCounter).select selection.paste --------------------------------------- Does this help? "Rob" wrote: Sorry didn't make myself understood, Below is an example which would also be applied to column "F" with a starting value of 1, notice I've used "xlFillSeries & the value range is 13 but could go to 200" not sure if it's best to be done by loop system. Anyway this information is then stored onto a blank worksheet. next step would be to goto worksheet that contains the terminals that has all the other info. such as cables etc. From here I would compare the 2 worksheet (row by row) if first value in column "F" equals the same the blank worksheet then to copy that row into the blank worksheet. My first macro didn't do it this way, at the time didn't have the loop system knowlege. It was done in this format "If Range E1< "1" then Row ("1:1).select Select.Insert Shift=xlDown, then entered the value range into E1 etc. this had to generate hundreds of macros to make this work ie per row. Range("M1").Select ActiveCell.FormulaR1C1 = "1001" Range("M1").Select Selection.AutoFill Destination:=Range("M2:M13"), Type:=xlFillSeries Range("M1:M13").Select Hope this is more of Help? Of to lunch now will be back in an hour "DaveO" wrote: 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 |
Range Value ie: (A1:A100) The Option of?
I am sorry Rob, I'm just failing to understand here. It's my fault I think as
I need to see things before I can really get to grips with them. As to copy and paste, unless you copying hundred of rows and columns of complex data types, you should be fine. Memory is utilised better now-a-days and a simple copy of maybe 200 rows is nothing in the grand scheme of things! If you can attach a copy of you spreadsheet I may be able to understand more, but will understand if the data is sensitive and so you would be unable to. HTH. "Rob" wrote: I'm a little lost now, where does the value range come in? I am beginning to think maybe to use a list box? As the when the Value Range is entered, this would entered into a cell, outside the working (pasting or instalation of terminal) range. Then maybe using a MsgBox Range("N1").Value or cell reference then using one of these codes, to install therminal ranges required in Column "F" & "M". I had thought off this a few days ago but was trying to avoid cell function into the worksheet. It just occured to me, where the confusion might be. In column N (contains Counta Formula) which counts the number of terminals within that work sheet, which could be incorrect. So a query "Is this a correct number of terminals?" If it "True" is then simply Copy from old worksheet & paste into new worksheet paste followed by installation of the terminal block in Column "m" (currently doesn't know how many terminals to install). If it's false somehow is to add correct number of terminal, just a thought something like Range value ("N") + Range value ("O") = total value. With this info the system then goes to new wooksheet & installs teriminal into "F & M" prior to paste from old worksheet. The comparison mentioned earlier, is if old worksheet is supposed to have 3 terminal for instant, but terminals avaliable are 1 & 3 terminal 2 is missing, but is avaliable in the new worksheet, so terminals 1 & 3 are pasted to it's respective place Is pasting a good way? As in the past have had crashed the system, used too much memory space, does saving worksheet at intervals clear memory space? The function codes you've sent me, am I To just copy & paste into a sub macro? As it comes up with compile error or sub function not defined? Sorry about this, hope not to be to much bother. "DaveO" wrote: OK, so taking your code, and assuming that column N holds data for all of the rows we're interested in... (If not column 'N' then replace it with a column letter that does.) --------------------------------------- dim intCounter as integer do while len(range("N" & intCounter).Text) < 0 intCounter = intCounter + 1 loop intCounter = intCounter - 1 Range("M1").select selection.copy Range("M2:M" & intCounter).select selection.paste --------------------------------------- Does this help? "Rob" wrote: Sorry didn't make myself understood, Below is an example which would also be applied to column "F" with a starting value of 1, notice I've used "xlFillSeries & the value range is 13 but could go to 200" not sure if it's best to be done by loop system. Anyway this information is then stored onto a blank worksheet. next step would be to goto worksheet that contains the terminals that has all the other info. such as cables etc. From here I would compare the 2 worksheet (row by row) if first value in column "F" equals the same the blank worksheet then to copy that row into the blank worksheet. My first macro didn't do it this way, at the time didn't have the loop system knowlege. It was done in this format "If Range E1< "1" then Row ("1:1).select Select.Insert Shift=xlDown, then entered the value range into E1 etc. this had to generate hundreds of macros to make this work ie per row. Range("M1").Select ActiveCell.FormulaR1C1 = "1001" Range("M1").Select Selection.AutoFill Destination:=Range("M2:M13"), Type:=xlFillSeries Range("M1:M13").Select Hope this is more of Help? Of to lunch now will be back in an hour "DaveO" wrote: 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 |
Range Value ie: (A1:A100) The Option of?
No problem,
Only problem is attaching the zip.file to this system, do you have an email address? "DaveO" wrote: I am sorry Rob, I'm just failing to understand here. It's my fault I think as I need to see things before I can really get to grips with them. As to copy and paste, unless you copying hundred of rows and columns of complex data types, you should be fine. Memory is utilised better now-a-days and a simple copy of maybe 200 rows is nothing in the grand scheme of things! If you can attach a copy of you spreadsheet I may be able to understand more, but will understand if the data is sensitive and so you would be unable to. HTH. "Rob" wrote: I'm a little lost now, where does the value range come in? I am beginning to think maybe to use a list box? As the when the Value Range is entered, this would entered into a cell, outside the working (pasting or instalation of terminal) range. Then maybe using a MsgBox Range("N1").Value or cell reference then using one of these codes, to install therminal ranges required in Column "F" & "M". I had thought off this a few days ago but was trying to avoid cell function into the worksheet. It just occured to me, where the confusion might be. In column N (contains Counta Formula) which counts the number of terminals within that work sheet, which could be incorrect. So a query "Is this a correct number of terminals?" If it "True" is then simply Copy from old worksheet & paste into new worksheet paste followed by installation of the terminal block in Column "m" (currently doesn't know how many terminals to install). If it's false somehow is to add correct number of terminal, just a thought something like Range value ("N") + Range value ("O") = total value. With this info the system then goes to new wooksheet & installs teriminal into "F & M" prior to paste from old worksheet. The comparison mentioned earlier, is if old worksheet is supposed to have 3 terminal for instant, but terminals avaliable are 1 & 3 terminal 2 is missing, but is avaliable in the new worksheet, so terminals 1 & 3 are pasted to it's respective place Is pasting a good way? As in the past have had crashed the system, used too much memory space, does saving worksheet at intervals clear memory space? The function codes you've sent me, am I To just copy & paste into a sub macro? As it comes up with compile error or sub function not defined? Sorry about this, hope not to be to much bother. "DaveO" wrote: OK, so taking your code, and assuming that column N holds data for all of the rows we're interested in... (If not column 'N' then replace it with a column letter that does.) --------------------------------------- dim intCounter as integer do while len(range("N" & intCounter).Text) < 0 intCounter = intCounter + 1 loop intCounter = intCounter - 1 Range("M1").select selection.copy Range("M2:M" & intCounter).select selection.paste --------------------------------------- Does this help? "Rob" wrote: Sorry didn't make myself understood, Below is an example which would also be applied to column "F" with a starting value of 1, notice I've used "xlFillSeries & the value range is 13 but could go to 200" not sure if it's best to be done by loop system. Anyway this information is then stored onto a blank worksheet. next step would be to goto worksheet that contains the terminals that has all the other info. such as cables etc. From here I would compare the 2 worksheet (row by row) if first value in column "F" equals the same the blank worksheet then to copy that row into the blank worksheet. My first macro didn't do it this way, at the time didn't have the loop system knowlege. It was done in this format "If Range E1< "1" then Row ("1:1).select Select.Insert Shift=xlDown, then entered the value range into E1 etc. this had to generate hundreds of macros to make this work ie per row. Range("M1").Select ActiveCell.FormulaR1C1 = "1001" Range("M1").Select Selection.AutoFill Destination:=Range("M2:M13"), Type:=xlFillSeries Range("M1:M13").Select Hope this is more of Help? Of to lunch now will be back in an hour "DaveO" wrote: 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 |
Range Value ie: (A1:A100) The Option of?
|
Range Value ie: (A1:A100) The Option of?
|
Range Value ie: (A1:A100) The Option of?
|
Range Value ie: (A1:A100) The Option of?
|
Range Value ie: (A1:A100) The Option of?
I'm just curious. Is there any reason why you guys are quoting your entire conversation each time you reply to each other? -- Daminc ------------------------------------------------------------------------ Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074 View this thread: http://www.excelforum.com/showthread...hreadid=465596 |
| All times are GMT +1. The time now is 10:40 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com