Cells( rowNumber, colName ) doesn't work
 

Is there an easy way of doing this, i.e. syntactically?

I assume there's a method to convert the col name to a col number in a
separate step.

Thanks,
Peter.

Cells( rowNumber, colName ) doesn't work
 

Hi Peter,

You can refer to a cell (say) B5 either as:

Cells(5,2)
or

Cells(5,"B")

So, if I interpret you question correctly, no conversion is necessary. Note,
however, that the second (letter form) requires the appended quotes.

---
Regards,
Norman



"Peter Chatterton" wrote in message
. cable.rogers.com...
Is there an easy way of doing this, i.e. syntactically?

I assume there's a method to convert the col name to a col number in a
separate step.

Thanks,
Peter.

Cells( rowNumber, colName ) doesn't work
 

Sorry, that was ambiguous.

I meant a name as in where you can name a Range,
and also a col -- but not col "A".

Isn't that a bit ambig. for Excel?
Presumably "A" is the name of the first col.

Thanks,
Peter

"Norman Jones" wrote in message
...
Hi Peter,

You can refer to a cell (say) B5 either as:

Cells(5,2)
or

Cells(5,"B")

So, if I interpret you question correctly, no conversion is necessary.
Note,
however, that the second (letter form) requires the appended quotes.

---
Regards,
Norman



"Peter Chatterton" wrote in
message
. cable.rogers.com...
Is there an easy way of doing this, i.e. syntactically?

I assume there's a method to convert the col name to a col number in a
separate step.

Thanks,
Peter.

Cells( rowNumber, colName ) doesn't work
 

Hi Peter,

I have given you examples of the Cells syntax. Beyond that I have to confess
that I do not understand your question.

Perhaps you could describe or give an example of what you are trying to
achieve.

---
Regards,
Norman



"Peter Chatterton" wrote in message
. cable.rogers.com...
Sorry, that was ambiguous.

I meant a name as in where you can name a Range,
and also a col -- but not col "A".

Isn't that a bit ambig. for Excel?
Presumably "A" is the name of the first col.

Thanks,
Peter

"Norman Jones" wrote in message
...
Hi Peter,

You can refer to a cell (say) B5 either as:

Cells(5,2)
or

Cells(5,"B")

So, if I interpret you question correctly, no conversion is necessary.
Note,
however, that the second (letter form) requires the appended quotes.

---
Regards,
Norman



"Peter Chatterton" wrote in
message
. cable.rogers.com...
Is there an easy way of doing this, i.e. syntactically?

I assume there's a method to convert the col name to a col number in a
separate step.

Thanks,
Peter.

Cells( rowNumber, colName ) doesn't work
 

I hope I've got the foll. correct.

If you have any other comments, feel free.
I'm very new at both Excel and VBA and
it's taking some getting used.

This is a real-world project by the way, and
due in tomorrow.

I'm testing my macro with the foll.,
where the quotes are Names in the Name pulldown
at the top left of the spreadsheet:
MacroSort ActiveSheet, "Database", "col1A", "col2"


Sub MacroSort(wWs As Worksheet, strData As String, _
strPrimCol As String, strSecCol As String)
'
' Select and sort strData area -- this works.
'
wWs.Range(strData).Sort Key1:=Range(strPrimCol), _
Key2:=Range(strSecCol)
'
' Loop on rows while col A isn't empty
' If col <input named value of curr line < previous value
' Insert a blank row.
'
Dim i As Long
i = 4
While Not IsEmpty(Cells(i, 1))
'While Not IsEmpty(Cells(i, "A")) this seems to work too
If Cells(i, strPrimCol) < Cells(i - 1, strPrimCol) Then
'the above gives a type mismatch
Rows(i).Insert (xlDown)
i = i + 2
Else
i = i + 1
End If
Wend
Exit Sub
End Sub

"Norman Jones" wrote in message
...
Hi Peter,

I have given you examples of the Cells syntax. Beyond that I have to
confess
that I do not understand your question.

Perhaps you could describe or give an example of what you are trying to
achieve.

---
Regards,
Norman



