Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A seemingly simple problem for VBA
A seemingly simple problem for VBA
Hi, I am trying to solve the following problem using Excel VBA. I have two EXCEL worksheets. Each sheet contains data of the following type. Account number, amounts for Jan, Feb, March etc There are about 100 accounts on each worksheet. There are about 20 accounts that are common to both worksheets. I have identified which those account numbers are. Now I need to separate the data (rows) for these account numbers from each sheet and put it on a third sheet. So, I need to extract about 20 rows from each sheet and put them on a third sheet, based on the account numbers I have identified. I need to be able to do this repeatedly, when data and account numbers keep changing. I have figured out a way to identify these common account numbers. Now I need to figure out how to extract them from each sheet using VBA. If this was a database problem, I could simply use an SQL SELECT statement. But I am not so good with VBA. Thanks to all who respond. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A seemingly simple problem for VBA
If this was a database problem, I could simply use an SQL SELECT statement
Well, you can run SQL on sheet ranges! Do a Google and you will be up and running quickly. Bear in mind that it is best to close the workbook before running the SQL as otherwise there can be a memory leak. RBS "amitexcel" wrote in message ... A seemingly simple problem for VBA Hi, I am trying to solve the following problem using Excel VBA. I have two EXCEL worksheets. Each sheet contains data of the following type. Account number, amounts for Jan, Feb, March etc There are about 100 accounts on each worksheet. There are about 20 accounts that are common to both worksheets. I have identified which those account numbers are. Now I need to separate the data (rows) for these account numbers from each sheet and put it on a third sheet. So, I need to extract about 20 rows from each sheet and put them on a third sheet, based on the account numbers I have identified. I need to be able to do this repeatedly, when data and account numbers keep changing. I have figured out a way to identify these common account numbers. Now I need to figure out how to extract them from each sheet using VBA. If this was a database problem, I could simply use an SQL SELECT statement. But I am not so good with VBA. Thanks to all who respond. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
A seemingly simple problem for VBA
I seem to have figured it out, though the solution is a bit complicated. Any
simple suggestions would be welcome. "amitexcel" wrote: A seemingly simple problem for VBA Hi, I am trying to solve the following problem using Excel VBA. I have two EXCEL worksheets. Each sheet contains data of the following type. Account number, amounts for Jan, Feb, March etc There are about 100 accounts on each worksheet. There are about 20 accounts that are common to both worksheets. I have identified which those account numbers are. Now I need to separate the data (rows) for these account numbers from each sheet and put it on a third sheet. So, I need to extract about 20 rows from each sheet and put them on a third sheet, based on the account numbers I have identified. I need to be able to do this repeatedly, when data and account numbers keep changing. I have figured out a way to identify these common account numbers. Now I need to figure out how to extract them from each sheet using VBA. If this was a database problem, I could simply use an SQL SELECT statement. But I am not so good with VBA. Thanks to all who respond. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
A seemingly simple problem for VBA
On Apr 20, 2:48*am, amitexcel
wrote: I seem to have figured it out, though the solution is a bit complicated. Any simple suggestions would be welcome. "amitexcel" wrote: A seemingly simple problem for VBA Hi, I am trying to solve the following problem using Excel VBA. I have two EXCEL worksheets. Each sheet contains data of the following type. Account number, amounts for Jan, Feb, March etc There are about 100 accounts on each worksheet. There are about 20 accounts that are common to both worksheets. I have identified which those account numbers are. Now I need to separate the data (rows) for these account numbers from each sheet and put it on a third sheet. So, I need to extract about 20 rows from each sheet and put them on a third sheet, based on the account numbers I have identified. I need to be able to do this repeatedly, when data and account numbers keep changing. I have figured out a way to identify these common account numbers. Now I need to figure out how to extract them from each sheet using VBA. If this was a database problem, I could simply use an SQL SELECT statement. But I am not so good with VBA. Thanks to all who respond.- Hide quoted text - - Show quoted text - If you have identified the acct nos then you can use that values with Find method of range. Suppose the Acct no is 102 then the follwing statement can copy the row from a sheet Activesheet.usedrange.find(what:="102").entirerow. copy If you can attach the excel file it will be nice for me |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
A seemingly simple problem for VBA
Unfortunately, that solution does not work. I will not be the user of this
tool. I am building it for someone else. And they will not be able to run SQL. Thanks for responding though. "RB Smissaert" wrote: If this was a database problem, I could simply use an SQL SELECT statement Well, you can run SQL on sheet ranges! Do a Google and you will be up and running quickly. Bear in mind that it is best to close the workbook before running the SQL as otherwise there can be a memory leak. RBS "amitexcel" wrote in message ... A seemingly simple problem for VBA Hi, I am trying to solve the following problem using Excel VBA. I have two EXCEL worksheets. Each sheet contains data of the following type. Account number, amounts for Jan, Feb, March etc There are about 100 accounts on each worksheet. There are about 20 accounts that are common to both worksheets. I have identified which those account numbers are. Now I need to separate the data (rows) for these account numbers from each sheet and put it on a third sheet. So, I need to extract about 20 rows from each sheet and put them on a third sheet, based on the account numbers I have identified. I need to be able to do this repeatedly, when data and account numbers keep changing. I have figured out a way to identify these common account numbers. Now I need to figure out how to extract them from each sheet using VBA. If this was a database problem, I could simply use an SQL SELECT statement. But I am not so good with VBA. Thanks to all who respond. . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
A seemingly simple problem for VBA
Hi,
I do not want to have to type in each account number. But I have figured out a way. I will post my solution soon after I finish. BTW, I am not sure how I could upload a spreadsheet. "Javed" wrote: On Apr 20, 2:48 am, amitexcel wrote: I seem to have figured it out, though the solution is a bit complicated. Any simple suggestions would be welcome. "amitexcel" wrote: A seemingly simple problem for VBA Hi, I am trying to solve the following problem using Excel VBA. I have two EXCEL worksheets. Each sheet contains data of the following type. Account number, amounts for Jan, Feb, March etc There are about 100 accounts on each worksheet. There are about 20 accounts that are common to both worksheets. I have identified which those account numbers are. Now I need to separate the data (rows) for these account numbers from each sheet and put it on a third sheet. So, I need to extract about 20 rows from each sheet and put them on a third sheet, based on the account numbers I have identified. I need to be able to do this repeatedly, when data and account numbers keep changing. I have figured out a way to identify these common account numbers. Now I need to figure out how to extract them from each sheet using VBA. If this was a database problem, I could simply use an SQL SELECT statement. But I am not so good with VBA. Thanks to all who respond.- Hide quoted text - - Show quoted text - If you have identified the acct nos then you can use that values with Find method of range. Suppose the Acct no is 102 then the follwing statement can copy the row from a sheet Activesheet.usedrange.find(what:="102").entirerow. copy If you can attach the excel file it will be nice for me . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
A seemingly simple problem for VBA
And they will not be able to run SQL
Just give them an add-in that will do that for them. Maybe via a simple wizard. RBS "amitexcel" wrote in message ... Unfortunately, that solution does not work. I will not be the user of this tool. I am building it for someone else. And they will not be able to run SQL. Thanks for responding though. "RB Smissaert" wrote: If this was a database problem, I could simply use an SQL SELECT statement Well, you can run SQL on sheet ranges! Do a Google and you will be up and running quickly. Bear in mind that it is best to close the workbook before running the SQL as otherwise there can be a memory leak. RBS "amitexcel" wrote in message ... A seemingly simple problem for VBA Hi, I am trying to solve the following problem using Excel VBA. I have two EXCEL worksheets. Each sheet contains data of the following type. Account number, amounts for Jan, Feb, March etc There are about 100 accounts on each worksheet. There are about 20 accounts that are common to both worksheets. I have identified which those account numbers are. Now I need to separate the data (rows) for these account numbers from each sheet and put it on a third sheet. So, I need to extract about 20 rows from each sheet and put them on a third sheet, based on the account numbers I have identified. I need to be able to do this repeatedly, when data and account numbers keep changing. I have figured out a way to identify these common account numbers. Now I need to figure out how to extract them from each sheet using VBA. If this was a database problem, I could simply use an SQL SELECT statement. But I am not so good with VBA. Thanks to all who respond. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple problem, simple formula, no FUNCTION ! | Excel Worksheet Functions | |||
Seemingly Simple Problem | Excel Discussion (Misc queries) | |||
Seemingly random .tmp files | New Users to Excel | |||
The Seemingly Impossible | Excel Worksheet Functions | |||
Begineer with (seemingly) hard problem | Excel Discussion (Misc queries) |