Zara  Bryant

Zara Bryant

1550033857

How to automatically insert a copied row after a specific value in a cell with VBA

I have a table that contains a set of bundles that have to be broken down into their components. For this I'm looking for VBA instructions that will copy any row that contains the tag "-edubnd" at the end of the 'sku' cell (please see table bellow for example) twice underneath itself.

It might be easier to ignore the tag component and use a specific set of values that the code looks for, that's also possible as the values marked as bundles are always the same in the column. What I mean is, instead of looking for the -edubnd tag, the code just looks for a specific value in that column,

I have created a sample table below that is similar-enough to my table in excel that it should serve to illustrate the question.

I'm currently filtering out the dataset, copying it into a different excel document, then running this:

Sub insertrows()

Dim I As Long

Dim xCount As Integer

LableNumber:

xCount = 2

For I = Range("A" & Rows.CountLarge).End(xlUp).Row To 1 Step -1

Rows(I).Copy

Rows(I).Resize(xCount).Insert

Next

Application.CutCopyMode = False

End Sub

_

CURRENT TABLE:

column1   |    column2    |  column3 |  column3
----------------------------------------------
  A       |      pear     |  blue    |  10
  A       |      apple    |  orange  |  50
  A       |      orange   |  yellow  |  30
  A       |      kiwi     |  yellow  |  20
  A       | orange-edubnd |  blue    |  100
  A       |      apple    |  green   |  10
  A       |  pear-edubnd  |  green   |  50
  A       |      mango    |  pink    |  60

_

DESIRED TABLE

Note: the copied row after each distinct column2 with the -edubnd tag

 column1   |    column2    |  column3 |  column3
----------------------------------------------
  A       |      pear     |  blue    |  10
  A       |      apple    |  orange  |  50
  A       |      orange   |  yellow  |  30
  A       |      kiwi     |  yellow  |  20
  A       | orange-edubnd |  blue    |  100
  A       | orange-edubnd |  blue    |  100
  A       | orange-edubnd |  blue    |  100
  A       |      apple    |  green   |  10
  A       |  pear-edubnd  |  green   |  50
  A       |  pear-edubnd  |  green   |  50
  A       |  pear-edubnd  |  green   |  50
  A       |      mango    |  pink    |  60


#excel-vba #excel

What is GEEK

Buddha Community

Alfie Mellor

1550038040

Pleased try this code after making the appropriate modifications to its parameters.

Sub InsertDuplicates()

    Const TestClm As String = "B"               ' modify as appropriate
    Const SearchCrit As String = "edubnd"

    Dim R As Long

    Application.ScreenUpdating = False
    With ThisWorkbook.Worksheets("InsRows")     ' change as appropriate
        For R = .Cells(.Rows.Count, TestClm).End(xlUp).Row To 2 Step -1
            If InStr(1, .Cells(R, TestClm).Value, SearchCrit, vbTextCompare) Then
                .Rows(R).EntireRow.Copy
                .Range(.Rows(R + 1), .Rows(R + 2)).Insert Shift:=xlDown
                Application.CutCopyMode = False
            End If
        Next R
    End With
    Application.ScreenUpdating = True
End Sub

Zara  Bryant

Zara Bryant

1550033857

How to automatically insert a copied row after a specific value in a cell with VBA

I have a table that contains a set of bundles that have to be broken down into their components. For this I'm looking for VBA instructions that will copy any row that contains the tag "-edubnd" at the end of the 'sku' cell (please see table bellow for example) twice underneath itself.

It might be easier to ignore the tag component and use a specific set of values that the code looks for, that's also possible as the values marked as bundles are always the same in the column. What I mean is, instead of looking for the -edubnd tag, the code just looks for a specific value in that column,

I have created a sample table below that is similar-enough to my table in excel that it should serve to illustrate the question.

I'm currently filtering out the dataset, copying it into a different excel document, then running this:

Sub insertrows()

Dim I As Long

Dim xCount As Integer

LableNumber:

xCount = 2

For I = Range("A" & Rows.CountLarge).End(xlUp).Row To 1 Step -1

Rows(I).Copy

Rows(I).Resize(xCount).Insert

Next

Application.CutCopyMode = False

End Sub

_

CURRENT TABLE:

column1   |    column2    |  column3 |  column3
----------------------------------------------
  A       |      pear     |  blue    |  10
  A       |      apple    |  orange  |  50
  A       |      orange   |  yellow  |  30
  A       |      kiwi     |  yellow  |  20
  A       | orange-edubnd |  blue    |  100
  A       |      apple    |  green   |  10
  A       |  pear-edubnd  |  green   |  50
  A       |      mango    |  pink    |  60

