Here is the code that I use in my macro to do what you are describing.
As you mention my data changes day to day (number of rows) but my code finds the last row and I use that as a variable to select my range copy and paste. My data starts at J5 thru L5 before running the macro. This is not a complete subroutine just the points you need.
Dim LastRow As Long
' Here are the formulas
Range("J5").FormulaR1C1 = _
"=VLOOKUP(RC[-9],'[file.xls]Sheet1'!C1:C2,2,FALSE)"
Range("K5").FormulaR1C1 = _
"=VLOOKUP(RC[-10],'[file.xls]Sheet1'!C1:C3,3,FALSE)"
Range("L5").FormulaR1C1 = _
"=VLOOKUP(RC[-11],'[file.xls]Sheet1'!C1:C11,11,FALSE)"
Range("J5").Select
' Here is the last row and then using that to autofill the formula down to the last row
LastRow = Worksheets("Sheet1").Range("A5").End(xlDown).Row
With Worksheets("Sheet1").Range("J5:L5")
.AutoFill Destination:=Range("J5:L" & LastRow&)
End With
' This next part copies and pastes the formulas into values
Range("J5:L" & LastRow&).Select
For Each xCell In Selection
If Not IsError(xCell.Value) Then xCell.Value = CDec(xCell.Value)
Next xCell
Hope that helps!
--- In ExcelVBA@yahoogroups.com, Dave 'Doc' Corio <dcorio@...> wrote:
>
> Hi, Dave. Determining the row is part of what I would like to do.
> Rows of data are added from the database this spreadsheet is based on.
> Some days there may be only 2 or 3 rows added, other days it might be 20
> or 30. In other words, no set standard. Other sheets read the data in
> this sheet and produce information based on it.
>
> What I hope to do, is to be able to simply hit a macro key and have
> the program copy the formulas in columns A and B to the last row of data
> (data starts in column D). BEFORE would look like
> A B C D
> 5000 IF E5000.... If G5000.... EMPTY DATA
> 5001 DATA
> 5002 DATA
> 5003 DATA
>
> AFTER would have the formulas from A5000:B5000 copied to 5001, 5002, and
> 5003
>
> Thanks
> Doc
>
>
> On 4/16/2012 6:30 PM, david.smart@... wrote:
> >
> > > ... but couldn't you determine the last row used by ...
> >
> > Yes, I think so.
> >
> > In this case, I think there is a group of new rows added by some other
> > means, but a couple of columns are not being filled. So it might be a
> > case of finding the bottom row and also finding the bottom extra
> > formula. The difference between these would be the new rows that need
> > formulas.
> >
> > Alternatively, just find the bottom row and then apply the additional
> > formulas to every row.
> >
> >
> > Regards, Dave S
> >
> > ________________________________
> >
> > From: ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com>
> > [mailto:ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com>] On
> > Behalf Of Dwight Pearson
> > Sent: Monday, 16 April 2012 08:52
> > To: ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com>
> > Subject: Re: [ExcelVBA] Request for Guidance
> >
> > I'm no expert and am still using Excel 2003 but couldn't you determine
> > the last row used by
> >
> > LastRow = Range("D65536").End(xlUp).Row
> >
> > Dwight Pearson
> > --- On Sun, 4/15/12, Dave 'Doc' Corio <dcorio@...
> > <mailto:dcorio%40zitomedia.net>
> > <mailto:dcorio%40zitomedia.net> > wrote:
> >
> > From: Dave 'Doc' Corio <dcorio@...
> > <mailto:dcorio%40zitomedia.net>
> > <mailto:dcorio%40zitomedia.net> >
> > Subject: Re: [ExcelVBA] Request for Guidance
> > To: ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com>
> > <mailto:ExcelVBA%40yahoogroups.com>
> > Date: Sunday, April 15, 2012, 10:44 AM
> >
> > Well, I'm ready to admit that I don't know NEAR as much as I
> > thought I did! A few years ago, this would (might) have been fairly easy
> >
> > - but even given good directions, I can't seem to get this concept to
> > work.
> >
> > David, you're absolutely right. I don't really care what is the
> > last row of data, as long as I can copy the formulas in Ax:Bx all the
> > way to the end. To save processing time, I'd be happy copying from
> > somewhere near the end (i.e. A5500:B5500) to the last row.
> >
> > Sorry if I led folks to believe that I actually have a clue lol!!
> >
> > Thanks
> > Doc
> >
> > On 4/15/2012 10:31 AM, David Lanman wrote:
> > >
> > > Hi Doc,
> > >
> > > I am not sure you really need to know what has been added, but you do
> > > need to know what the last "Data" entry row is. Knowing the last row
> > > you can copy from A2:B2 down to the last row, overwriting the existing
> >
> > > formulas would not matter, I assume.
> > > Range(A2:B2).copy
> > > Range Paste (A & LastRow: B & LastRow)
> > >
> > > Would that work? You can find last row GoTo Special LastCell.
> > >
> > > Thanks,
> > > David
> > >
> > >
> > > ________________________________
> > > From: Doc <dcorio@... <mailto:dcorio%40zitomedia.net>
> > <mailto:dcorio%40zitomedia.net>
> > <mailto:dcorio%40zitomedia.net>>
> > > To: ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com>
> > <mailto:ExcelVBA%40yahoogroups.com>
> > <mailto:ExcelVBA%40yahoogroups.com>
> > > Sent: Saturday, April 14, 2012 1:58 PM
> > > Subject: [ExcelVBA] Request for Guidance
> > >
> > >
> > >
> > >
> > > Greetings to the group from a new member! Name is Doc in north-central
> > PA.
> > >
> > > It's been a long time and several versions since I worked with VBA,
> > > and I'm finding myself a little lost. Okay - a LOT lost!
> > >
> > > I don't want someone to just write this for me - I would like a little
> >
> > > help steering in the right direction.
> > >
> > > In Excel 2007 I have a data sheet consisting of several thousand lines
> >
> > > of data from a database. The first two columns (A & B) are formulas
> > > that determine items from the rows of data. Column C is empty. When
> > > the data is refreshed, there may be anywhere from 1 to 25 new rows of
> > > data added.
> > >
> > > The VBA I've been trying (and failing) to work would do the following:
> > >
> > > Determine how many new rows of data were added (data starts in column
> > D)
> > > and
> > > copy the formulas from the last existing cells A & B to the new rows.
> > >
> > > Very much appreciate any help with this
> > > Thanks!
> > > Doc
> > >
> > > [Non-text portions of this message have been removed]
> > >
> > >
> >
> > [Non-text portions of this message have been removed]
> >
> > [Non-text portions of this message have been removed]
> >
> > Visit our website at http://www.ubs.com
> >
> > This message contains confidential information and is intended only
> > for the individual named. If you are not the named addressee you
> > should not disseminate, distribute or copy this e-mail. Please
> > notify the sender immediately by e-mail if you have received this
> > e-mail by mistake and delete this e-mail from your system.
> >
> > E-mails are not encrypted and cannot be guaranteed to be secure or
> > error-free as information could be intercepted, corrupted, lost,
> > destroyed, arrive late or incomplete, or contain viruses. The sender
> > therefore does not accept liability for any errors or omissions in the
> > contents of this message which arise as a result of e-mail transmission.
> > If verification is required please request a hard-copy version. This
> > message is provided for informational purposes and should not be
> > construed as a solicitation or offer to buy or sell any securities
> > or related financial instruments.
> >
> > UBS reserves the right to retain all messages. Messages are protected
> > and accessed only in legally justified cases.
> >
> >
>
>
> [Non-text portions of this message have been removed]
>
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar