How to select and copy desire range

[Gaurao]

Hi Friends,

I would like to find a cell having [ specific text(this is fixed) + some numeric value (variable)] till

[ specific text(this is fixed) + some numeric value (variable)] and then copy past it to column I.

Book02.xlsx

eg. I want a RANGE of a cells in column A which has data XYZ 4.65 till ABC 78.65. The words XYZ and ABC will be fixed always however numeric value will be changing "ALONG WITH cell reference number".

I want this range to be copied in column I.

Can some one provide me the macro which can perform this operation?

Regards,

[Mumps1]

Try the following macro. Please note that there must be a space between the XYZ and the numeric value and between the ABC and the numeric value.

Please Login or Register  to view this content.

[Gaurao]

Thank you Mumps1 for reply however I dont want input box as i have to run this macro for many different values.

Also when i tried to run this its showing error.

.Range(.Cells.Find("XYZ " & firstVal), .Cells.Find("ABC " & secondVal)).Copy .Range("I1")

Please help.

Try the following macro. Please note that there must be a space between the XYZ and the numeric value and between the ABC and the numeric value.

Please Login or Register  to view this content.

[Mumps1]

Hi Gauraco. When I tried it on a dummy sheet it worked for me. You have to make sure that there is only one space between the XYZ and the numeric value and between the ABC and the numeric value and you must replace the XYZ and ABC with the actual string you are searching for. How do you determine which numeric value you want to search for?

[Gaurao]

Ohh I just realized that my data is : Monthly charges $45.76 and next value is Usage charge $33.00 which has spaces.
This might be creating issue. Can it be possible if it is work for data having spaces?

Regards.

Hi Gauraco. When I tried it on a dummy sheet it worked for me. You have to make sure that there is only one space between the XYZ and the numeric value and between the ABC and the numeric value and you must replace the XYZ and ABC with the actual string you are searching for. How do you determine which numeric value you want to search for?

[Mumps1]

I am assuming that the first specific text is always going to be "Monthly charges" and the second specific text is always going to be "Usage charges".. Is this correct? Also, if I assume correctly that the dollar amount for each charge will be constantly changing, I need to know how you determine what that charge will be each time you do a search. ?????

[Gaurao]

Yeah...
You are absolutely correct. "Monthly charges" and "Usage charges" will always remain only the $ amount will be changing.

Answer to your 2 question is "Monthly charges" and "Usage charges" are the single entry in column. So I want a data to be copied between this range.

Thanks.

[Mumps1]

Again, I ask: How do you determine which amount you are searching for? Without the answer to this question, it's impossible to go ahead.

[Gaurao]

Apologize.

Answer to your 2 question is "Monthly charges" and "Usage charges" are the single entry in column. So I want a data to be copied between this range.

Thanks.

Again, I ask: How do you determine which amount you are searching for? Without the answer to this question, it's impossible to go ahead.

[Mumps1]

OK, now I understand. Try:

Please Login or Register  to view this content.

[jaslake]

Hi Gaurao

If you put a $ in this

Please Login or Register  to view this content.

such as you have in this

Please Login or Register  to view this content.

one of us MAY be able to make this work.

[Gaurao]

Hi Mumps1,

I tried this code however there are two issues. your above code is working fine however I want to copy a row which has "Monthly charges (+ numeric value). There are many sentence which are having "Monthly charges" word however I want a cell which has "Monthly charges with any numeric value. This was my original question.

Secondly its not running or fetching data in sequence. My expectation is it should 1st search "Monthly charges" and then "Usage charges"

Can it be done?

OK, now I understand. Try:

Please Login or Register  to view this content.

[Gaurao]

Hi Gaurao

If you put a $ in this

Please Login or Register  to view this content.

such as you have in this

Please Login or Register  to view this content.

one of us MAY be able to make this work.

Hi,

I cant add $ sign as im fetching data from somewhere else and I have to work on it without manipulating it.

[Gaurao]

Hi Mumps1,

I will handle numeric problem. I want your help on this

Secondly its not running or fetching data in sequence. My expectation is it should 1st search "Monthly charges" and then "Usage charges"

Can it be done?

Regards,

[Mumps1]

I'm not sure what you mean by "in sequence". You wanted to copy a range between "Monthly charges" and "Usage charges". The code does that. It might be easier to help if you could post a copy of your actual file with a detailed explanation of what you want to do using specific references to cells and/or ranges. If your file contains confidential information, you could replace it with generic data.

[Gaurao]

Book11.xlsxHi Mumps1,

Please see attached file. I want to copy highlighted part of excel.

I'm not sure what you mean by "in sequence". You wanted to copy a range between "Monthly charges" and "Usage charges". The code does that. It might be easier to help if you could post a copy of your actual file with a detailed explanation of what you want to do using specific references to cells and/or ranges. If your file contains confidential information, you could replace it with generic data.

[Mumps1]

Hi Gaurao. I had a look at your file but it is still not clear. In the comment you placed on the sheet, you say you want to

copy a cell having NUMBER, Total amount due, Previous Balance…..and many more things

Does this mean that you want to copy only row 7, row 14 and row 20? What happened to the range you said you wanted to copy? What are "many more things"? You also say that you want to do the same for all the sheets in the workbook. Do you want to copy to the same sheet each time or copy to a different sheet? I think that the best way to get a clear understanding of what you want is to create a sample file that is as close as possible to your actual file and post it here. Your sample file should include at least two "Before" sheets that show what your data looks like now and two "After" sheets that show what your data will look like after the macro is run. I'm sorry for all these questions, but unless there is a very clear understanding of what you want done, it is very difficult to write a macro that will do what you want.

[Gaurao]

Hi,

Thanks for showing interest. I'm OK with your many questions. I'll be glad to answer it.

I want to copy all the data from row 7 till row 20. I want to copy this data and past it to any of the column of the same sheet. Later I'll run this macro for all the sheets in a workbook.

I know its bit difficult for you to understand it as my data is confidential so I'm not able to share it.

I hope this is clear. If not please let me know I'll be happy to answer it.

Regards.

[Mumps1]

Hi Gaurao. Below you will see two macros. The first will work on the active sheet only. The second will work on all the sheets in the workbook since you mentioned that is what you want to do. Both macros assume that you will always be searching in column A for the range between the strings "NUMBER" and "Previous balance". If these search criteria are not always the same, then the code will have to be modified. Please let me know if this works for you.

Please Login or Register  to view this content.

[Gaurao]

Hi Mumps1,

Thank you. Its working fine now. However Can we modify above code when there is no "Previous balance" exist then it should simply exit instead of giving error.

I ran this macro and got error when my one of the sheet was not having word "Previous balance". Can we eliminate this error?

Regards,
Gaurao

Hi Gaurao. Below you will see two macros. The first will work on the active sheet only. The second will work on all the sheets in the workbook since you mentioned that is what you want to do. Both macros assume that you will always be searching in column A for the range between the strings "NUMBER" and "Previous balance". If these search criteria are not always the same, then the code will have to be modified. Please let me know if this works for you.

Please Login or Register  to view this content.

[Mumps1]

Try:

Please Login or Register  to view this content.

[Gaurao]

Hi Mumps1,

I really THANK YOU VERY MUCH for your help in this. The macro is working nice.

Have a nice day ahead.

Regards,
Gaurao

Try:

Please Login or Register  to view this content.

[Mumps1]

My pleasure.