Using a formula to sort two columns

[brightyoyo]

Hi the formula works great when I manually use it , but I would like to use it in a macro where it sorts column B as well.


This is part of the macro


Dim F As Range


'Add Formula

For Each F In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)

F.Offset(, 3).FormulaArray = _

"=INDEX(List, MATCH(SMALL(COUNTIF(List, ""<""&List), ROW(R)), COUNTIF(List, ""<""&List), 0))"


Next F

 

[SirJB7]

Hi, brightyoyo!

Try double quoting each single quote in the formula.

Regards!

 

[brightyoyo]

Hi SirJB7

I get a Syntax Error

 

[SirJB7]

Hi, brightyoyo!


It should be like this:

[pre]

F.Offset(, 3).FormulaArray = _
"=INDEX(List, MATCH(SMALL(COUNTIF(List, """"<""""&List), ROW(R)), COUNTIF(List, """"<""""&List), 0))"

[/pre]
Didn't check if the original worked in a worksheet, but if it does this is the equivalent to set it by VBA code.


Regards!

 

[brightyoyo]

Hi That just copied the first cell and then copied it down the column. What I am looking for is to sort Column A and B according to Column A. I would like to do this with a macro. Here is a sample


https://www.dropbox.com/sh/67unlewr92vgm3z/XAGscYaB7G

 

[SirJB7]

Hi, brightyoyo!

That just was the answer to your original request. I'd try to check the file later.

Regards!