Hi, i need an macro that fill an cell with some numbers and when i select
that cell, i can add something to that text:

ex:

database sheet!

A B
1 john 3194/
2 mary 07/
3 ....etc

result sheet!

A B
1 john 3194/525485
2 mary 07/288548
3 ...etc

I have an database sheet with name and that code with "/" (3194/)

Can this be done? And i need them in the same cell.

Thanks in advance.

I think the code below will do the trick for you. Once you get it into your
workbook, just change the name of the Constant dbSheetName to be the actual
name of your database sheet. This is set up assuming, as you showed, that
the lookup table is in columns A and B on the database sheet.

To get this code to the right place in your workbook:
Open the workbook, select the result sheet
Right-click on the result sheet's name tab and choose [View Code] from the
list that appears.
copy the code below and paste it into the code module presented to you in
the previous step.
Change the name of database sheet to match that sheet's name in your workbook.
Close the VB editor.
Give it a test run.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const dbSheetName = "database" ' change as needed
Dim lastRow As Long
'check that an empty cell in column 2 (B) has been selected
'and that there is something next to it in column A
If Target.Column = 2 Then
If Target.Cells.Count = 1 And _
IsEmpty(Target) And _
Not IsEmpty(Target.Offset(0, -1)) Then
'find area of the lookup table
'on the dbsheet
lastRow = ThisWorkbook.Worksheets(dbSheetName). _
Range("A" & Rows.Count).End(xlUp).Row
Target.FormulaR1C1 = "=VLOOKUP(RC[-1],'" & _
dbSheetName & "'!R1C1:R" & lastRow & "C2,2,0)"
Target.Formula = Target.Value
'if there was an error (as #N/A) returned
'then clear the cell's contents
If IsError(Target) Then
Target.ClearContents
End If
End If
End If
End Sub

"puiuluipui" wrote:

Hi, i need an macro that fill an cell with some numbers and when i select
that cell, i can add something to that text:

ex:

database sheet!

A B
1 john 3194/
2 mary 07/
3 ....etc

result sheet!

A B
1 john 3194/525485
2 mary 07/288548
3 ...etc

I have an database sheet with name and that code with "/" (3194/)

Can this be done? And i need them in the same cell.

Thanks in advance.

It's working, but i have to doubleclick the cell to add something. Maybe the
code to work after i add something? The numbers to appear after i move to
cell c1?
Ex:

A1 B1
john (i write 456789) move to cell C1, and B1 to become "3194/456789" )

Thanks allot!

"JLatham" a scris:

I think the code below will do the trick for you. Once you get it into your
workbook, just change the name of the Constant dbSheetName to be the actual
name of your database sheet. This is set up assuming, as you showed, that
the lookup table is in columns A and B on the database sheet.

To get this code to the right place in your workbook:
Open the workbook, select the result sheet
Right-click on the result sheet's name tab and choose [View Code] from the
list that appears.
copy the code below and paste it into the code module presented to you in
the previous step.
Change the name of database sheet to match that sheet's name in your workbook.
Close the VB editor.
Give it a test run.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const dbSheetName = "database" ' change as needed
Dim lastRow As Long
'check that an empty cell in column 2 (B) has been selected
'and that there is something next to it in column A
If Target.Column = 2 Then
If Target.Cells.Count = 1 And _
IsEmpty(Target) And _
Not IsEmpty(Target.Offset(0, -1)) Then
'find area of the lookup table
'on the dbsheet
lastRow = ThisWorkbook.Worksheets(dbSheetName). _
Range("A" & Rows.Count).End(xlUp).Row
Target.FormulaR1C1 = "=VLOOKUP(RC[-1],'" & _
dbSheetName & "'!R1C1:R" & lastRow & "C2,2,0)"
Target.Formula = Target.Value
'if there was an error (as #N/A) returned
'then clear the cell's contents
If IsError(Target) Then
Target.ClearContents
End If
End If
End If
End Sub

"puiuluipui" wrote:

Hi, i need an macro that fill an cell with some numbers and when i select
that cell, i can add something to that text:

ex:

database sheet!

A B
1 john 3194/
2 mary 07/
3 ....etc

result sheet!

A B
1 john 3194/525485
2 mary 07/288548
3 ...etc

I have an database sheet with name and that code with "/" (3194/)

Can this be done? And i need them in the same cell.

Thanks in advance.

It would be better/easier from a coding standpoint if you just typed
something into C# and then moved into B# and the code then did the lookup for
whatever goes with what is in A# and puts that together along with what you
typed into C# into B# and erases what you typed.

How does that sound? If OK, I could modify the code this evening.

"puiuluipui" wrote:

It's working, but i have to doubleclick the cell to add something. Maybe the
code to work after i add something? The numbers to appear after i move to
cell c1?
Ex:

A1 B1
john (i write 456789) move to cell C1, and B1 to become "3194/456789" )

Thanks allot!

"JLatham" a scris:

I think the code below will do the trick for you. Once you get it into your
workbook, just change the name of the Constant dbSheetName to be the actual
name of your database sheet. This is set up assuming, as you showed, that
the lookup table is in columns A and B on the database sheet.

