VBA code store address with areas separated with "," and I need it sometimes with ";" instead

[tskogstrom]

VBA and Excel GUI aren't syncronised

CODE 1:
MsgBox Range(Sheets("Sheet1").Range("A1").Value).Cells.Count need
areas separated by ";"

CODE 2:
" IF Sheet2.Range("A1").Value<>
Columns("V:V").SpecialCells(xlCellTypeFormulas, 1).Rows.Address
Then ..." need areas separated by ","

- why and can I do something about it?


I try to count the number of cells defined as a range in a cell in a
worksheet with MsgBox
Range(Sheets("Sheet1").Range("A1").Value).Cells.Count . The A1 cell
value could be like:
"$V$10:$V$25,$V$33:$V$48,$V$54:$V$71,$V$77:$V$94,$V$100:$V$117,$V
$124:$V$140"

When I let the VBA code store an range address, I get the areas
separated with comma",". Because of the language defined in the Excel
GUI, they should be separated with semicomma ";"

I ran a "," find/replaced by ";" in the cell with the address, and
after the MsgBox Range(Sheets("Sheet1").Range("A1").Value).Cells.Count
worked all right.'''

Hmm, now I dont know what to do. I use the content in the cell with
other subs to compare like
" IF Sheet2.Range("A1").Value<>
Columns("V:V").SpecialCells(xlCellTypeFormulas, 1).Rows.Address
Then ..." and it work OK. I guess this code won't work if I (by
code) replace the cell content from "," to ";".


What could I do?

Kind regards,
Tskogstrom

 

[Dave Peterson]

#1.

I didn't have any trouble with your first code with:
$V$10:$V$25,$V$33:$V$48,$V$54:$V$71,$V$77:$V$94,$V$100:$V$117,$V$124:$V$140
in A1.

The msgbox returned 103 for me. But I use the comma as the windows list
separator under my regional settings.

So I saved the workbook.
Closed excel.
And changed that list separator to a semi colon.
I reopened excel.
I reopened that saved workbook and ran the code and still got 103.

So I don't think your statement about #1 is true.

If you tried:

debug.print Range("$V$10:$V$25,$V$33:$V$48,$V$54:$V$71," _
& "$V$77:$V$94,$V$100:$V$117,$V$124:$V$140").cells.count

What do you get returned in the immediate window?

In fact, if you do:
debug.print Range("a1,b9").cells.count
what happens?

I think something else is going wrong--maybe a typo in A1 so that the value
doesn't look like an address of a range?????

====

#2.
When I tested with both the comma and semicolon, the .address returned the
addresses with each area separated by commas.

If you do:
debug.print range("a1,b9").address
what do you see in the immediate window?

 

[Trevor Shuttleworth]

Does the cell have quotes ( "....") in it or not ?

Debug.Print Sheets("Sheet2").Range("A1")
Debug.Print Range(Sheets("Sheet2").Range("A1").Value).Cells.Count

$V$10:$V$25,$V$33:$V$48,$V$54:$V$71,$V$77:$V$94,$V$100:$V$117,$V$124:$V$140,$V$147:$V$163,$V$165:$V$233,$V$238:$V$289
241

Debug.Print Sheets("Sheet2").Range("A1")
Debug.Print Range(Sheets("Sheet2").Range("A1").Value).Cells.Count

"$V$10:$V$25,$V$33:$V$48,$V$54:$V$71,$V$77:$V$94,$V$100:$V$117,$V$124:$V$140"

$V$10:$V$25,$V$33:$V$48,$V$54:$V$71,$V$77:$V$94,$V$100:$V$117,$V$124:$V$140
103

The middle version, with the quotes, gives an error

Regards

Trevor