VBA to assign value

[kw42chan]

hi all,

I would like to assign value for set of cells.

Image 2.png

Can someone give some advice to my code?

Please Login or Register  to view this content.

[Phuocam]

Try this ...

Please Login or Register  to view this content.

[kw42chan]

Try this ...

Please Login or Register  to view this content.

I see the code and have an epiphany (light bulb) feeling.
Thanks, it is quite hard for me to figure out where to put K = K+1.

Can have any advice?

[Doc.AElstein]

Hi kw42chan

I see the code and have an epiphany (light bulb) feeling.
Thanks, it is quite hard for me to figure out where to put K = K+1.
Can have any advice?

Think of it like this:
In the code you do what Excel usually does when given a 2 Dimensional Array of values to use in a Function or formula. . ...

__ For every "row" ' it...
____"goes along" the "columns
____ For every column
____ So as you Count through the Cells number
_____ k = k + 1' K is the Count of the cell or cell number in rows then column convention of numbering Excel Cells
_____Cells( Row , Column ) = k
____ you first go along the " columns " ( or For the first "row" - At the first "row" you do the above for every column. - The first column,)
____ then the
____ Next column. When a full set of columns is done in a row, you go to the
__ Next "row"

_...........

Here is another interesting way. It is more complicated but IMO worth having a go to understand as it does help get a good understanding of some Excel and VBA things.
The code uses the Loop Bound variable Count ( 1, 2, 3, 4, 5, ...etc. ) as the Cell “Count”, - just like the normal order convention in Excel is to go “along the rows”, then “down the columns”. That is basically your screenshot of what you want.

Brief code description. Full detailed explanation are in the ‘Green comments ( For the full code version ) which is here:
http://www.excelforum.com/showthread...=1#post4521755


Rem 1)
You need to Change the Worksheet Name and the Range used in the code to suit yours

Rem 2)
The selected cell in that order discussed above ( rows then columns ) has row and column indices or co ordinates ( normalised to starting at 1, 1 ) of
‘2a)
Rows (1, 1, 1, 1, 2, 2, 2, 2, 3, 3, 3, 3)
Columns (1, 2, 3, 4, 1, 2, 3, 4, 1, 2, 3, 4)

‘2b)(i) You can get those numbers from a bit of maths working on the Loop Bound variable Count. Like for the 5th pair of co ordinates, for the 5th Cell, using 5

Rows : Integer of ( ( 5 + ( 4 - 1 ) ) / 4 )
_____ = 2

Columns: ( Mod of ( 5-1 ) , 4 ) + 1
______ = ( 4 – 4 ) + 1
________= 0
( ( Mod is the remainder after a number has as many possible full amounts of, a second number removed )

‘2b) (ii) The above does work, or should... I am using the VBA Evaluate(“ “) Function which allows me to use the spreadsheet functions within VBA. In this case ii want to use the Column(__:__) and Row(__:__) Functions which allow me to get those Arrays of indices required. What generally happens when Excel sees an Array of values where it normally sees a single value is to do take each value in turn , do the calculation and the return the result in a Array of similar size to the given one of the Array of values
http://www.eileenslounge.com/viewtop...194190#p194190
This Array is somewhere.. But exactly how the Evaluate works is not always a direct 1 to 1 of putting the formula into a Worksheet. To have that filled Output Array in a Worksheet you would need to select a big enough range for that Array and tell Excel you want to put an Array in it ( which is the Same as telling Excel use that range as an Array for future reference. ( That is where the Famous CSE stuff come in: For this example, you would Select a 12 column Range, click in Formula Bar, paste in the Formula, and Hit Ctrl+Shift+Enter). In VBA Evaluate it may or may not give you back an Array. It may give you back what you would get if you paste that Formula into one cell. That would be the first value in that Array. It would tend to depend on what “rule”” has been set for the particular functions you are using. Whoever wrote those riles forgot and did not bother to document it. One of many simple trick is used in the code to make sure that an Array is returned. Usually this involves usoing a Function which necessarily requires an Array outout so is almost certainly in the “rule” set do that. In this case a simple
If ( Row(), TheFormula)
will cause Excel to look to see “If” over Rows. The answer is always yes ( True ) , and the calculation after is therefore always done and retuned. Always means several times at each Row over the specified rows, hence an Array output is returned.

2c) makes the code more flexible. - I am guessing you may want that . So in this section, things that are at the limits are based on the Input range rather than hard coded numbers

Rem 3) This effectively loops through the Cells in the row then column order, and puts in the Cell ( actually in an Array to be pasted out finally ) the cell number, which is what you want.

Rem 4 ) Pastes that Array out to the worksheet.

So, for example, if you chose this range_....
Using Excel 2007 32 bit

Row\Col	N	O	P
42
43
44

Worksheet: pgcArraysSplitToColumn

_... and then run the code, you get this

Row\Col	N	O	P
42	1	2	3
43	4	5	6
44	7	8	9

Worksheet: pgcArraysSplitToColumn

But the code will work for a any given Range Object of a single Area of Contiguous Cells

I just give you in this post the code simplified to the minimum. But it is basically the same code.
The Full code which I have explained is here:
http://www.excelforum.com/showthread...=1#post4519378

