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")

--
Regards
Frank Kabel
Frankfurt, Germany

Janmy wrote:
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
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
How to set the macro if the information in two cell (e.g. Cell A an
cell B) are the same, then the cell content (e.g. Cell C) show th
information contained on the first row
e.g. Cell A Cell B Cell
ABC DEF FF
ABC DEF 'display th
information above' i.e. FF

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


--
Regards
Frank Kabel
Frankfurt, Germany

Janmy wrote:
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: -----

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")

--
Regards
Frank Kabel
Frankfurt, Germany

Janmy wrote:
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,
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


--
Regards
Frank Kabel
Frankfurt, Germany

Janmy wrote:
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: -----
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")
--
Regards
Frank Kabel
Frankfurt, Germany
Janmy wrote:
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
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.


--
Regards
Frank Kabel
Frankfurt, Germany

Janmy wrote:
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


--
Regards
Frank Kabel
Frankfurt, Germany

Janmy wrote:
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: -----
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")
--
Regards
Frank Kabel
Frankfurt, Germany
Janmy wrote:
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

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" wrote in message
...
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: -----

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")

--
Regards
Frank Kabel
Frankfurt, Germany

Janmy wrote:
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

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
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


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