Real basic VBA question

drowe005

New Member
Joined
Jan 27, 2015
Messages
39
I've got what should be a very simple question, but my VBA knowledge is extremely lacking.

I know how to make this work with an excel formula, however I need this to work with the rest of my code and the excel formulas mess stuff up.

What I need is:

Data is to be entered into columns A and B, and upon these 2 entries being entered, a combination of these two cells is to be automatically be entered into column C.

For example:
Cell A = 1234
Cell B = 5678

And then I need Cell C to automatically display
C= 12345678

Hopefully someone can help me get this figured out. Thanks in advance.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,563
Office Version
2010
Platform
Windows
I know how to make this work with an excel formula, however I need this to work with the rest of my code and the excel formulas mess stuff up.
In what way does the formula in Column C "mess stuff up"?
 

Blade Hunter

Well-known Member
Joined
Mar 13, 2008
Messages
3,147
perhaps this at the worksheet level?

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Or Target.Column = 2 Then Range("C" & Target.Row) = Range("A" & Target.Row) & Range("B" & Target.Row)
End Sub
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,648
drowe005,

I assume that both numbers in columns A, and, B are in the same row.

Here is another Worksheet_Change Event for you to consider.

In the following screenshot, the YELLOW cells have been entered first:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">1234</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">5678</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">12345678</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">5678</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1234</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">56781234</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1234</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">5678</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">12345678</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Code:
Public MyA As Long
Public MyB As Long
Public MyARow As Long
Public MyBRow As Long
Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 02/06/2015, ME834056
If Target = "" Then Exit Sub
With Application
  .EnableEvents = False
  If Target.Column = 1 Then
    MyARow = Target.Row
    MyA = Target.Value
  ElseIf Target.Column = 2 Then
    MyBRow = Target.Row
    MyB = Target.Value
  End If
  If MyA > 0 And MyB > 0 And MyARow = MyBRow Then
    Range("C" & MyARow) = MyA & MyB
    MyA = 0
    MyB = 0
    MyARow = 0
    MyBRow = 0
  End If
  .EnableEvents = True
End With
End Sub
Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,563
Office Version
2010
Platform
Windows
I assume that both numbers in columns A, and, B are in the same row.
I don't know how to say this without it sounding "bad", so please excuse the inadequacy of my wording. It looks like you wanted your code to wait until both cells (Column A and B) on the same row are filled in before the sum is placed in Column C; but, as written, there are limitation in how the code functions with respect to the row that values are place in and what happens when values are deleted. I think the following code may be what you ultimately had hoped for...

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Application.Count(Intersect(Target.EntireRow, Columns("A:B"))) = 1 Then
     Cells(Target.Row, "C").Resize(, 2).Clear
   ElseIf Application.Count(Intersect(Target.EntireRow, Columns("A:B"))) = 2 Then
     Cells(Target.Row, "C").Value = Application.Sum(Cells(Target.Row, "A").Resize(, 2).Value)
   Else
     Cells(Target.Row, "A").Resize(, 3).Clear
   End If
 End Sub
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,648
I don't know how to say this without it sounding "bad", so please excuse the inadequacy of my wording. It looks like you wanted your code to wait until both cells (Column A and B) on the same row are filled in before the sum is placed in Column C; but, as written, there are limitation in how the code functions with respect to the row that values are place in and what happens when values are deleted. I think the following code may be what you ultimately had hoped for...

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Application.Count(Intersect(Target.EntireRow, Columns("A:B"))) = 1 Then
     Cells(Target.Row, "C").Resize(, 2).Clear
   ElseIf Application.Count(Intersect(Target.EntireRow, Columns("A:B"))) = 2 Then
     Cells(Target.Row, "C").Value = Application.Sum(Cells(Target.Row, "A").Resize(, 2).Value)
   Else
     Cells(Target.Row, "A").Resize(, 3).Clear
   End If
 End Sub

I guess we will just have to see what reply will work correctly for drowe005.
 

drowe005

New Member
Joined
Jan 27, 2015
Messages
39
Thanks for the input from you all, but I haven't be able to make it all work right yet. And sorry for the delay posting back; it has been one hectic day today.

First off, I'd like to thank you, hiker, since a majority of the code that I am currently using is from a post of yours from several years ago; I then modified it slightly to suit my needs better.

While trying to figure out how to put either of you guy's codes into mine, a comment of Ricks gave me a better idea. Ideally, using only 2 columns would be perfect for me. The only whole reason I wanted a 3rd column to display a combination of the two cells was to assign a unique number that related to that row's set of data in order for the program to check for duplicate entries when new entries are added. So if it would be possible to to tell the program to wait until data was entered into both cells A and B (and just scratch the whole use of a 3rd column), and then check for the duplicates in the other rows, that would be perfect.

