Convert from vbScript to Powershell

O

OldDog

Hi,

Here is a fragment of a vbScript that I have:

XL.Range("B2").Select
XL.Selection.FormatConditions.Delete
XL.Selection.FormatConditions.Add xlExpression,, "=COUNTIF(A:A,A2)>1"
XL.Selection.FormatConditions(1).Interior.ColorIndex = 42
XL.Selection.Copy
XL.Columns("B:B").Select
XL.Range("B2").Activate
XL.Selection.PasteSpecial xlPasteFormats, xlNone, False, False

Basically what it does is change to interior color of a cell if the
"=COUNTIF(A:A,A2)>1"
statement is true.


I tried this:

for($i = 2; $i -le 13; $i++)
{
If ($ws1.Cells.Item($i,2) -eq "=COUNTIF(A:A,A2)>1") {
$ws1.Cells.Item($i,2).Interior.ColorIndex = 8;
$ws1.Cells.Item($i,2).Font.ColorIndex = 0;
$ws1.Cells.Item($i, 2).Font.Bold = $true;
}
}

But that colors every cell blue. I also tried:

If ($ws1.Cells.Item($i,2) -eq "TRUE")

And $true with no luck.

Any ideas?

OldDog
 

My Computer

W

Wolfgang Kais

Hi OldDog.

"OldDog" write:

> Hi,
>
> Here is a fragment of a vbScript that I have:
>
> XL.Range("B2").Select
> XL.Selection.FormatConditions.Delete
> XL.Selection.FormatConditions.Add xlExpression,, "=COUNTIF(A:A,A2)>1"
> XL.Selection.FormatConditions(1).Interior.ColorIndex = 42
> XL.Selection.Copy
> XL.Columns("B:B").Select
> XL.Range("B2").Activate
> XL.Selection.PasteSpecial xlPasteFormats, xlNone, False, False
>
> Basically what it does is change to interior color of a cell if the
> "=COUNTIF(A:A,A2)>1"
> statement is true.
>
> I tried this:
>
> for($i = 2; $i -le 13; $i++)
> {
> If ($ws1.Cells.Item($i,2) -eq "=COUNTIF(A:A,A2)>1") {
> $ws1.Cells.Item($i,2).Interior.ColorIndex = 8;
> $ws1.Cells.Item($i,2).Font.ColorIndex = 0;
> $ws1.Cells.Item($i, 2).Font.Bold = $true;
> }
> }
>
> But that colors every cell blue. I also tried:
>
> If ($ws1.Cells.Item($i,2) -eq "TRUE")
>
> And $true with no luck.
>
> Any ideas?
Your code does not at all look like the vbscript. How about this:

$XL.Range("B2").Select() | Out-Null
$XL.Selection.FormatConditions.Delete()
$XL.Selection.FormatConditions.Add(2, 0, "=COUNTIF(A:A,A2)>1") | Out-Null
$XL.Selection.FormatConditions.Item(1).Interior.ColorIndex = 42
$XL.Selection.Copy() | Out-Null
$XL.Columns.Item("B:B").Select() | Out-Null
$XL.Range("B2").Activate() | Out-Null
$XL.Selection.PasteSpecial(-4122, -4142, $false, $false) | Out-Null

--
Regards,
Wolfgang
 

My Computer

O

OldDog

On May 23, 4:04 pm, "Wolfgang Kais" <[email protected]> wrote:

> Hi OldDog.
>
> "OldDog" write:
>
>
>
>
>
>

> > Here is a fragment of a vbScript that I have:
>

> > XL.Range("B2").Select
> > XL.Selection.FormatConditions.Delete
> > XL.Selection.FormatConditions.Add xlExpression,, "=COUNTIF(A:A,A2)>1"
> > XL.Selection.FormatConditions(1).Interior.ColorIndex = 42
> > XL.Selection.Copy
> > XL.Columns("B:B").Select
> > XL.Range("B2").Activate
> > XL.Selection.PasteSpecial xlPasteFormats, xlNone, False, False
>

> > Basically what it does is change to interior color of a cell if the
> > "=COUNTIF(A:A,A2)>1"
> > statement is true.
>

> > I tried this:
>

> > for($i = 2; $i -le 13; $i++)
> > {
> > If ($ws1.Cells.Item($i,2) -eq "=COUNTIF(A:A,A2)>1") {
> > $ws1.Cells.Item($i,2).Interior.ColorIndex = 8;
> >     $ws1.Cells.Item($i,2).Font.ColorIndex = 0;
> > $ws1.Cells.Item($i, 2).Font.Bold = $true;
> > }
> > }
>

