Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Apply VBA macro to multiple cells

Tags:

excel

vba

I would say I am not expert in VBA. I have a VBA code that append multiple values from selected list in one cell. The code works but now I would like to apply the code to multiple cells.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' To Select Multiple Items from a Drop Down List in Excel
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$A$7" Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & "," & Chr(10) & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

How can I extend my code to use cell range

$A$7:$A$18 

instead of "$A$7" so that I can apply the VBA code to multiple cells in Excel.

like image 345
Developer Avatar asked Aug 30 '17 12:08

Developer


People also ask

How do I put the same value in multiple cells in Excel VBA?

Select the entire column Put the cursor to the first cell in the column (or the second one if your Table has headers), then press Shift+Ctrl+End to go to the end of your table, hold Shift and press the Left key repeatedly until only the needed column gets selected.

How do I apply the same macro to all sheets?

Click Insert > Module, and paste the following macro in the Module Window. 3. Then put the cursor at the first part macro, and press F5 key to run the code, and your macro code will be applied to one by one sheet.

How to select multiple columns in Excel VBA?

You can select multiple columns the same way you have selected a single column. But the code is different here. So let’s start by opening the VBA window! We want to select columns from B to D. For that, the code is, And out multiple columns are selected.

How to select entire column or range automatically in Excel using VBA?

VBA programming codes can select entire columns or ranges automatically which will save a lot of your time. In this article, we will show you some of the methods to do that job. 1. Run a VBA Code to Select a Single Column 2. Apply a VBA Code to Select Multiple Columns 3. Use a VBA Code to Select Columns in a Range

How to select a range using VBA codes in Excel?

Selecting a range using VBA codes is also easy and requires a small length of code. Assume that we need to select a range from B3 to F13. Follow these steps to learn! Insert the VBA code into the module. We have selected our range using the VBA codes. You can input numbers or texts in your selected range too.

How to enter a VBA code in Excel?

To enter a VBA code we first have to open the VBA window. You can either do it using keyboard shortcuts or from your Developer Tab. Press Ctrl+F11 to open the VBA Window. In the VBA window, we have to create a module to write down our code. Click on Insert, then click Module to open one.


1 Answers

Change

If Target.Address = "$A$7" Then

to

If Not Intersect(Target, Range("A7:A18")) Is Nothing Then
like image 132
Egan Wolf Avatar answered Sep 28 '22 01:09

Egan Wolf