MACRO's

[arangoa79]

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

 

[Don Guillett]

As ALWAYS, post your code and details for comments and suggestions.

 

[Dave Peterson]

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.

 

[Gord Dibben]

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