> > But that colors every cell blue. I also tried:
>

> > If ($ws1.Cells.Item($i,2) -eq "TRUE")
>

> > And $true with no luck.
>

> > Any ideas?
>
> Your code does not at all look like the vbscript. How about this:
>
> $XL.Range("B2").Select() | Out-Null
> $XL.Selection.FormatConditions.Delete()
> $XL.Selection.FormatConditions.Add(2, 0, "=COUNTIF(A:A,A2)>1") | Out-Null
> $XL.Selection.FormatConditions.Item(1).Interior.ColorIndex = 42
> $XL.Selection.Copy() | Out-Null
> $XL.Columns.Item("B:B").Select() | Out-Null
> $XL.Range("B2").Activate() | Out-Null
> $XL.Selection.PasteSpecial(-4122, -4142, $false, $false) | Out-Null
>
> --
> Regards,
> Wolfgang- Hide quoted text -
>
> - Show quoted text -
Your method works very well, Thank you!

BTW, do you know how to do a two column sort?


I tried this:

$xlSortValues = $xlPinYin = 1
$xlAscending = 1
$xlDescending = 2
$xlYes = 1

$range1 = $ws1.Range("A2:E$lastrow")
$range2 = $ws1.Range("B2:B$lastrow")
$range3 = $ws1.Range("E2:E$lastrow")

[void]$range1.sort($range2, $xlDescending , $range3,$nul,
$xlAscending)

But it does not sort the second column.
 

My Computer

W

Wolfgang Kais

Hello OldDog.

"OldDog" wrote:

> Do you know how to do a two column sort?
> I tried this:

> $xlSortValues = $xlPinYin = 1
> $xlAscending = 1
> $xlDescending = 2
> $xlYes = 1
>
> $range1 = $ws1.Range("A2:E$lastrow")
> $range2 = $ws1.Range("B2:B$lastrow")
> $range3 = $ws1.Range("E2:E$lastrow")
>
> [void]$range1.sort($range2, $xlDescending , $range3,$nul,
> $xlAscending)
>
> But it does not sort the second column.
Only specify the first cell to be sorted (not tested):
$range2 = $ws1.Range("B2")
$range3 = $ws1.Range("E2")

This was not a conversion problem, was it?

--
Regards,
Wolfgang
 

My Computer

O

OldDog

On May 23, 11:57 pm, "Wolfgang Kais" <[email protected]> wrote:

> Hello OldDog.
>
>
>
>
>
> "OldDog" wrote:

> > Do you know how to do a two column sort?
> > I tried this:
> > $xlSortValues = $xlPinYin = 1
> > $xlAscending = 1
> > $xlDescending = 2
> > $xlYes = 1
>

> > $range1 = $ws1.Range("A2:E$lastrow")
> > $range2 = $ws1.Range("B2:B$lastrow")
> > $range3 = $ws1.Range("E2:E$lastrow")
>

> > [void]$range1.sort($range2, $xlDescending , $range3,$nul,
> > $xlAscending)
>

> > But it does not sort the second column.
>
> Only specify the first cell to be sorted (not tested):
> $range2 = $ws1.Range("B2")
> $range3 = $ws1.Range("E2")
>
> This was not a conversion problem, was it?
>
> --
> Regards,
> Wolfgang- Hide quoted text -
>
> - Show quoted text -
Yes it is, although I have been trying to figure this one out for a
long time.

I tried it as you sugested and I get an error:

Exception calling "Sort" with "5" argument(s): "Reference is not
valid."
At E:\Scripts\PS1\XLFindDups.ps1:133 char:19
+ [void]$range1.sort <<<< ($range2,$xlDescending,$range3,$xlPinYin,
$xlAscending
)
+ CategoryInfo : NotSpecified: (:) [],
MethodInvocationException
+ FullyQualifiedErrorId : ComMethodTargetInvocation

Here is my code:

$xlSortValues = $xlPinYin = 1
$xlAscending = 1
$xlDescending = 2

$range1 = $ws1.Range("A2:E$lastrow")
$range2 = $ws1.Range("B2")
$range3 = $ws1.Range("E2")
# one-column sort --> works
#[void]$range1.sort($range2,$xlDescending)
[void]$range1.sort($range2,$xlDescending,$range3,$xlPinYin,
$xlAscending)