To get this code to the right place in your workbook:
Open the workbook, select the result sheet
Right-click on the result sheet's name tab and choose [View Code] from the
list that appears.
copy the code below and paste it into the code module presented to you in
the previous step.
Change the name of database sheet to match that sheet's name in your workbook.
Close the VB editor.
Give it a test run.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const dbSheetName = "database" ' change as needed
Dim lastRow As Long
'check that an empty cell in column 2 (B) has been selected
'and that there is something next to it in column A
If Target.Column = 2 Then
If Target.Cells.Count = 1 And _
IsEmpty(Target) And _
Not IsEmpty(Target.Offset(0, -1)) Then
'find area of the lookup table
'on the dbsheet
lastRow = ThisWorkbook.Worksheets(dbSheetName). _
Range("A" & Rows.Count).End(xlUp).Row
Target.FormulaR1C1 = "=VLOOKUP(RC[-1],'" & _
dbSheetName & "'!R1C1:R" & lastRow & "C2,2,0)"
Target.Formula = Target.Value
'if there was an error (as #N/A) returned
'then clear the cell's contents
If IsError(Target) Then
Target.ClearContents
End If
End If
End If
End Sub

"puiuluipui" wrote:

Hi, i need an macro that fill an cell with some numbers and when i select
that cell, i can add something to that text:

ex:

database sheet!

A B
1 john 3194/
2 mary 07/
3 ....etc

result sheet!

A B
1 john 3194/525485
2 mary 07/288548
3 ...etc

I have an database sheet with name and that code with "/" (3194/)

Can this be done? And i need them in the same cell.

Thanks in advance.

Well, since I have time now - try this anyhow. I think you'll like it. As
noted earlier, when there's an entry in column A and no entry in B on a row
and you make an entry into C on that row, then the lookup is done and what
you typed in is combined with the lookup results and put into column B, and
your typing in C is erased.

Private Sub Worksheet_Change(ByVal Target As Range)
Const dbSheetName = "database" ' change as needed
Dim lastRow As Long
'check that an empty cell in column 2 (B) has been selected
'and that there is something next to it in column A
If Target.Column = 3 Then
If Target.Cells.Count = 1 And _
Not IsEmpty(Target) And _
Not IsEmpty(Target.Offset(0, -2)) Then
'find area of the lookup table
'on the dbsheet
lastRow = ThisWorkbook.Worksheets(dbSheetName). _
Range("A" & Rows.Count).End(xlUp).Row
Target.Offset(0, -1).FormulaR1C1 = "=VLOOKUP(RC[-1],'" & _
dbSheetName & "'!R1C1:R" & lastRow & "C2,2,0)"
If IsError(Target.Offset(0, -1)) Then
Target.Offset(0, -1) = Target
Else
Target.Offset(0, -1).Formula = "'" & _
Target.Offset(0, -1).Value & Target.Value
End If
Target.ClearContents
End If
End If
End Sub


"JLatham" wrote:

It would be better/easier from a coding standpoint if you just typed
something into C# and then moved into B# and the code then did the lookup for
whatever goes with what is in A# and puts that together along with what you
typed into C# into B# and erases what you typed.

How does that sound? If OK, I could modify the code this evening.

"puiuluipui" wrote:

It's working, but i have to doubleclick the cell to add something. Maybe the
code to work after i add something? The numbers to appear after i move to
cell c1?
Ex:

A1 B1
john (i write 456789) move to cell C1, and B1 to become "3194/456789" )

Thanks allot!

"JLatham" a scris:

I think the code below will do the trick for you. Once you get it into your
workbook, just change the name of the Constant dbSheetName to be the actual
name of your database sheet. This is set up assuming, as you showed, that
the lookup table is in columns A and B on the database sheet.

To get this code to the right place in your workbook:
Open the workbook, select the result sheet
Right-click on the result sheet's name tab and choose [View Code] from the
list that appears.
copy the code below and paste it into the code module presented to you in
the previous step.
Change the name of database sheet to match that sheet's name in your workbook.
Close the VB editor.
Give it a test run.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const dbSheetName = "database" ' change as needed
Dim lastRow As Long
'check that an empty cell in column 2 (B) has been selected
'and that there is something next to it in column A
If Target.Column = 2 Then
If Target.Cells.Count = 1 And _
IsEmpty(Target) And _
Not IsEmpty(Target.Offset(0, -1)) Then
'find area of the lookup table
'on the dbsheet
lastRow = ThisWorkbook.Worksheets(dbSheetName). _
Range("A" & Rows.Count).End(xlUp).Row
Target.FormulaR1C1 = "=VLOOKUP(RC[-1],'" & _
dbSheetName & "'!R1C1:R" & lastRow & "C2,2,0)"
Target.Formula = Target.Value
'if there was an error (as #N/A) returned
'then clear the cell's contents
If IsError(Target) Then
Target.ClearContents
End If
End If
End If
End Sub

"puiuluipui" wrote:

Hi, i need an macro that fill an cell with some numbers and when i select
that cell, i can add something to that text:

ex:

database sheet!

A B
1 john 3194/
2 mary 07/
3 ....etc

