Cells( rowNumber, colName ) doesn't work

[Peter Chatterton]

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.

 

[Norman Jones]

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.

 

[Peter Chatterton]

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]

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.

 

[Peter Chatterton]

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]

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

 

[Peter Chatterton]

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]

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

 

[Peter Chatterton]

Great, thanks again Norman.

Peter.

Hi Peter,


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