New Collection add trim
 

This works perfect but how would I add the following? Please Help.
How can I add if len(trim(cell)) <9521 then

Dim bkList As New Collection
With Workbooks("1DLSUNDAY.XLS").Worksheets("Master")
Set rng = .Range(.Cells(2, 3), .Cells(200, 3).End(xlDown))
End With

On Error Resume Next
For Each cell In rng
if len(trim(cell)) 0 then
bkList.Add Trim(cell.Text), Trim(cell.Text)
end if
Next
On Error GoTo 0

For Each itm In bkList
Workbooks.Open "C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
BLANK\" & _
itm & ".xls"
Next

New Collection add trim
 

Hi,
You can just do
if len(trim(cell)) 0 AND len(trim(cell)) <9521 Then

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Tomkat743" wrote:

This works perfect but how would I add the following? Please Help.
How can I add if len(trim(cell)) <9521 then

Dim bkList As New Collection
With Workbooks("1DLSUNDAY.XLS").Worksheets("Master")
Set rng = .Range(.Cells(2, 3), .Cells(200, 3).End(xlDown))
End With

On Error Resume Next
For Each cell In rng
if len(trim(cell)) 0 then
bkList.Add Trim(cell.Text), Trim(cell.Text)
end if
Next
On Error GoTo 0

For Each itm In bkList
Workbooks.Open "C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
BLANK\" & _
itm & ".xls"
Next

New Collection add trim
 

Thanks,

"sebastienm" wrote:

Hi,
You can just do
if len(trim(cell)) 0 AND len(trim(cell)) <9521 Then

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Tomkat743" wrote:

This works perfect but how would I add the following? Please Help.
How can I add if len(trim(cell)) <9521 then

Dim bkList As New Collection
With Workbooks("1DLSUNDAY.XLS").Worksheets("Master")
Set rng = .Range(.Cells(2, 3), .Cells(200, 3).End(xlDown))
End With

On Error Resume Next
For Each cell In rng
if len(trim(cell)) 0 then
bkList.Add Trim(cell.Text), Trim(cell.Text)
end if
Next
On Error GoTo 0

For Each itm In bkList
Workbooks.Open "C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
BLANK\" & _
itm & ".xls"
Next

New Collection add trim
 

For some reason that didn't work I still get 9585.xls cannot be found

"Tomkat743" wrote:

Thanks,

"sebastienm" wrote:

Hi,
You can just do
if len(trim(cell)) 0 AND len(trim(cell)) <9521 Then

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Tomkat743" wrote:

This works perfect but how would I add the following? Please Help.
How can I add if len(trim(cell)) <9521 then

Dim bkList As New Collection
With Workbooks("1DLSUNDAY.XLS").Worksheets("Master")
Set rng = .Range(.Cells(2, 3), .Cells(200, 3).End(xlDown))
End With

On Error Resume Next
For Each cell In rng
if len(trim(cell)) 0 then
bkList.Add Trim(cell.Text), Trim(cell.Text)
end if
Next
On Error GoTo 0

For Each itm In bkList
Workbooks.Open "C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
BLANK\" & _
itm & ".xls"
Next

New Collection add trim
 

ok i guess you are not loooking for the length of the cell text to be less
than 9521 but for its value to be less than 9521:

if len(trim(cell)) 0 AND val(trim(cell)) <9521 AND val(trim(cell)) 0
Then

- the first part of the condition makes sure there is some non-blank text in
the cell: LEngth 0
- the two following conditions make sur the Value is greater than 0 and less
than 9521.

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Tomkat743" wrote:

For some reason that didn't work I still get 9585.xls cannot be found

"Tomkat743" wrote:

Thanks,

"sebastienm" wrote:

Hi,
You can just do
if len(trim(cell)) 0 AND len(trim(cell)) <9521 Then

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Tomkat743" wrote:

This works perfect but how would I add the following? Please Help.
How can I add if len(trim(cell)) <9521 then

Dim bkList As New Collection
With Workbooks("1DLSUNDAY.XLS").Worksheets("Master")
Set rng = .Range(.Cells(2, 3), .Cells(200, 3).End(xlDown))
End With

On Error Resume Next
For Each cell In rng
if len(trim(cell)) 0 then
bkList.Add Trim(cell.Text), Trim(cell.Text)
end if
Next
On Error GoTo 0

For Each itm In bkList
Workbooks.Open "C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
BLANK\" & _
itm & ".xls"
Next

New Collection add trim
 

Thanks that fixed it.

"sebastienm" wrote:

ok i guess you are not loooking for the length of the cell text to be less
than 9521 but for its value to be less than 9521:

if len(trim(cell)) 0 AND val(trim(cell)) <9521 AND val(trim(cell)) 0
Then

- the first part of the condition makes sure there is some non-blank text in
the cell: LEngth 0
- the two following conditions make sur the Value is greater than 0 and less
than 9521.

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Tomkat743" wrote:

For some reason that didn't work I still get 9585.xls cannot be found

"Tomkat743" wrote:

Thanks,

"sebastienm" wrote:

Hi,
You can just do
if len(trim(cell)) 0 AND len(trim(cell)) <9521 Then

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Tomkat743" wrote:

This works perfect but how would I add the following? Please Help.
How can I add if len(trim(cell)) <9521 then

Dim bkList As New Collection
With Workbooks("1DLSUNDAY.XLS").Worksheets("Master")
Set rng = .Range(.Cells(2, 3), .Cells(200, 3).End(xlDown))
End With

On Error Resume Next
For Each cell In rng
if len(trim(cell)) 0 then
bkList.Add Trim(cell.Text), Trim(cell.Text)
end if
Next
On Error GoTo 0

For Each itm In bkList
Workbooks.Open "C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
BLANK\" & _
itm & ".xls"
Next