Hi,

I'm currently trying to teach myself VBA. I may have set myself a rather
ambitious problem, and I'm wondering if anyone can help. There are a few
queries. All the queries center around the same report that is generated by
another program - the locations of headers can change column each time the
report is generated, and the number of rows change each time.

A simplistic view of the report is as follows:

A B C
1.Origin Destination %Margin
2. ABC DEF 5
3. GHI JKL 10
4. MNO PQR 15

1: Range function
The first thing I want to do is put in a formula in the column to the right
of the %Margin column, whereever that happens to be. I've written code
which finds the cell C1 (in this case), and increments the row and column by
one, activates that cell (here - D2). I then do

varStart= Active.Cell
ActiveCell.FormulaR1C1 = //the required formula
'Find the last cell in that column/sheet:
Set varLast = Range("D2").SpecialCells(xlCellTypeLastCell)
lLastRow = varLast.Row
lLastCol = varLast.Column
'Fill the formula down to the end
Selection.AutoFill Destination:=Range("D2:D4"), Type:=xlFillDefault

I'm sure there must be some way to replace the references D2 and D2:D4 in
the code above with references to varStart and varLast that I have already
found, but I can't get it to work. Through research it appears that the
Range function only accepts a string as an argument - I tried converting
varStart and varLast to strings, but just don't seem to get it right. Can
anyone give me a hand with this?

2. Deleting rows based on multiple criteria
The second problem is slightly more complicated (I think). Imagine the 3
letter codes above are airport codes. I have an array of these codes

varCodes = Array("ABC", "DEF", "GHI", "MNO", "PQR")

What I want to do is delete any line that doesn't have BOTH origin AND
destination as one of the codes in the array. In this case I would want
line 3 to be deleted as JKL is not in the array. My plan was to find the
Origin column, run through that, and set a FALSE flag if it came across any
row that had a code not in my array, then run through the Destination column
doing the same thing, then finally delete any row that had a false flag.

This is the snippet I tried:

For lOriginRow = 1 To 100
//(I only used 100 here because I haven't managed to prob 1 working)
For i = 1 To 5 //the number of items in the array
If varCodes(i) = ActiveCell.Value Then
Cells(lOriginRow, 13) = "True"
End If
Next i
Next lOriginRow

The problem here seems to be with varCodes(i)=ActiveCell.Value - am I
actually incrementing the row each time?

Also Cells(lOriginRow, 13) doesn't seem to be the right way to reference it.

Any help would be greatly appreciated. If anyone would like me to send them
the sample spreadsheet with code to date, please mail me offlist and I'll
send it on.

Regards, and thanks in advance,
Fiona