"Peter Chatterton" wrote in
message
. cable.rogers.com...
Sorry, that was ambiguous.

I meant a name as in where you can name a Range,
and also a col -- but not col "A".

Isn't that a bit ambig. for Excel?
Presumably "A" is the name of the first col.

Thanks,
Peter

"Norman Jones" wrote in message
...
Hi Peter,

You can refer to a cell (say) B5 either as:

Cells(5,2)
or

Cells(5,"B")

So, if I interpret you question correctly, no conversion is necessary.
Note,
however, that the second (letter form) requires the appended quotes.

---
Regards,
Norman



"Peter Chatterton" wrote in
message
. cable.rogers.com...
Is there an easy way of doing this, i.e. syntactically?

I assume there's a method to convert the col name to a col number in
a
separate step.

Thanks,
Peter.

Cells( rowNumber, colName ) doesn't work
 

Hi Peter,

Retaining your naming conventions, I think your code modified as follows
is nearer to what you are trying to do:

Sub Tester()
MacroSort ActiveSheet, "Database", Range("col1A"), Range("col2")
End Sub

Sub MacroSort(wWs As Worksheet, strData As String, _
rngPrimCol As Range, rngSecCol As Range)
Dim i As Long
wWs.Range(strData).Sort Key1:=rngPrimCol(1), _
Key2:=rngSecCol(1)
' Loop on rows while col A isn't empty
' If col <input named value of curr line < previous value
' Insert a blank row.
i = 4
While Not IsEmpty(Cells(i, 1))
If Cells(i, rngPrimCol.Column) < _
Cells(i - 1, rngPrimCol.Column) Then
Rows(i).Insert (xlDown)
i = i + 2
Else
i = i + 1
End If
Wend
Exit Sub
End Sub

---
Regards,
Norman



"Peter Chatterton" wrote in message
. cable.rogers.com...
I hope I've got the foll. correct.

If you have any other comments, feel free.
I'm very new at both Excel and VBA and
it's taking some getting used.

This is a real-world project by the way, and
due in tomorrow.

I'm testing my macro with the foll.,
where the quotes are Names in the Name pulldown
at the top left of the spreadsheet:
MacroSort ActiveSheet, "Database", "col1A", "col2"


Sub MacroSort(wWs As Worksheet, strData As String, _
strPrimCol As String, strSecCol As String)
'
' Select and sort strData area -- this works.
'
wWs.Range(strData).Sort Key1:=Range(strPrimCol), _
Key2:=Range(strSecCol)
'
' Loop on rows while col A isn't empty
' If col <input named value of curr line < previous value
' Insert a blank row.
'
Dim i As Long
i = 4
While Not IsEmpty(Cells(i, 1))
'While Not IsEmpty(Cells(i, "A")) this seems to work too
If Cells(i, strPrimCol) < Cells(i - 1, strPrimCol) Then
'the above gives a type mismatch
Rows(i).Insert (xlDown)
i = i + 2
Else
i = i + 1
End If
Wend
Exit Sub
End Sub

"Norman Jones" wrote in message
...
Hi Peter,

I have given you examples of the Cells syntax. Beyond that I have to
confess
that I do not understand your question.

Perhaps you could describe or give an example of what you are trying to
achieve.

---
Regards,
Norman



"Peter Chatterton" wrote in
message
. cable.rogers.com...
Sorry, that was ambiguous.

I meant a name as in where you can name a Range,
and also a col -- but not col "A".

Isn't that a bit ambig. for Excel?
Presumably "A" is the name of the first col.

Thanks,
Peter

"Norman Jones" wrote in message
...
Hi Peter,

You can refer to a cell (say) B5 either as:

Cells(5,2)
or

Cells(5,"B")

So, if I interpret you question correctly, no conversion is necessary.
Note,
however, that the second (letter form) requires the appended quotes.

---
Regards,
Norman



"Peter Chatterton" wrote in
message
. cable.rogers.com...
Is there an easy way of doing this, i.e. syntactically?

I assume there's a method to convert the col name to a col number in
a
separate step.

Thanks,
Peter.

