Click here to Skip to main content
15,946,718 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hello everybody.
i m trying to code up a udf custom traspose without pressing ctrl + shift + enter.
this udf is meant to be vertical to horizontal transpose
below my code:

Function TRASPMAT(Source As Range) As Variant 

Dim Cont As Integer, i As Integer
Cont = WorksheetFunction.CountA(Source)

For i = 1 To Cont
    TRASPMAT = Source(i)
    ActiveCell.Insert shift:=xlRight

End Function

What I have tried:

first i triend a for next loop to return single cell value inside the resulting horizontal array , but i failed.
so , as above code, i tried to assing to the cell the function retur value and shift the cell to right through a for loop along data size, also not working

1 solution

first i triend a for next loop to return single cell value inside the resulting horizontal array , but i failed

The reason you failed is in this line:
ActiveCell.Insert shift:=xlRight

As Microsoft documentation[^] says: ActiveCell returns a Range object that represents the active cell in the active window (the window on top) or in the specified window.
So, ActiveCell refers to the same cell inside a loop.

You should refer to the cells in a Source range, then copy them whenever you want:
Dim c As Range
For Each c in Source.Cells
  c.Copy ...

More: Range.Copy method (Excel) | Microsoft Learn[^]
Share this answer
Mat 257 8-Nov-23 1:51am    
therefore the function return null and i have to re set as 'void' function right? (using a c++ definitions)
it just perform copy and paste task. correct?
Maciej Los 8-Nov-23 2:35am    
No. Sorry, but you're wrong.
The intructions inside a loop do all the same. They insert a cell in active cell and move the rest of cells to the right direction.
In VBA function have to be a function if this function have to return some value(s). If a piece of code have to be executed without returning a value, then it should be a procedure (in VBA is called/named "sub").

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900