Here is the code that I currently have:
Code:
Option ExplicitPrivate Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 12/18/2012 mrexcel.com
If Intersect(Target, Range("C2:C3000")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
Dim lc As Long, fr As Long, n As Long, nr As Long
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  n = Application.CountIf(Columns(1), Cells(Target.Row, 1))
  If n = 1 Then
    lc = Cells(Target.Row, Columns.Count).End(xlToLeft).Column
    If lc = 1 Then
      Cells(Target.Row, lc + 2) = Format(Now, "m/d/yyyy h:mm")
    ElseIf lc > 2 Then
      Cells(Target.Row, lc + 1) = Format(Now, "m/d/yyyy h:mm")
    End If
  Else
    fr = 0
    On Error Resume Next
    fr = Application.Match(Cells(Target.Row, 1), Columns(1), 0)
    On Error GoTo 0
    If fr > 0 Then
      lc = Cells(fr, Columns.Count).End(xlToLeft).Column
      If lc = 1 Then
        Cells(fr, lc + 2) = Format(Now, "m/d/yyyy h:mm")
      ElseIf lc > 2 Then
        Cells(fr, lc + 1) = Format(Now, "m/d/yyyy h:mm")
      End If
      Target.ClearContents
    End If
  End If
  On Error Resume Next
  Me.Range("C1", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  On Error GoTo 0
  nr = Me.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
  Me.Cells(nr, 1).Select
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub
Currently the program accepts data into cells A, B, and C, and upon hitting the Enter key in cell C, it then compares all the cells in column A, and if it is a unique record, it will add a time "in" stamp into column D. If it is a duplicate record of cell A, it will add a time "out" stamp into Cell F and delete the entire row of the most recent duplicate record, and then returns to Cell A of the first free row.


An alternate solution would be if one of you could help me paste your code from your response in this post into my code. Thanks again to both of you.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,648
drowe005,

First off, I'd like to thank you, hiker, since a majority of the code that I am currently using is from a post of yours from several years ago; I then modified it slightly to suit my needs better.
Thanks for the feedback.

You are very welcome. Glad I could help.

And, I am glad that you were able to modify the code to suit your needs better.


While trying to figure out how to put either of you guy's codes into mine, a comment of Ricks gave me a better idea. Ideally, using only 2 columns would be perfect for me. The only whole reason I wanted a 3rd column to display a combination of the two cells was to assign a unique number that related to that row's set of data in order for the program to check for duplicate entries when new entries are added. So if it would be possible to to tell the program to wait until data was entered into both cells A and B (and just scratch the whole use of a 3rd column), and then check for the duplicates in the other rows, that would be perfect.
I do not completely understand your last quote.

Maybe Blade Hunter, or, Rick Rothstein, or, someone else will understand your last quote, and, solve your current request.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,563
Office Version
2010
Platform
Windows
I don't know how to say this without it sounding "bad", so please excuse the inadequacy of my wording. It looks like you wanted your code to wait until both cells (Column A and B) on the same row are filled in before the sum is placed in Column C; but, as written, there are limitation in how the code functions with respect to the row that values are place in and what happens when values are deleted. I think the following code may be what you ultimately had hoped for...

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Application.Count(Intersect(Target.EntireRow, Columns("A:B"))) = 1 Then
     Cells(Target.Row, "C").Resize(, 2).Clear
   ElseIf Application.Count(Intersect(Target.EntireRow, Columns("A:B"))) = 2 Then
     Cells(Target.Row, "C").Value = Application.Sum(Cells(Target.Row, "A").Resize(, 2).Value)
   Else
     Cells(Target.Row, "A").Resize(, 3).Clear
   End If
 End Sub
The OP asked to concatenate the A and B cells, so then why on earth did I use the SUM function in my code???? The code should have been this...

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Application.Count(Intersect(Target.EntireRow, Columns("A:B"))) = 1 Then
     Cells(Target.Row, "C").Resize(, 2).Clear
   ElseIf Application.Count(Intersect(Target.EntireRow, Columns("A:B"))) = 2 Then
     Cells(Target.Row, "C").Value = Cells(Target.Row, "A").Value & Cells(Target.Row, "B").Value
   Else
     Cells(Target.Row, "A").Resize(, 3).Clear
   End If
 End Sub




While trying to figure out how to put either of you guy's codes into mine, a comment of Ricks gave me a better idea. Ideally, using only 2 columns would be perfect for me. The only whole reason I wanted a 3rd column to display a combination of the two cells was to assign a unique number that related to that row's set of data in order for the program to check for duplicate entries when new entries are added. So if it would be possible to to tell the program to wait until data was entered into both cells A and B (and just scratch the whole use of a 3rd column), and then check for the duplicates in the other rows, that would be perfect.
I do not completely understand your last quote.

Maybe Blade Hunter, or, Rick Rothstein, or, someone else will understand your last quote, and, solve your current request.
Unfortunately, I do not understand it either
 
Last edited:

drowe005

New Member
Joined
Jan 27, 2015
Messages
39
drowe005,



Thanks for the feedback.

You are very welcome. Glad I could help.

And, I am glad that you were able to modify the code to suit your needs better.




I do not completely understand your last quote.

Maybe Blade Hunter, or, Rick Rothstein, or, someone else will understand your last quote, and, solve your current request.
My current worksheet looks like this:



Right now, my data in cell A has to be a combination of cells B and C in order to give a unique number for that combination of data. Several combinations of Emp ID can be on the same MO, however, only one MO can be assigned to each Emp ID at any given time. The way the code works now is only going off the data in cell A. What I would like to do is tell the code to look at the combination of both cell B and C, and then compare this to the rest (instead of only looking at cell A and comparing to the rest). If that is not possible, simply having the code to tell it to automatically make that combination of cells B and C (like my original request) would also solve my problem.

Ideally, my perfect setup for a worksheet would look like this:



Hopefully this clarified what I was asking, I know it did sound confusing with the way that I worded it.
 
Last edited:

Forum statistics

Threads
1,085,193
Messages
5,382,242
Members
401,781
Latest member
Dlloyd15

Some videos you may like

This Week's Hot Topics

Top