Please Login or Register  to view this content.

Alan
Hope the light bulb does not explode

[kw42chan]

Hi kw42chan



Think of it like this:
In the code you do what Excel usually does when given a 2 Dimensional Array of values to use in a Function or formula. . ...

__ For every "row" ' it...
____"goes along" the "columns
____ For every column
____ So as you Count through the Cells number
_____ k = k + 1' K is the Count of the cell or cell number in rows then column convention of numbering Excel Cells
_____Cells( Row , Column ) = k
____ you first go along the " columns " ( or For the first "row" - At the first "row" you do the above for every column. - The first column,)
____ then the
____ Next column. When a full set of columns is done in a row, you go to the
__ Next "row"

_...........

Here is another interesting way. It is more complicated but IMO worth having a go to understand as it does help get a good understanding of some Excel and VBA things.
The code uses the Loop Bound variable Count ( 1, 2, 3, 4, 5, ...etc. ) as the Cell “Count”, - just like the normal order convention in Excel is to go “along the rows”, then “down the columns”. That is basically your screenshot of what you want.

Brief code description. Full detailed explanation are in the ‘Green comments ( For the full code version ) which is here:
http://www.excelforum.com/showthread...=1#post4519378


Rem 1)
You need to Change the Worksheet Name and the Range used in the code to suit yours

Rem 2)
The selected cell in that order discussed above ( rows then columns ) has row and column indices or co ordinates ( normalised to starting at 1, 1 ) of
‘2a)
Rows (1, 1, 1, 1, 2, 2, 2, 2, 3, 3, 3, 3)
Columns (1, 2, 3, 4, 1, 2, 3, 4, 1, 2, 3, 4)

‘2b)(i) You can get those numbers from a bit of maths working on the Loop Bound variable Count. Like for the 5th pair of co ordinates, for the 5th Cell, using 5
Rows : Integer of ( ( 5 + ( 4 - 1 ) ) / 4 ) = 2
Columns: ( Mod ( the remainder after a number has as many possible full amounts of, a second number removed
) of ( 5-1 ) , 4 ) + 1

= ( 4 – 4 ) + 1 = 0

‘2b) (ii) The above does work, or should... I am using the VBA Evaluate(“ “) Function which allows me to use the spreadsheet functions within VBA. In this case ii want to use the Column(__:__) and Row(__:__) Functions which allow me to get those Arrays of indices required. What generally happens when Excel sees an Array of values where it normally sees a single value is to do take each value in turn , do the calculation and the return the result in a Array of similar size to the given one of the Array of values
http://www.eileenslounge.com/viewtop...194190#p194190
This Array is somewhere.. But exactly how the Evaluate works is not always a direct 1 to 1 of putting the formula into a Worksheet. To have that filled Output Array in a Worksheet you would need to select a big enough range for that Array and tell Excel you want to put an Array in it ( which is the Same as telling Excel use that range as an Array for future reference. ( That is where the Famous CSE stuff come in: For this example, you would Select a 12 column Range, click in Formula Bar, paste in the Formula, and Hit Ctrl+Shift+Enter). In VBA Evaluate it may or may not give you back an Array. It may give you back what you would get if you paste that Formula into one cell. That would be the first value in that Array. It would tend to depend on what “rule”” has been set for the particular functions you are using. Whoever wrote those riles forgot and did not bother to document it. One of many simple trick is used in the code to make sure that an Array is returned. Usually this involves usoing a Function which necessarily requires an Array outout so is almost certainly in the “rule” set do that. In this case a simple
If ( Row(), TheFormula)
will cause Excel to look to see “If” over Rows. The answer is always yes ( True ) , and the calculation after is therefore always done and retuned. Always means several times at each Row over the specified rows, hence an Array output is returned.

2c) makes the code more flexible. - I am guessing you may want that . So in this section, things that are at the limits are based on the Input range rather than hard coded numbers

Rem 3) This effectively loops through the Cells in the row then column order, and puts in the Cell ( actually in an Array to be pasted out finally ) the cell number, which is what you want.

Rem 4 ) Pastes that Array out to the worksheet.

So, for example, if you chose this range_....
Using Excel 2007 32 bit

Row\Col	N	O	P
42
43
44

Worksheet: pgcArraysSplitToColumn

_... and then run the code, you get this

Row\Col	N	O	P
42	1	2	3
43	4	5	6
44	7	8	9

Worksheet: pgcArraysSplitToColumn

But the code will work for a any given Range Object of a single Area of Contiguous Cells

I just give you in this post the code simplified to the minimum. But it is basically the same code.
The Full code which I have explained is here:
http://www.excelforum.com/showthread...=1#post4519378

Please Login or Register  to view this content.

Alan
Hope the light bulb does not explode

hi Alan,

thanks so much!!

[Doc.AElstein]

Hi kw42chan

hi Alan,
thanks so much!!

You are welcome.
Alan



P.s.

When using Reply With Quote just use a few of the relevant bits - Edit out other bits _... Like this:

_....
Think of it like this:
In the code you do what Excel usually does when .........
_...........
Here is another interesting way. .......

_.. This helps to keep the Thread less cluttered
Thanks.