_

DESIRED TABLE

Note: the copied row after each distinct column2 with the -edubnd tag

 column1   |    column2    |  column3 |  column3
----------------------------------------------
  A       |      pear     |  blue    |  10
  A       |      apple    |  orange  |  50
  A       |      orange   |  yellow  |  30
  A       |      kiwi     |  yellow  |  20
  A       | orange-edubnd |  blue    |  100
  A       | orange-edubnd |  blue    |  100
  A       | orange-edubnd |  blue    |  100
  A       |      apple    |  green   |  10
  A       |  pear-edubnd  |  green   |  50
  A       |  pear-edubnd  |  green   |  50
  A       |  pear-edubnd  |  green   |  50
  A       |      mango    |  pink    |  60


#excel-vba #excel

Alfredo  Yost

Alfredo Yost

1594741535

Excel VBA Tutorial for Beginners 16 - Clear Cells in Excel using VBA

In thi MS Excel VBA video, we are going to look at how can we use the VB code to clear up cells in MS Excel. The clear cells feature of MS Excel is used for the purpose. Using this feature we can easily clear cell contents, cell formatting, cell comments and hyperlinks as well

Welcome to the The Beginner’s Guide course to Excel VBA (Visual Basic for Applications). This course enables you to Learn MS Excel VBA in simple and easy steps. In this Microsoft Excel Basics Tutorial series we will start from the basics and gradually move towards the Expert level in Microsoft Excel VBA. This MS Excel VBA course provides the Beginners to Intermediate Excel VBA Skills, Tips, and Tricks. In this course we will learn how to Enter and edit Excel data, Format numbers, fonts and alignment, Make simple pivot tables and charts, Create simple Excel formulas, How to Use Excel Functions IF and VLOOKUP. Learn common Excel functions used in any Office, How to Create dynamic reports, Build Excel formulas to analyze date, text fields, values and arrays and much more advanced stuff.

In this video we will see the Overview of formulas in Excel. We will see Basic Excel formulas & functions with examples .

#excel #vba #excel vba

Alfredo  Yost

Alfredo Yost

1594745220

Excel VBA Tutorial for Beginners 17- Delete Cells using VBA in MS Excel

In this MS Excel VBA video, we are going to see that how can we delete cells in excel using the VB code in the VBA editor

Welcome to the The Beginner’s Guide course to Excel VBA (Visual Basic for Applications). This course enables you to Learn MS Excel VBA in simple and easy steps. In this Microsoft Excel Basics Tutorial series we will start from the basics and gradually move towards the Expert level in Microsoft Excel VBA. This MS Excel VBA course provides the Beginners to Intermediate Excel VBA Skills, Tips, and Tricks. In this course we will learn how to Enter and edit Excel data, Format numbers, fonts and alignment, Make simple pivot tables and charts, Create simple Excel formulas, How to Use Excel Functions IF and VLOOKUP. Learn common Excel functions used in any Office, How to Create dynamic reports, Build Excel formulas to analyze date, text fields, values and arrays and much more advanced stuff.

In this video we will see the Overview of formulas in Excel. We will see Basic Excel formulas & functions with examples .

#excel #excel vba t #ms excel #vba

Jerod  Mante

Jerod Mante

1602023820

SQL Insert Multiple Rows | Insert Multiple Rows In SQL

We can insert multiple rows in the SQL database using insert statement, insert into select statement, and Union All statement. Inserting multiple values in the rows is a tedious task when a table comes with a lot of attributes. In Laravel, we can add the whole array as rows and insert them in the database. We have used the DB query builder and  eloquent model.

Previously, we were habituated to add values in row one at a time which is very time-taking and which also results in a lot of error.  If we want to get rid of this, we are going to learn how to insert the multiple rows in a table at a time.

SQL Insert Multiple Rows

There are different methods to insert values in a table:

1. Using  INSERT Statement.

2. Using  INSERT INTO SELECT statement.

3. Using UNION ALL keyword.

#Syntax

INSERT INTO table_name(column1, column2, ……., column n) VALUES (values in
row 1), (values in row 2), ……… (values in row n);

#sql #sql insert multiple rows

Omar  Amin

Omar Amin

1625638847

Insert Header And Footer To Word Documents With VBA

In this tutorial, I am going to share a VBA script I use to automatically insert footer and header to a Word Document.

Subscribe: https://www.youtube.com/c/JieJenn/featured

#excel-vba #vba