Copy a range of data and paste it in every other cell

Help
1

image
image902×178 17.1 KB

Need help in transform from either from row to every other cell in row or column to every other cell in row. (see which convenient to you)

Screenshot attached, left hand side is data either showing in a row or in a column, right hand side is the result i wish to get.

thanks!

2

Hi @damonyl - Use Read Range Excel, that will store the output as Data Table. Then For Each Row of Data Table use Write Cell Activity. In Write Cell pass the cell range something like this “A” & Counter. This “Counter” should be integer and incremented after the write.

Hence write cell A1 gets incremented to A2, A3 like that inside the For Each Row Data Table.

Also the at the same time the text your going to write using Write Cell should be row(counter).ToString

It should be

  1. Read Range Data Table
  2. For Each Datatable
  3. Write Cell = row(Counter).ToString and Range = “A” & Counter

Thanks,
AK

3

Hi @AnandKumar26 But as from your answer, is this write in a column, what i need is A2,b2,c2…, write in a row.

If in VBA, that’s function can write the cell coordinate like (counter, 2) so can increase in row, not sure if VBA has it. or any method bring out the same way

4

Hi Damonyl

why not try to append the datatable to the excel?

5

Hi @Puleoo, nope in my case, i have a list of existing data, if append, it wont went into same row

6

I think i got what your mean.

try to assign a array for “A”, “B”,…“AA”.
And you use array(0) stand for “A”, so you could use the method like in VBA.

7

@Puleoo, is that has any automating way to assign “A”, “b”, “c”…

but good trick, will try

8

Hello @damonyl
If you want you can iterate the letters to using this Code
Convert.ToChar(Convert.ToUInt16("A"C)+1)
in place of add your counter and it’ll add the letters just like numbers
Check the sample workflow for better understanding
IncrementOption.xaml (8.8 KB)

3 Likes
9

For that you can create an Array Variable and store the something like this {“A”,“B”,“C”… “AZ”…“BZ”}

Then you can get the value from Array based upon your Index value which returns Array(0) which gives “A”

Hence with that you can write in cell “A2”

Thanks,
AK

10

Fantastic!
its work!

but @vickydas, do you mind to explain what had happen? what is "A"C, what is ToUInt16 and is +1 to make it start from 1, so that will be A?

11

Hello @damonyl
Convert.ToChar(Convert.ToUInt16("A"C)+1)
Here ("A"C) is our character
(Convert.ToUInt16("A"C) here we are converting our Character to Integer so that we can add our number to get different value

(Convert.ToUInt16("A"C)+1) here as we have converted our value to Int we are adding an integer here its 1 so after adding that we’ll get a value in integer type

Convert.ToChar(Convert.ToUInt16("A"C)+1) in this final step we are converting are integer value once again to character to get the desired alphabet

We have already assigned it’s value as A but by adding + 1 we are getting the next alphabet

12

@vickydas,

What’s the C stand for? as i know is convertible format, but cant recall.

what is difference with

and ToInt32?

And i tried, it can turns up to AA, AB, any method can bring this up?

13

Char its to tell that the Indicated element is a Char

There is no difference you can do it with int32 as well the goal is to convert it to an integer format to add the characters

Yes you can do this using this code
Convert.ToChar(Convert.ToUInt16("A"C))+Convert.ToChar(Convert.ToUInt16("A"C)+1)
The output of this code will be AB

1 Like
14

Thanks @vickydas! for explaining so clear and wipe out my confusion!

15

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.