I have E,F,G of Excel columns. G column is an Average data column. E column is 1st Jun data and F column is 2nd Jun data. In E3 cell, I label ‘1-Jun’, then in F3 cell I label ‘2-Jun’ as my data headers.
I want to add 3rd Jun data, so from G3 cell, I insert 1 column before G column. Now, the newly added column will take over the G column, and the previous G column will become H column.
Now, the text order in row 3 starting from E3 cell will become as below:
E3 > F3 > G3 > H3
1-Jun > 2-Jun > (blank) > Average
I know I can use auto fill (source = F3, destination = G3) to auto fill in proper date in G3, but as I continuously insert new column(s) later, the auto fill source and destination will be different.
My plan is, source = ‘current cell which already selected’, destination = ‘+ 1 right cell from the current selected cell’
How can I achieve my plan?
The best way to add this is via using the vba.
U can try the following approach:
- Read the excel using read range and store the column names in array using the below assign activity
arr_Columns= (From dc In dt.Columns.Cast(Of DataColumn)
assuming dt is the datatable
Find the index of Average column using Array.IndexOf(arr_Columns,“Average”)
Now use the index to find the column letter of that column in excel, use the link below to get that
Convert column index to alphabetical characters UiPath - RPA Learners
Once you get the column letter, you can use the below vba to insert a column which also shifts the existing column to right
here val is parameter representing column letter,
let’s say ur Average column is at G , then the val would be G here
for example, consider this excel:
if we put val=“G”, and run the vba we can get like this:
The new column would be created at G and all other existing columns will shift to next columns
Hope this helps u
Thanks & Regards,