Cells( rowNumber, colName ) doesn't work
 

That's great, thanks a lot Norman!

One question: what's with the (1) in
wWs.Range(strData).Sort Key1:=strPrimCol(1), ?

It looks like you're treating the Range as a collection
of cells and I can't find that in my wrox book,
which has a lot about Ranges.

Peter.

"Norman Jones" wrote in message
...
Hi Peter,

Retaining your naming conventions, I think your code modified as follows
is nearer to what you are trying to do:

Sub Tester()
MacroSort ActiveSheet, "Database", Range("col1A"), Range("col2")
End Sub

Sub MacroSort(wWs As Worksheet, strData As String, _
rngPrimCol As Range, rngSecCol As Range)
Dim i As Long
wWs.Range(strData).Sort Key1:=rngPrimCol(1), _
Key2:=rngSecCol(1)
' Loop on rows while col A isn't empty
' If col <input named value of curr line < previous value
' Insert a blank row.
i = 4
While Not IsEmpty(Cells(i, 1))
If Cells(i, rngPrimCol.Column) < _
Cells(i - 1, rngPrimCol.Column) Then
Rows(i).Insert (xlDown)
i = i + 2
Else
i = i + 1
End If
Wend
Exit Sub
End Sub

---
Regards,
Norman



"Peter Chatterton" wrote in
message
. cable.rogers.com...
I hope I've got the foll. correct.

If you have any other comments, feel free.
I'm very new at both Excel and VBA and
it's taking some getting used.

This is a real-world project by the way, and
due in tomorrow.

I'm testing my macro with the foll.,
where the quotes are Names in the Name pulldown
at the top left of the spreadsheet:
MacroSort ActiveSheet, "Database", "col1A", "col2"


Sub MacroSort(wWs As Worksheet, strData As String, _
strPrimCol As String, strSecCol As String)
'
' Select and sort strData area -- this works.
'
wWs.Range(strData).Sort Key1:=Range(strPrimCol), _
Key2:=Range(strSecCol)
'
' Loop on rows while col A isn't empty
' If col <input named value of curr line < previous value
' Insert a blank row.
'
Dim i As Long
i = 4
While Not IsEmpty(Cells(i, 1))
'While Not IsEmpty(Cells(i, "A")) this seems to work too
If Cells(i, strPrimCol) < Cells(i - 1, strPrimCol) Then
'the above gives a type mismatch
Rows(i).Insert (xlDown)
i = i + 2
Else
i = i + 1
End If
Wend
Exit Sub
End Sub

"Norman Jones" wrote in message
...
Hi Peter,

I have given you examples of the Cells syntax. Beyond that I have to
confess
that I do not understand your question.

Perhaps you could describe or give an example of what you are trying to
achieve.

---
Regards,
Norman



"Peter Chatterton" wrote in
message
. cable.rogers.com...
Sorry, that was ambiguous.

I meant a name as in where you can name a Range,
and also a col -- but not col "A".

Isn't that a bit ambig. for Excel?
Presumably "A" is the name of the first col.

Thanks,
Peter

"Norman Jones" wrote in message
...
Hi Peter,

You can refer to a cell (say) B5 either as:

Cells(5,2)
or

Cells(5,"B")

So, if I interpret you question correctly, no conversion is
necessary.
Note,
however, that the second (letter form) requires the appended quotes.

---
Regards,
Norman



"Peter Chatterton" wrote in
message

. cable.rogers.com...
Is there an easy way of doing this, i.e. syntactically?

I assume there's a method to convert the col name to a col number
in
a
separate step.

Thanks,
Peter.

Cells( rowNumber, colName ) doesn't work
 

Hi Peter,

One question: what's with the (1) in
wWs.Range(strData).Sort Key1:=strPrimCol(1), ?

It looks like you're treating the Range as a collection
of cells and I can't find that in my wrox book,
which has a lot about Ranges.

strPrimCol(1)

is an abbreviation or shorthand version of:

strPrimCol(1).item(1)

and refers to the first cell in the specified range, which I presumed you
were intending to use as your sort key.

You can check the Item property of the Range Method in VBA help.