# If I put $nul in place of the $xlPinYin, I don't get an error but
the second column does not sort.
#
# Here is the macro from Excel:
#
# ActiveWorkbook.Worksheets("FindDups").Sort.SortFields.Add
Key:=Range("B2:B9") '
# , SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:=xlSortNormal
# ActiveWorkbook.Worksheets("FindDups").Sort.SortFields.Add
Key:=Range("E2:E9") '
# , SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
#
# And here is the vbScript I am trying to convert:
# XL.Range("A:E").Sort xl.Range("B2"),2,xl.Range("A2"),,
1,xl.Range("C2"),1,1,1,False,,1

Thanks for looking at this.

OldDog
 

My Computer

O

OldDog

On May 24, 9:37 am, OldDog <[email protected]> wrote:

> On May 23, 11:57 pm, "Wolfgang Kais" <[email protected]> wrote:
>
>
>
>
>

> > Hello OldDog.
>

> > "OldDog" wrote:

> > > Do you know how to do a two column sort?
> > > I tried this:
> > > $xlSortValues = $xlPinYin = 1
> > > $xlAscending = 1
> > > $xlDescending = 2
> > > $xlYes = 1
>

> > > $range1 = $ws1.Range("A2:E$lastrow")
> > > $range2 = $ws1.Range("B2:B$lastrow")
> > > $range3 = $ws1.Range("E2:E$lastrow")
>

> > > [void]$range1.sort($range2, $xlDescending , $range3,$nul,
> > > $xlAscending)
>

> > > But it does not sort the second column.
>

> > Only specify the first cell to be sorted (not tested):
> > $range2 = $ws1.Range("B2")
> > $range3 = $ws1.Range("E2")
>

> > This was not a conversion problem, was it?
>

> > --
> > Regards,
> > Wolfgang- Hide quoted text -
>

> > - Show quoted text -
>
> Yes it is, although I have been trying to figure this one out for a
> long time.
>
> I tried it as you sugested and I get an error:
>
> Exception calling "Sort" with "5" argument(s): "Reference is not
> valid."
> At E:\Scripts\PS1\XLFindDups.ps1:133 char:19
> + [void]$range1.sort <<<< ($range2,$xlDescending,$range3,$xlPinYin,
> $xlAscending
> )
>     + CategoryInfo          : NotSpecified: (:) [],
> MethodInvocationException
>     + FullyQualifiedErrorId : ComMethodTargetInvocation
>
> Here is my code:
>
> $xlSortValues = $xlPinYin = 1
> $xlAscending = 1
> $xlDescending = 2
>
> $range1 = $ws1.Range("A2:E$lastrow")
> $range2 = $ws1.Range("B2")
> $range3 = $ws1.Range("E2")
> # one-column sort --> works
> #[void]$range1.sort($range2,$xlDescending)
> [void]$range1.sort($range2,$xlDescending,$range3,$xlPinYin,
> $xlAscending)
>
> #  If I put $nul in place of the $xlPinYin, I don't get an error but
> the second column does not sort.
> #
> #  Here is the macro from Excel:
> #
> #  ActiveWorkbook.Worksheets("FindDups").Sort.SortFields.Add
> Key:=Range("B2:B9") '
> #         , SortOn:=xlSortOnValues, Order:=xlDescending,
> DataOption:=xlSortNormal
> #     ActiveWorkbook.Worksheets("FindDups").Sort.SortFields.Add
> Key:=Range("E2:E9") '
> #         , SortOn:=xlSortOnValues, Order:=xlAscending,
> DataOption:=xlSortNormal
> #
> #   And here is the vbScript I am trying to convert:
> #   XL.Range("A:E").Sort xl.Range("B2"),2,xl.Range("A2"),,
> 1,xl.Range("C2"),1,1,1,False,,1
>
> Thanks for looking at this.
>
> OldDog- Hide quoted text -
>
> - Show quoted text -
I found this on the Scripting guys web site:

$r = $ws1.UsedRange

$r2 = $ws1.Range("B2")
$r3 = $ws1.Range("E2")
$r4 = $ws1.Range("C2")

$a = $r.Sort($r2,$xlDescending,$r3,$null,$xlAscending, `
$r4,$xlAscending,$xlYes)


Seems that the "$a = $r.sort" is the Key item I was missing.
 

My Computer

Top