If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
MACRO's
I work with imported data that varies in size. In order to prepare the data
for analysis I take the same repetitive steps. I know using a macro would make it simpler. I have a question regarding the macro as I record it. I always copy a vlookup formula down a column to the last row containing data. If when recording I stop at line 300 is that where the macro will stop every time I run it? The amount of data I work with changes in size every week. If the current data source has lets say 400 lines will the macro stop at 300, which is where it stopped when I recorded it? If so how can I ensure it goes all the way to the last line? In this same scenario, once the vlookup is done I copy that same column and past special values. I then sort by that column in order to isolate the #N/A. I then apply a different vlookup to all the rows after the first #N/A. How does the macro know to start at a different row then when it was recorded. Since the data will change after sorting the first #N/A will most likely start in a different row. Not sure if all this makes, maybe I need to consult with a excel wiz but I would like to solve this without asking for help from my peers. Thanks |
#2
|
|||
|
|||
MACRO's
|
#3
|
|||
|
|||
MACRO's
Can you pick out a column that always has data in it when that row is used?
If yes, then you can use that to determine how far down to fill your =vlookup() formulas. dim LastRow as long With activesheet lastrow = .cells(.rows.count,"A").end(xlup).row 'use the formula you want here, but write it so it refers to the 'the top cell of the range (I used E1, so my formula used A1). .range("E1:E" & lastrow).formula = "=vlookup(a1,sheet2!a:b,2,false)" End with Then you can sort your data by whatever column you want and look for the #N/A. Dim FirstNACell As Range with activesheet Set FirstNACell = Nothing On Error Resume Next Set FirstNACell _ = .Range("e1").EntireColumn.Cells _ .SpecialCells(xlCellTypeFormulas, xlErrors).Cells(1) On Error GoTo 0 If FirstNACell Is Nothing Then 'no errors found Else .Range("e" & FirstNACell.Row & ":e" & lastrow).Formula = "=whatever" end if end with Sometimes, it's much easier to write the formula in R1C1 reference style. Then you can use: ..formular1c1 = "=vlookup(rc1,sheet2!c1:c2,2,false)" rc1 means same row as the formula, but in column 1. And C1:c2 in R1C1 reference style means column1 to column 2 (A:B). ==== Untested, uncompiled. Watch for typos. arangoa79 wrote: I work with imported data that varies in size. In order to prepare the data for analysis I take the same repetitive steps. I know using a macro would make it simpler. I have a question regarding the macro as I record it. I always copy a vlookup formula down a column to the last row containing data. If when recording I stop at line 300 is that where the macro will stop every time I run it? The amount of data I work with changes in size every week. If the current data source has lets say 400 lines will the macro stop at 300, which is where it stopped when I recorded it? If so how can I ensure it goes all the way to the last line? In this same scenario, once the vlookup is done I copy that same column and past special values. I then sort by that column in order to isolate the #N/A. I then apply a different vlookup to all the rows after the first #N/A. How does the macro know to start at a different row then when it was recorded. Since the data will change after sorting the first #N/A will most likely start in a different row. Not sure if all this makes, maybe I need to consult with a excel wiz but I would like to solve this without asking for help from my peers. Thanks -- Dave Peterson |
#4
|
|||
|
|||
MACRO's
Adjust column to suit.
Will fill down from C1 to last row in column C Sub Auto_Fill() Dim Lrow As Long With ActiveSheet Lrow = .Range("C" & Rows.Count).End(xlUp).Row .Range("C1:C" & Lrow).FillDown End With End Sub Not sure what or why you are sorting for #N/A and why you are changing the VLOOKUP formula at that point. Gord Dibben MS Excel MVP On Tue, 19 May 2009 09:00:02 -0700, arangoa79 wrote: I work with imported data that varies in size. In order to prepare the data for analysis I take the same repetitive steps. I know using a macro would make it simpler. I have a question regarding the macro as I record it. I always copy a vlookup formula down a column to the last row containing data. If when recording I stop at line 300 is that where the macro will stop every time I run it? The amount of data I work with changes in size every week. If the current data source has lets say 400 lines will the macro stop at 300, which is where it stopped when I recorded it? If so how can I ensure it goes all the way to the last line? In this same scenario, once the vlookup is done I copy that same column and past special values. I then sort by that column in order to isolate the #N/A. I then apply a different vlookup to all the rows after the first #N/A. How does the macro know to start at a different row then when it was recorded. Since the data will change after sorting the first #N/A will most likely start in a different row. Not sure if all this makes, maybe I need to consult with a excel wiz but I would like to solve this without asking for help from my peers. Thanks |
Thread Tools | |
Display Modes | |
|
|