Solved copy lines and replace values base on 2nd criteria

July 20, 2017 at 11:11:51
Specs: Windows 7
Hi All,

I have found many answers on this forum but now I'm stuck, probably because I'm a vba neophyte :(.

the objective I want to achieve is copy a fix number of rows from one sheet in another sheet based on the input in another sheet. hereunder how far I got inspired by the "Sub carHunter()" script I found on the forum:

Sub CPQbuilder()
'Determine last row with data in Sheet 1 Column A
  lastSrc_rw = Sheets("import").Range("A" & Rows.Count).End(xlUp).Row
'Loop through list of bundles
    For Each Line In Sheets("import").Range("A2:A" & lastSrc_rw)
'Search for each bundle, copy row if found
     With Sheets("Bundles").Columns(1)
      Set c = .Find(Line, LookIn:=xlValues, lookat:=xlPart)
      
       If Not c Is Nothing Then
         firstAddress = c.Address
        Do
'Find next open row in Sheet 2
          nxtDst_rw = Sheets("Quotelines").Range("A" & Rows.Count).End(xlUp).Row + 1

'Copy row containing found bundle and replace specifics
            c.EntireRow.Copy Destination:=Sheets("Quotelines").Range("A" & nxtDst_rw)
            Sheets("Quotelines").Range("B" & nxtDst_rw) = Sheets("import").Range("B" & lastSrc_rw)
            Sheets("Quotelines").Range("C" & nxtDst_rw) = Sheets("import").Range("C" & lastSrc_rw)
            Sheets("Quotelines").Range("D" & nxtDst_rw) = Sheets("import").Range("D" & lastSrc_rw)
            Sheets("Quotelines").Range("E" & nxtDst_rw) = Sheets("import").Range("E" & lastSrc_rw)
            Sheets("Quotelines").Range("F" & nxtDst_rw) = Sheets("import").Range("F" & lastSrc_rw)
            Sheets("Quotelines").Range("G" & nxtDst_rw) = Sheets("import").Range("G" & lastSrc_rw)
            Sheets("Quotelines").Range("H" & nxtDst_rw) = Sheets("import").Range("H" & lastSrc_rw)
            Sheets("Quotelines").Range("I" & nxtDst_rw) = Sheets("import").Range("I" & lastSrc_rw)
            Sheets("Quotelines").Range("J" & nxtDst_rw) = Sheets("import").Range("J" & lastSrc_rw)
            Sheets("Quotelines").Range("K" & nxtDst_rw) = Sheets("import").Range("K" & lastSrc_rw)
            Sheets("Quotelines").Range("L" & nxtDst_rw) = Sheets("import").Range("L" & lastSrc_rw)
            Sheets("Quotelines").Range("M" & nxtDst_rw) = Sheets("import").Range("M" & lastSrc_rw)
            
            
'Search for same bundle again, stop when no more found
            Set c = .FindNext(c)
       
     
        Loop While Not c Is Nothing And c.Address <> firstAddress
       End If
     End With
     
     lastSrc_rw = Sheets("import").Range("A" & Rows.Count).End(xlUp).Row - 1
    Next
End Sub


what I miss now is that based on a second find, the scrpit should check for a value in sheet:s("Bundles") column N and alternate the data if the value is "true". so I want to have another value in Sheets("import").Range("B" & lastSrc_rw) if the value in Bundles is True than if it's false.

don't hesitate if you need additional info and ths for all help you can give.

siech


See More: copy lines and replace values base on 2nd criteria

Reply ↓  Report •

✔ Best Answer
July 26, 2017 at 05:43:00
I now understand the use of the random number, but I don't understand this:

"The use of lasSrc_rw was a bug. "

I don't see lasSrc_rw used anywhere in your code.

I also noticed that you set the variable lastColumn equal to a value, but you never use it in the code.

Finally, I don't really know which is more efficient, but the 6 lines of code where you are setting Column E:J equal to the values from the "import" sheet could possibly be reduced to this:

                Sheets("IMPORT").Range("AK" & actualSrc_rw & ":AP" & actualSrc_rw).Copy _
                     Sheets("Quotelines").Range("E" & nxtDst_rw)
                Sheets("Quotelines").Range("L" & nxtDst_rw) = Sheets("IMPORT").Range("AQ" & actualSrc_rw)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
July 20, 2017 at 11:39:17
I'm confused. You say:

"I want to have another value in Sheets("import").Range("B" & lastSrc_rw) if the value in Bundles is True than if it's false."

The code is setting the value of Sheets("Quotelines").Range("B" & nxtDst_rw) equal to the value in Sheets("import").Range("B" & lastSrc_rw)

Since you are essentially pulling data from Sheets("import").Range("B" & lastSrc_rw) not putting data into that Range, I don't understand what you mean by "I want to have another value in Sheets("import").Range("B" & lastSrc_rw) "

Please explain further.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#2
July 20, 2017 at 12:45:32
Hi,

thx for picking this up so quickly. essentially I have multiple lines (e.g: 4) in the tab "bundles". Each line in import should be picked up and will copy the lines from "bundles" to produce 4 lines in the "quotelines" tab. The objective is to alternate the values in these 4 copied lines based on an additional criteria.

so I have:
Tab 1 (import): 1 line with data;
Tab 2 (quote lines): the blanck tab in which the data will be copied;
Tab 3 (bundles): the source with e.g. 4 lines;

The code posted will copy the 4 lines from tab 3 to tab 2 based on the nr of lines of tab 1.
What I need is to check an additional value (find in find) so the lines from tab 3 of type "bundle" will be copied with other data from tab 1 than the lines that are not of type bundle.

I have uploaded the excel here: https://we.tl/byTyvHUMqT
in the tab bundles I have marked the cells that should get a different value in yellow based on the value of colomn N.

Best of luck,

siech


Reply ↓  Report •

#3
July 21, 2017 at 03:57:18
I'll take a shot at this, but it's going to be a busy weekend, so don't expect an immediate answer.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

Related Solutions

#4
July 23, 2017 at 12:14:18
Hi,

I figgured it out, hereunder the code for if somebody would have the same requirement. As I'm still learning this stuff all improvement advise is also most welcome:

Sub CPQbuilder_bundles()

'Determine last row with data in Sheet 1 Column A
lastSrc_rw = Sheets("import").Range("A" & Rows.Count).End(xlUp).Row

'Loop through list of bundles
For Each Line In Sheets("import").Range("A2:A" & lastSrc_rw)

    'Search for each bundle, copy row if found
    With Sheets("Bundles").Columns(1)
        Set c = .Find(Line, LookIn:=xlValues, lookat:=xlPart)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                'create random nr
                Randomize
                LRandomNumber = Int((9999 - 1 + 1) * Rnd + 3)
                
                'Find next open row in Sheet 2
                nxtDst_rw = Sheets("Quotelines").Range("A" & Rows.Count).End(xlUp).Row + 1

                'Copy row containing found bundle and replace specifics
                isBundle = Sheets("Bundles").Cells(c.Row, 14)
                c.EntireRow.Copy Destination:=Sheets("Quotelines").Range("A" & nxtDst_rw)
                'Replace specifics
                Sheets("Quotelines").Range("B" & nxtDst_rw) = Sheets("import").Range("B" & lastSrc_rw)
                If isBundle = True Then
                    Sheets("Quotelines").Range("C" & nxtDst_rw) = Empty
                    Sheets("Quotelines").Range("D" & nxtDst_rw) = Sheets("import").Range("D" & lastSrc_rw)
                Else
                    Sheets("Quotelines").Range("C" & nxtDst_rw) = Sheets("import").Range("C" & lastSrc_rw)
                    Sheets("Quotelines").Range("D" & nxtDst_rw) = Sheets("import").Range("D" & lastSrc_rw) & c & LRandomNumber
                End If
                Sheets("Quotelines").Range("E" & nxtDst_rw) = Sheets("import").Range("E" & lastSrc_rw)
                Sheets("Quotelines").Range("F" & nxtDst_rw) = Sheets("import").Range("F" & lastSrc_rw)
                Sheets("Quotelines").Range("G" & nxtDst_rw) = Sheets("import").Range("G" & lastSrc_rw)
                Sheets("Quotelines").Range("H" & nxtDst_rw) = Sheets("import").Range("H" & lastSrc_rw)
                Sheets("Quotelines").Range("I" & nxtDst_rw) = Sheets("import").Range("I" & lastSrc_rw)
                Sheets("Quotelines").Range("J" & nxtDst_rw) = Sheets("import").Range("J" & lastSrc_rw)
                Sheets("Quotelines").Range("K" & nxtDst_rw) = Sheets("import").Range("K" & lastSrc_rw)
                Sheets("Quotelines").Range("L" & nxtDst_rw) = Sheets("import").Range("L" & lastSrc_rw)
                Sheets("Quotelines").Range("M" & nxtDst_rw) = Sheets("import").Range("M" & lastSrc_rw)
            'Search for same bundle again, stop when no more found
            Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstAddress
       End If
     End With
    'increment counter
    lastSrc_rw = Sheets("import").Range("A" & Rows.Count).End(xlUp).Row - 1
    Next
End Sub



Reply ↓  Report •

#5
July 23, 2017 at 14:38:13
What is the random number used for?

Why do you do this:

Sheets("Quotelines").Range("D" & nxtDst_rw) = _ 
Sheets("import").Range("D" & lastSrc_rw) & c & LRandomNumber

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#6
July 25, 2017 at 14:36:46
Hi Derby,
The use of lasSrc_rw was a bug. The code is used to generate csv files that will be imported into another system, the random nr is to add a token to this specific cell as it's used as external ID (I concatenate: vat+product+random token). the corrected code hereunder:

Private Sub CPQbuilder_quotelines()

Dim lastSrc_rw As Integer
Dim quote_extID As String
Dim actualSrc_rw As Integer
Dim nxtDst_rw As Integer
Dim isBundle As Boolean


'Determine last row with data in Sheet 1 Column A
lastSrc_rw = Sheets("IMPORT").Range("A" & Rows.Count).End(xlUp).Row
lastColumn = Sheets("IMPORT").Cells(1, Columns.Count).End(xlToLeft).Column
actualSrc_rw = 3

'Count lines in import tab and store bundles in memory
For Each Line In Sheets("IMPORT").Range("A3:A" & lastSrc_rw)

    'Search for each bundle, copy row if found
    With Sheets("Bundles").Columns(1)
        Set c = .Find(Line, LookIn:=xlValues, lookat:=xlPart)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                'create random nr & set some vars
                Randomize
                LRandomNumber = Int((9999 - 1 + 1) * Rnd + 3)
                
                quote_extID = Sheets("IMPORT").Range("C" & actualSrc_rw) & c & Sheets("IMPORT").Range("AJ" & actualSrc_rw)
                quoteline_extID = Sheets("IMPORT").Range("C" & actualSrc_rw) & c & LRandomNumber
                
                'Find next open row in Sheet Quotelines
                nxtDst_rw = Sheets("Quotelines").Range("A" & Rows.Count).End(xlUp).Row + 1

                'Copy row from "Bundles" containing the found bundle and replace specifics
                isBundle = Sheets("Bundles").Cells(c.Row, 14) 'SBQQ__Bundle__c
                c.EntireRow.Copy Destination:=Sheets("Quotelines").Range("A" & nxtDst_rw)
                
                'Replace specifics
                
                Sheets("Quotelines").Range("B" & nxtDst_rw) = quote_extID
                If isBundle = True Then
                    Sheets("Quotelines").Range("C" & nxtDst_rw) = Empty
                    Sheets("Quotelines").Range("D" & nxtDst_rw) = quoteline_extID
                    Bundleline_extID = quoteline_extID
                Else
                    Sheets("Quotelines").Range("C" & nxtDst_rw) = Bundleline_extID
                    Sheets("Quotelines").Range("D" & nxtDst_rw) = quoteline_extID
                End If
                Sheets("Quotelines").Range("E" & nxtDst_rw) = Sheets("IMPORT").Range("AK" & actualSrc_rw)
                Sheets("Quotelines").Range("F" & nxtDst_rw) = Sheets("IMPORT").Range("AL" & actualSrc_rw)
                Sheets("Quotelines").Range("G" & nxtDst_rw) = Sheets("IMPORT").Range("AM" & actualSrc_rw)
                Sheets("Quotelines").Range("H" & nxtDst_rw) = Sheets("IMPORT").Range("AN" & actualSrc_rw)
                Sheets("Quotelines").Range("I" & nxtDst_rw) = Sheets("IMPORT").Range("AO" & actualSrc_rw)
                Sheets("Quotelines").Range("J" & nxtDst_rw) = Sheets("IMPORT").Range("AP" & actualSrc_rw)
                Sheets("Quotelines").Range("L" & nxtDst_rw) = Sheets("IMPORT").Range("AQ" & actualSrc_rw)
            'Search for same bundle again, stop when no more found
            Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstAddress
       End If
     End With
    'increment counter
    'lastSrc_rw = Sheets("IMPORT").Range("A" & Rows.Count).End(xlUp).Row - 1
    actualSrc_rw = actualSrc_rw + 1
    Next
End Sub


Reply ↓  Report •

#7
July 26, 2017 at 05:43:00
✔ Best Answer
I now understand the use of the random number, but I don't understand this:

"The use of lasSrc_rw was a bug. "

I don't see lasSrc_rw used anywhere in your code.

I also noticed that you set the variable lastColumn equal to a value, but you never use it in the code.

Finally, I don't really know which is more efficient, but the 6 lines of code where you are setting Column E:J equal to the values from the "import" sheet could possibly be reduced to this:

                Sheets("IMPORT").Range("AK" & actualSrc_rw & ":AP" & actualSrc_rw).Copy _
                     Sheets("Quotelines").Range("E" & nxtDst_rw)
                Sheets("Quotelines").Range("L" & nxtDst_rw) = Sheets("IMPORT").Range("AQ" & actualSrc_rw)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#8
July 26, 2017 at 16:08:15
Hi Derby,

thx for the feedback, that makes the code cleaner an more maintainable. The "lasSrc_rw" was in the previous version, in my last post I cleaned it up.
I am reading about areas as I think this could further make the code cleaner. If I find a way I'll post it.

thx
siech


Reply ↓  Report •

Ask Question