A seemingly simple problem for VBA
in [Programming]
|
Prev: macro stopping
Next: Adding code
From: amitexcel on 20 Apr 2010 12:38 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 <amitex...(a)discussions.microsoft.com> > 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 > > > > . >
From: RB Smissaert on 20 Apr 2010 13:36
> 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" <amitexcel(a)discussions.microsoft.com> wrote in message news:53CE2F83-3905-48F8-9B9A-288CD68BF8D0(a)microsoft.com... > 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" <amitexcel(a)discussions.microsoft.com> wrote in message >> news:D8EC2960-9600-441B-9EEB-B8857170BD8C(a)microsoft.com... >> >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. >> > >> >> . >> |