Hi Dave,
Thanks for that. It solves problem 1 perfectly - from examining your code
it seems that I was using Cells instead of .Cells - I'm still getting to
grips with
VB/A syntax :s
For problem 2 (deleting the rows) your code works great provided I know that
Origin and Destination are definitely in columns A and B
To cater for the case where Origin and Desination may be in other columns I
defined OriginCell to be
Set OriginCell = .Cells.Find(what:="Origin", _
after:=.Cells(.Cells.Count), LookIn:=xlValues, _
lookat:=xlWhole, searchorder:=xlNext, _
searchdirection:=xlByColumns, MatchCase:=False)
and then further down I used
If IsError(Application.Match(.Cells(iRow,
OriginCell.Column).Value, _
varCodes, 0)) _
(i.e. I switched "A" for OriginCell.Column) and that didn't work. But then
I defined
Dim OriginColIndex as Long
and then put
OriginColIndex = OriginCell.Column
and then changed it to
If IsError(Application.Match(.Cells(iRow, OriginColIndex).Value, _
varCodes, 0)) _
it all worked fine. I'm guessing that the range datatype wasn't accepted by
the Cells() property(object?), and that I managed to force a conversion by
declaring OriginColIndex as Long.
Can anyone tell me if that's what's actually happening, or if I just got it
right by fluke? It would be nice to understand what I'm doing so that I can
use the knowledge in future, rather than just playing with things until they
work :)
Thanks again for your help Dave,
Regards,
Fiona
"Dave Peterson" wrote in message
...
With linewrap fixed:
Dave Peterson wrote:
You can actually assign the formula to the range in one plop--you don't
need to
put it in one cell and copy down (but that's ok, too).
And there's a worksheet function that you could use to check to see if
the value
is in that array.
You could use
=iserror(match(value,range,0))
to determine if that value was in that range.
in VBA, you'd use application.match(), but you can still check for an
error
(=match() returns a number if there is a match).
Option Explicit
Sub testme01()
Dim MarginCell As Range
Dim MarginString As String
Dim varCodes As Variant
Dim res As Variant
Dim LastRow As Long
Dim FirstRow As Long
Dim iRow As Long
Dim delRng As Range
varCodes = Array("ABC", "DEF", "GHI", "MNO", "PQR")
MarginString = "%margin"
With ActiveSheet
With .Range("a1").EntireRow
Set MarginCell = .Cells.Find(what:=MarginString, _
after:=.Cells(.Cells.Count), LookIn:=xlValues,
_
lookat:=xlWhole, searchorder:=xlNext, _
searchdirection:=xlByColumns, MatchCase:=False)
End With
If MarginCell Is Nothing Then
MsgBox "Can't find: " & MarginString
Exit Sub
End If
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
.Range(MarginCell.Offset(1, 1), .Cells(LastRow, _
MarginCell.Offset(0, 1).Column)).FormulaR1C1 _
= "=rc[-1]" 'or whatever you want
FirstRow = 2
For iRow = LastRow To 2 Step -1
If IsError(Application.Match(.Cells(iRow, "A").Value, _
varCodes, 0)) _
Or IsError(Application.Match(.Cells(iRow, "B").Value, _
varCodes, 0))
Then
If delRng Is Nothing Then
Set delRng = .Cells(iRow, "A")
Else
Set delRng = Union(.Cells(iRow, "A"), delRng)
End If
End If
Next iRow
If delRng Is Nothing Then
'do nothing
Else
'delRng.EntireRow.Delete 'for real
delRng.EntireRow.Select 'for testing
End If
End With
End Sub
Fiona O'Grady wrote:
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
--
Dave Peterson
--
Dave Peterson