A few VBA questions - long post!

[Fiona O'Grady]

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("D24"), Type:=xlFillDefault

I'm sure there must be some way to replace the references D2 and D24 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]

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

 

[Dave Peterson]

With linewrap fixed:

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]

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

With linewrap fixed:

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

 

[Dave Peterson]

I don't see a difference between using this:

If IsError(Application.Match(.Cells(iRow, _
OriginCell.Column).Value, varCodes, 0)) _

And

Dim OriginColIndex as Long
OriginColIndex = OriginCell.Column
then
If IsError(Application.Match(.Cells(iRow, _
OriginColIndex).Value, varCodes, 0)) _


I'd guess it was something else that was the problem.

==
did you put that A in double quotes?

If IsError(Application.Match(.Cells(iRow, "A").value, _
varCodes, 0)) _

If IsError(Application.Match(.Cells(iRow, 1).value, _
varCodes, 0)) _

These two are equivalent. And .cells() will accept either numbers 1-256 or
letters ("a"-"IV").

But glad you got it working.

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

With linewrap fixed:

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("D24"), Type:=xlFillDefault

I'm sure there must be some way to replace the references D2 and D24
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

 

[Fiona O'Grady]

I don't see a difference between using this:

If IsError(Application.Match(.Cells(iRow, _
OriginCell.Column).Value, varCodes, 0)) _

And

Dim OriginColIndex as Long
OriginColIndex = OriginCell.Column
then
If IsError(Application.Match(.Cells(iRow, _
OriginColIndex).Value, varCodes, 0)) _


I'd guess it was something else that was the problem.

It probably was. I thought the first version should work as well, that's
why I was asking. I didn't want to start my VBA self-teaching with a
misconception that would make me use extra lines of code when it wasn't
necessary

Thanks,
Fiona