Additionally, Chip Pearson hosts an article written by Alan Beban which is
well worth reading:

http://www.cpearson.com/excel/cells.htm

---
Regards,
Norman



"Peter Chatterton" wrote in message
. cable.rogers.com...
That's great, thanks a lot Norman!

One question: what's with the (1) in
wWs.Range(strData).Sort Key1:=strPrimCol(1), ?

It looks like you're treating the Range as a collection
of cells and I can't find that in my wrox book,
which has a lot about Ranges.

Peter.

"Norman Jones" wrote in message
...
Hi Peter,

Retaining your naming conventions, I think your code modified as follows
is nearer to what you are trying to do:

Sub Tester()
MacroSort ActiveSheet, "Database", Range("col1A"), Range("col2")
End Sub

Sub MacroSort(wWs As Worksheet, strData As String, _
rngPrimCol As Range, rngSecCol As Range)
Dim i As Long
wWs.Range(strData).Sort Key1:=rngPrimCol(1), _
Key2:=rngSecCol(1)
' Loop on rows while col A isn't empty
' If col <input named value of curr line < previous value
' Insert a blank row.
i = 4
While Not IsEmpty(Cells(i, 1))
If Cells(i, rngPrimCol.Column) < _
Cells(i - 1, rngPrimCol.Column) Then
Rows(i).Insert (xlDown)
i = i + 2
Else
i = i + 1
End If
Wend
Exit Sub
End Sub

---
Regards,
Norman



"Peter Chatterton" wrote in
message
. cable.rogers.com...
I hope I've got the foll. correct.

If you have any other comments, feel free.
I'm very new at both Excel and VBA and
it's taking some getting used.

This is a real-world project by the way, and
due in tomorrow.

I'm testing my macro with the foll.,
where the quotes are Names in the Name pulldown
at the top left of the spreadsheet:
MacroSort ActiveSheet, "Database", "col1A", "col2"


Sub MacroSort(wWs As Worksheet, strData As String, _
strPrimCol As String, strSecCol As String)
'
' Select and sort strData area -- this works.
'
wWs.Range(strData).Sort Key1:=Range(strPrimCol), _
Key2:=Range(strSecCol)
'
' Loop on rows while col A isn't empty
' If col <input named value of curr line < previous value
' Insert a blank row.
'
Dim i As Long
i = 4
While Not IsEmpty(Cells(i, 1))
'While Not IsEmpty(Cells(i, "A")) this seems to work too
If Cells(i, strPrimCol) < Cells(i - 1, strPrimCol) Then
'the above gives a type mismatch
Rows(i).Insert (xlDown)
i = i + 2
Else
i = i + 1
End If
Wend
Exit Sub
End Sub

"Norman Jones" wrote in message
...
Hi Peter,

I have given you examples of the Cells syntax. Beyond that I have to
confess
that I do not understand your question.

Perhaps you could describe or give an example of what you are trying
to
achieve.

---
Regards,
Norman



"Peter Chatterton" wrote in
message
. cable.rogers.com...
Sorry, that was ambiguous.

I meant a name as in where you can name a Range,
and also a col -- but not col "A".

Isn't that a bit ambig. for Excel?
Presumably "A" is the name of the first col.

Thanks,
Peter

"Norman Jones" wrote in message
...
Hi Peter,

You can refer to a cell (say) B5 either as:

Cells(5,2)
or

Cells(5,"B")

So, if I interpret you question correctly, no conversion is
necessary.
Note,
however, that the second (letter form) requires the appended
quotes.

---
Regards,
Norman



"Peter Chatterton" wrote
in
message

. cable.rogers.com...
Is there an easy way of doing this, i.e. syntactically?

I assume there's a method to convert the col name to a col number
in
a
separate step.

Thanks,
Peter.

Cells( rowNumber, colName ) doesn't work
 

Great, thanks again Norman.

Peter.


"Norman Jones" wrote in message
...
Hi Peter,

One question: what's with the (1) in
wWs.Range(strData).Sort Key1:=strPrimCol(1), ?

