Prev: test for exernal links
Next: Auto row fit height
From: Rikkart on 15 Mar 2010 10:31
Can I replace the copy-paste values code with something like
<range>.cells(i,j).value = <range>.cells(k,l).formula ? (or .text or .? or
....)

I have a standard list with standard columns, and operational lists with
subsets of columns.
I also have an equivalence table between columns, to accomodate for
titleswitching, missing columns and changes in the order.
Now when I try a nested loop for rows and columns, applying a
"<standardlistrange>.cells(i,j).formula =
<operationalrange>.cells(i,k).value" formula to every cell, I get good
results,
EXCEPT FOR e.g. text being converted to a date ( "5 - 10" in the "number of
employees" column becomes a date) and telephone number text fields become
numbers in standard format etc. PasteValues doesn't do this, but I'd like to
avoid the copy-paste cycle inside my nested loop for speed...

Thanks beforehand

..cells(i,j).formula =
From: Dave Peterson on 15 Mar 2010 11:01
You could set the format to be the same:

with somecell
.numberformat = someothercell.numberformat
.value = someothercell.value
end with




Rikkart wrote:
>
> Can I replace the copy-paste values code with something like
> <range>.cells(i,j).value = <range>.cells(k,l).formula ? (or .text or .? or
> ...)
>
> I have a standard list with standard columns, and operational lists with
> subsets of columns.
> I also have an equivalence table between columns, to accomodate for
> titleswitching, missing columns and changes in the order.
> Now when I try a nested loop for rows and columns, applying a
> "<standardlistrange>.cells(i,j).formula =
> <operationalrange>.cells(i,k).value" formula to every cell, I get good
> results,
> EXCEPT FOR e.g. text being converted to a date ( "5 - 10" in the "number of
> employees" column becomes a date) and telephone number text fields become
> numbers in standard format etc. PasteValues doesn't do this, but I'd like to
> avoid the copy-paste cycle inside my nested loop for speed...
>
> Thanks beforehand
>
> .cells(i,j).formula =

--

Dave Peterson
 | 
Pages: 1
Prev: test for exernal links
Next: Auto row fit height