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.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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"?
 
Upvote 0
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
 
Upvote 0
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:


Excel 2007
ABC
2
31234567812345678
4
55678123456781234
6
71234567812345678
8
Sheet1



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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top