Repeat cell content

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How to set the macro if the information in two cell (e.g. Cell A and cell B) are the same, then the cell content (e.g. Cell C) show the information contained on the first row?

e.g. Cell A Cell B Cell C
ABC DEF FFF
ABC DEF 'display the information above' i.e. FFF
 
Hi
I'm not sure I understood you example. Are you checking if the entries
in col A,B are identical to the row before. That is A2=A1 AND B2=B1. If
yes a non VBA solution would be the following formula entered in C2
=IF(AND(A2=A1,B2=B1),"C1","something else")
 
Hi
Thanks for your solution. But as the database is large (more than two rows for same Cell A and Cell B) and there is line spacing between the data row, therefore, I think using a macro to do this formula setting for me is much helpful. So any suggestion

Thanks and regards.

----- Frank Kabel wrote: ----

H
I'm not sure I understood you example. Are you checking if the entrie
in col A,B are identical to the row before. That is A2=A1 AND B2=B1. I
yes a non VBA solution would be the following formula entered in C
=IF(AND(A2=A1,B2=B1),"C1","something else"

-
Regard
Frank Kabe
Frankfurt, German

Janmy wrote
 
Hi
I think you have to explain your issue with a little bit more detail
(regardless if it will be a formula or VBA solution).:
- what do you mean 'if the information are the same'
- which cells should be compared
- what should be néntered in col. C if the cells are not identical

You may give an example with some more rows covering all different
cases
 
Hi,
Thanks for your help. The example is:
Col A Col B Col C Col D
Row 1: 1/2/2004 1234 ABC ABC IS A MAN
Row 2: 1/3/2004 1234 ABC ABC MAN
Row 3: 2/3/2004 1234 ABC
Row 4: 4/3/2004 2345 ABC BBB

Then I need to put 'ABC IS A MAN' in Col D in row 2 and 3. And the content remained the same in Col D for row 4.

Thanks and regards.

----- Frank Kabel wrote: -----

Hi
I think you have to explain your issue with a little bit more detail
(regardless if it will be a formula or VBA solution).:
- what do you mean 'if the information are the same'
- which cells should be compared
- what should be néntered in col. C if the cells are not identical

You may give an example with some more rows covering all different
cases
 
Hi
it's probably me but I still don't understand the logic :-). Seems to
be you want to repeat the first entry of column D as long as col C and
col B remain the same. If this is true you may try the following:
- use column E as a helper column
- In E1 insert the formula: =D1
- in E2 inerste the formula
=IF(AND(B2=B1,C2=C1),D1,D2)
copy this down
if this is the correct result you may copy this and paste as values
(goto 'Edit - Paste - Special' and choose 'Values')

But I'm still not sure this will give you your desired result.
 
Cell A and Cell B cannot have more than two rows, that is not cells but
ranges (unless you have merged them).

Give some example of the particular problem, not just the generic example so
far.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Janmy said:
Hi,
Thanks for your solution. But as the database is large (more than two
Click to expand...
rows for same Cell A and Cell B) and there is line spacing between the data
row, therefore, I think using a macro to do this formula setting for me is
much helpful. So any suggestion?
 
Janmy wrote>
Col A Col B Col C Col D
Row 1: 1/2/2004 1234 ABC ABC IS A MAN
Row 2: 1/3/2004 1234 ABC ABC MAN
Row 3: 2/3/2004 1234 ABC
Row 4: 4/3/2004 2345 ABC BBB

Then I need to put 'ABC IS A MAN' in Col D in row 2 and 3. And the
Click to expand...
content remained the same in Col D for row 4.

You may try something along those lines:
Open the VB editor and enter following code for the Worksheet (not a
module!)

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
'target is the cellobject that was rightclicked
Cancel = True ' prevents the usual contextmenu to show
col = Target.Column
row = target.column
if col=4 then
lastval= Cells(row-1, col) 'value in the row above
Cells(Target.Row, col) = lastval
endif
End Sub
In your example: a rightclick on col D row 3 (now empty) will copy ABC MAN
in that cell

You may vary on this theme by inserting a For/Next, looking for a cell which
is not empty, etcetera

Alternatively, if you do not want to use this RightClick procedure, try
something like:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target = "*" Then
col = Target.Column
row = target.column
if col=4 then
lastval= Cells(row-1, col) 'value in the row above
Cells(Target.Row, col) = lastval
endif
endif
On Error GoTo 0
End Sub

In this example, if you type * in col D row 3 it will copy the value of the
cell above, at least if it happens in colums 4 (D)
Of course you may change * for any other printable character and/or use
other conditions.

HTH, Ko
 
Oops,
I wrote row = target.column. This is of course an error. Should be
row = target.row

Ko Vijn
 
Back
Top