How to increase column index in dynamic way

Help Studio
, , ,
1

In every iteration of loop I want to Paste data in +4 column means for eg in first iteration bot pasted data in A1:E25 (ROWS COUNT NOT FIXED but COLUMNS ARE! )

In second iteration I want to Paste data from I column means in second iteration data should paste in I1:M25 and so on…

#help #excel #question

2

Hi @Tate_S

If the column count is fixed then use a Integer variable inside the row loop and increment it every time.

And you can always convert the number to ASCII character to get the column name of excel. Here is sample xaml to convert number to excel column name
ExcelIndexToColumn.xaml (7.4 KB)

cheers

3

Thanks @Anil_G, for quick help!
Right now I am increasing index by +9
For eg when index is 19 i am getting column name as “S” which is as expected but in very next iteration when we increase +9 will get index as 28 we are getting col name “ABS” instead of “AB”

Please suggest. XML would be helpful
Thanks again
#help #question #excel

4

Hi @Tate_S

Pass the incremented value only to the workflow (have index to column as separate workflow)that i have given. i guess you are trying to copy paste into same workflow so the same string is getting updates. If you are using the workflow itself make sure you clear the string before passing so that the string will be empty again

Cheers

5

Hello @Anil_G
So I am using it as separate workflow and passing int_index in it
But now for above 26 index getting output as “A” only

6

Hi @Tate_S

change int_IndexColumn to In Argument and str_ColumnName to out argument and make it a xaml. keep only the while loop in that xaml. That should work

cheers

7

This isn’t really how you should be doing Excel automation. You should read the whole thing into a datatable, manipulate the data in the datatable, then write back to Excel.

And don’t copy/paste. Use the Excel activities Read Range, Write Range, etc.

8

Hi,

If your excel file is empty at the start, then you can use this VBA script to give you the starting cell of where your data needs to go:

Function OffsetColumn(ColOffset As Integer)
If ActiveSheet.UsedRange.Address = “$A$1” Then
OffsetColumn = Range(“A1”).Address
Else
OffsetColumn = ActiveSheet.UsedRange.Cells(1, ActiveSheet.UsedRange.Columns.Count).Offset(0, ColOffset).Address
End If
End Function

The above functiuon takes 1 argument, the number of columns to offset (in your case +4)

If the sheet is empty it will return “A1” otherwise it will return +4 to the last column used in the sheet.

If you need any help with invoking this in your code then please reply to this :slight_smile:

9

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