It looks like you're treating the Range as a collection
of cells and I can't find that in my wrox book,
which has a lot about Ranges.

strPrimCol(1)

is an abbreviation or shorthand version of:

strPrimCol(1).item(1)

and refers to the first cell in the specified range, which I presumed you
were intending to use as your sort key.

You can check the Item property of the Range Method in VBA help.

Additionally, Chip Pearson hosts an article written by Alan Beban which is
well worth reading:

http://www.cpearson.com/excel/cells.htm

---
Regards,
Norman



"Peter Chatterton" wrote in
message
. cable.rogers.com...
That's great, thanks a lot Norman!

One question: what's with the (1) in
wWs.Range(strData).Sort Key1:=strPrimCol(1), ?

It looks like you're treating the Range as a collection
of cells and I can't find that in my wrox book,
which has a lot about Ranges.

Peter.

"Norman Jones" wrote in message
...
Hi Peter,

Retaining your naming conventions, I think your code modified as
follows
is nearer to what you are trying to do:

Sub Tester()
MacroSort ActiveSheet, "Database", Range("col1A"), Range("col2")
End Sub

Sub MacroSort(wWs As Worksheet, strData As String, _
rngPrimCol As Range, rngSecCol As Range)
Dim i As Long
wWs.Range(strData).Sort Key1:=rngPrimCol(1), _
Key2:=rngSecCol(1)
' Loop on rows while col A isn't empty
' If col <input named value of curr line < previous value
' Insert a blank row.
i = 4
While Not IsEmpty(Cells(i, 1))
If Cells(i, rngPrimCol.Column) < _
Cells(i - 1, rngPrimCol.Column) Then
Rows(i).Insert (xlDown)
i = i + 2
Else
i = i + 1
End If
Wend
Exit Sub
End Sub

---
Regards,
Norman



"Peter Chatterton" wrote in
message
. cable.rogers.com...
I hope I've got the foll. correct.

If you have any other comments, feel free.
I'm very new at both Excel and VBA and
it's taking some getting used.

This is a real-world project by the way, and
due in tomorrow.

I'm testing my macro with the foll.,
where the quotes are Names in the Name pulldown
at the top left of the spreadsheet:
MacroSort ActiveSheet, "Database", "col1A", "col2"


Sub MacroSort(wWs As Worksheet, strData As String, _
strPrimCol As String, strSecCol As String)
'
' Select and sort strData area -- this works.
'
wWs.Range(strData).Sort Key1:=Range(strPrimCol), _
Key2:=Range(strSecCol)
'
' Loop on rows while col A isn't empty
' If col <input named value of curr line < previous value
' Insert a blank row.
'
Dim i As Long
i = 4
While Not IsEmpty(Cells(i, 1))
'While Not IsEmpty(Cells(i, "A")) this seems to work too
If Cells(i, strPrimCol) < Cells(i - 1, strPrimCol) Then
'the above gives a type mismatch
Rows(i).Insert (xlDown)
i = i + 2
Else
i = i + 1
End If
Wend
Exit Sub
End Sub

"Norman Jones" wrote in message
...
Hi Peter,

I have given you examples of the Cells syntax. Beyond that I have to
confess
that I do not understand your question.

Perhaps you could describe or give an example of what you are trying
to
achieve.

---
Regards,
Norman



"Peter Chatterton" wrote in
message

. cable.rogers.com...
Sorry, that was ambiguous.

I meant a name as in where you can name a Range,
and also a col -- but not col "A".

Isn't that a bit ambig. for Excel?
Presumably "A" is the name of the first col.

Thanks,
Peter

"Norman Jones" wrote in message
...
Hi Peter,

You can refer to a cell (say) B5 either as:

Cells(5,2)
or

Cells(5,"B")

So, if I interpret you question correctly, no conversion is
necessary.
Note,
however, that the second (letter form) requires the appended
quotes.

---
Regards,
Norman



"Peter Chatterton" wrote
in
message

. cable.rogers.com...
Is there an easy way of doing this, i.e. syntactically?

I assume there's a method to convert the col name to a col
number
in
a
separate step.

Thanks,
Peter.