How to Insert a value into a specified Cell using a Function

[FreiBetto]

I'm new in VBA and I want something very simple: To copy a cell value to another cell. So I'm trying to make a function:

Function CopyCell(Cell1, Cell2)
Range(Cell1).Value = Cell2
End Function


I want to copy the value of Cell1 into Cell2. (C'mon, I'm a begginer!)

Didn't work, the cell where I used this function returns "#value!" what I'm doing wrong?

 

[mintjulep]

The macro recorder is your friend.

 

[PHV]

Why a function ?
you may simply do this:
Dim Cell1 As Range, Cell2 As Range
...
Cell2.Value = Cell1.Value

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

Functions do not copy cells.

Functions RETURNS A VALUE.

In your example you would NOT reference the cell receiving the value...

Function CopyCell(rCell As Range) As Variant
   CopyCell = rCell.Value
End Function

As PHV suggested, I don't know why you'd want to COMPLICATE something that simple.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[FreiBetto]

I have a column to test its values with a line, just one match will return true:

To demonstrate:

Consider these cells
A1 B1 C1 E1 F1 G1
A2 B2 C2 E2 F2 G2
A3 B3 C3 E3 F3 G3
A4 B4 C4 E4 F4 G4
A5 B5 C5 E5 F5 G5
A6 B6 C6 E6 F6 G6

I want to copy the following formula from C3 to G6
=IF($A3=C$1;CopyCell(C$2;$B3);"")

Or either:
If Value of A3 equals C1 then
Copy C2 to B3

A Column is the data list to be tested
A1 Line is the value array to be tested with the A Column
A2 Line is the Values to be pasted into the B Column

Basically, the function will allow me to "write the found value into a single column"

So the Idea is to insert into the B column only the results of the logical tests, using the cells between C3 to G6 only to DO THESE TESTS.

Thats why I chose a FUNCTION. But of course, I might be wrong and there must be another way.

 

[SkipVought]

Again, you cannot copy and paste with a function.

A function returns a single value. In this case (this formula in B3) if the expression is TRUE, then the value in C2 is returned to B3...
[tt]
=IF($A3=C$1,C$2,"")
[/tt]
This does not COPY ANYTHING! The FORMAT of C2 is NOT transferred to B3 as it would in a COPY 'n' PASTE operation.


Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[FreiBetto]

Tks Skip,

I understand that, but "copy" was the more comprehensive term for what I want to do. I will try to be more specific:

Each cell from C3 to G6 on the example above contains a DIFERENT logical test

There are 4 logical tests for each item in "A" column and with just one will return TRUE (That's an example, the real thing has 255 columns of width, or either, it would be necessary 255 LOGICAL TESTS FOR EACH VALUE OF "A" COLUMN AND JUST ONE WILL BE TRUE, makes the simple formula thing a little more difficult he?)

Actually, I’ve already tried with the CONCAT function, making the comparison and returning “” when FALSE and the value when true and then concatenate all the 255 results. IT WORKED, but Excel give a limit of cells to concatenate too small (About 26 cells), what makes necessary to concatenate AGAIN the results of each group of 26 cells already concatenated, it sucks to just write all the formulas to do it..

I’m trying a more simple solution:

WHEN value is true THEN put the correspondent value in a specific cell.

It could be a looping, testing each cell, but still it would be awesome to just put a formula to order Excel place a value from a cell to another, but if it’s impossible… what can I do?

 

[SkipVought]

Maybe it would be helpful to post a sample of these values in column A and rows 1 & 2 that are to be tested along with the logic and expected results.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[FreiBetto]

Well, it's a bunch of long codes. So I’ve changed the values but kept the same principle.

VALUES RESULTS LEMON MANDIVA POTATO TOMATO CARROT BANANA ORANGE
V V WHITE BLACK YELLOW RED GREEN BROWN MAGENTA
POTATO YELLOW F F YELLOW F F F F
TOMATO RED F F F RED F F F
BANANA BROWN F F F F F BROWN F
ORANGE MAGENTA F F F F F F MAGENTA
LEMON WHITE WHITE F F F F F F

 

[FreiBetto]

The writing got messed up, Sry. I'll Post again..

 

[FreiBetto]

Ok.

A B C D E F G
1VALUES RESULTS LEMON MANDIVA POTATO TOMATO CARROT
2------ ------- WHITE BLACK YELLOW RED GREEN
3POTATO YELLOW F F YELLOW F F
4TOMATO RED F F F RED F
5BANANA BROWN F F F F F
6ORANGE MAGENTA F F F F F
7LEMON WHITE WHITE F F F F


This is a sample of the problem, the F's are the false returns. The point of the whole thing is to achieve the results written on "B" column.

Each line of logical tests returns only one TRUE and when that happens, the correspondent value from line "2" must be placed in the column of the results

Any suggestion?

 

[SkipVought]

paste in C3. Copy across & down
[tt]
=IF(MATCH($A3,$C$1:$G$1,0)=COLUMN()-2,INDEX($C$2:$G$2,1,MATCH($A3,$C$1:$G$1,0)),"")
[/tt]

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}