result sheet!

A B
1 john 3194/525485
2 mary 07/288548
3 ...etc

I have an database sheet with name and that code with "/" (3194/)

Can this be done? And i need them in the same cell.

Thanks in advance.

Hi,...it's beautiful. It's working. Great work. I really like it. Thanks.
I have two more questions to ask you. Two separate codes.
The first is that i need in column "E", the time, something like =NOW(). I
want the code to run just once, and the time not to be changed if i add
something in "A" or when i close and open workbook.
Ex:
A ... E
John 10:55

If i close the workbook, and open it again, i need the time to remain 10:55.

If i add something to John:

A .... E
John (tt) 10:55

The time to be exactly like the first time 10:55.
I have this code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
N = Target.Row
If Me.Range("A" & N).Value < "" Then
Me.Range("B" & N).Value = Now
End If
End If
enditall:
Application.EnableEvents = True
End Sub

But if i add something to john, the time is changing.


And the second question is that i need some kind of vlookup macro:
I have an database and i need to extract in "D", the 6 cell, but only if two
conditions are met.

Database sheet:
A ... C ... F
John ace center
John van side

Result sheet:
A B ... D
John ace center
John van side

Can this be done?
Sorry for asking you another two questions, but i hope you can help me with
this. :)

Thanks allot for first code. You are right, i really like it.
Thanks allot










"JLatham" wrote:

Well, since I have time now - try this anyhow. I think you'll like it. As
noted earlier, when there's an entry in column A and no entry in B on a row
and you make an entry into C on that row, then the lookup is done and what
you typed in is combined with the lookup results and put into column B, and
your typing in C is erased.

Private Sub Worksheet_Change(ByVal Target As Range)
Const dbSheetName = "database" ' change as needed
Dim lastRow As Long
'check that an empty cell in column 2 (B) has been selected
'and that there is something next to it in column A
If Target.Column = 3 Then
If Target.Cells.Count = 1 And _
Not IsEmpty(Target) And _
Not IsEmpty(Target.Offset(0, -2)) Then
'find area of the lookup table
'on the dbsheet
lastRow = ThisWorkbook.Worksheets(dbSheetName). _
Range("A" & Rows.Count).End(xlUp).Row
Target.Offset(0, -1).FormulaR1C1 = "=VLOOKUP(RC[-1],'" & _
dbSheetName & "'!R1C1:R" & lastRow & "C2,2,0)"
If IsError(Target.Offset(0, -1)) Then
Target.Offset(0, -1) = Target
Else
Target.Offset(0, -1).Formula = "'" & _
Target.Offset(0, -1).Value & Target.Value
End If
Target.ClearContents
End If
End If
End Sub


"JLatham" wrote:

It would be better/easier from a coding standpoint if you just typed
something into C# and then moved into B# and the code then did the lookup for
whatever goes with what is in A# and puts that together along with what you
typed into C# into B# and erases what you typed.

How does that sound? If OK, I could modify the code this evening.

"puiuluipui" wrote:

It's working, but i have to doubleclick the cell to add something. Maybe the
code to work after i add something? The numbers to appear after i move to
cell c1?
Ex:

A1 B1
john (i write 456789) move to cell C1, and B1 to become "3194/456789" )

Thanks allot!

"JLatham" a scris:

I think the code below will do the trick for you. Once you get it into your
workbook, just change the name of the Constant dbSheetName to be the actual
name of your database sheet. This is set up assuming, as you showed, that
the lookup table is in columns A and B on the database sheet.

To get this code to the right place in your workbook:
Open the workbook, select the result sheet
Right-click on the result sheet's name tab and choose [View Code] from the
list that appears.
copy the code below and paste it into the code module presented to you in
the previous step.
Change the name of database sheet to match that sheet's name in your workbook.
Close the VB editor.
Give it a test run.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const dbSheetName = "database" ' change as needed
Dim lastRow As Long
'check that an empty cell in column 2 (B) has been selected
'and that there is something next to it in column A
If Target.Column = 2 Then
If Target.Cells.Count = 1 And _
IsEmpty(Target) And _
Not IsEmpty(Target.Offset(0, -1)) Then
'find area of the lookup table
'on the dbsheet
lastRow = ThisWorkbook.Worksheets(dbSheetName). _
Range("A" & Rows.Count).End(xlUp).Row
Target.FormulaR1C1 = "=VLOOKUP(RC[-1],'" & _
dbSheetName & "'!R1C1:R" & lastRow & "C2,2,0)"
Target.Formula = Target.Value
'if there was an error (as #N/A) returned
'then clear the cell's contents
If IsError(Target) Then
Target.ClearContents
End If
End If
End If
End Sub

"puiuluipui" wrote:

Hi, i need an macro that fill an cell with some numbers and when i select
that cell, i can add something to that text:

ex:

database sheet!

A B
1 john 3194/
2 mary 07/
3 ....etc

result sheet!

A B
1 john 3194/525485
2 mary 07/288548
3 ...etc

I have an database sheet with name and that code with "/" (3194/)

Can this be done? And i need them in the same cell.

Thanks in advance.