Results for "copy rows specific string missing lot"

Aha merci !! Mais bien sûr comment ai-je pu ne pas y penser plus tôt !!

Bonne nouvelle année à vous Curulis !

Hello Jeanne,

When you want to delete rows in the same time you're looking after items, you have to write your FOR-NEXT code from bottom to the top of the rows!!
Try this!

Have a happy new year!

Hi, I hope you are well,

I am encountering a problem with a function/code that I built in order to:

Go through the rows of a sheet one after the other and :

-Copy the rows in which the first cell contains a string,

-Paste the rows in a second sheet

-Delete said rows on the parent file

The thing is, this code is partly working, some of the rows which contain the string are indeed copied and pasted but some arent ! And I am not understanding why.

The rows that are sucessfully copied also are not the same ones when I rearrange their place in the sheet

See below my code

My code:

Sub ctrlcctrlv()

Index1 = 1 'index for selecting parent sheet where to find rows
Index2 = 2 'index for sheet where to paste the rows

IndexL2 = 1 'index for pasting rows one after the other on sheet 2

anomalie = "example" 'the string I'm looking for

For Each rw In Worksheets(Index1).Rows
libellé = rw.Cells(1, 1) 'the string in first cell of my row for which I want to check if it contains the string I'm looking for
If InStr(1, libellé, anomalie) <> 0 Then
rw.Copy Worksheets(Index2).Rows(IndexL2)
rw.Delete
IndexL2 = IndexL2 + 1
End If
Next

End Sub

edit moderation : code put between code tags, using "</>" button on the menu bar. Please make sure to use it for future messages.

Thank you very much for anyone who will take the time to answer.

I think i might change methods all over ... :[ maybe look for my string in the whole column directly and store the indexes that come up :(

Sincerely,

First proposal :

Good afternoon Jeremie,

Are you French? If so, why are you posting in the English section of this forum?

Anyway, can you share your file or a part of it, so that we can help you more easily?

Hi everybody ! I am pleased to be a new member of this group that has helped me so much trough many of my projects.

Here is my problem. I want to do the sum of values in a specific range of cells. In the picture provided, I would like to know the total value of "CAL JAUNE 20 X 50' SP". So I'd need to find all the cells that contain the value "CAL JAUNE 20 X 50' SP" in the B column and do the sum of all the cells in the same row but in the E colum. I'll have to do that with a bunch of other codes. Also the SUM of "CAL JAUNE 20 X 50' SP" has to be in cell A1 of sheet 2, the SUM of "CAL 100' HARM MOKA/6BTE" in cell B1 of sheet 2... The quantity of CAL JAUNE 20 X 50' SP is going to change as they are produce, so that's why I need to identify the cells with this value in it.

Thanks a lot in advance, J !:)

Hi, I totally agree with Saboh. But if you still want to use VBA, here is some starting points :

Last row based on column A :

LR = Sheets("SheetName").Range("A" & Rows.Count).End(xlUp).Row

Writing formula with VBA :

Sheets("SheetName").Range("A1").Formula = "=SUM(B1:B" & LR & ")"

You can also use generic formulas like =SUM(B:B), but those ones use quite more ressources.

Hello Noura and welcome to the forum,

If I may, you're not following the right approach. There are native Excel tools that allow you to do this in a simple and intuitive way, without using VBA (old, security concerns, subject to bugs…).

Based on your description, it sounds like you're looking for tables. Here's a great tutorial that gives a comprehensive overview of their features: Excel table: comprehensive tutorial with examples.

For your information, in addition to automatically extending your formulas, this will also apply to pivot tables.

Hi everyone,

I'm an investment analyst and new to using VBA. I have a spreadsheet where I add new data every month (one new row each time). I want to use VBA to:

• Find the last row with data
• Automatically extend formulas to include the new row
• Update named ranges or charts so they stay current

What is the best way to do this in VBA?
Any simple examples or advice would be really helpful.

Thank you!

Best regards,

Noura

Hi Forum

Hi Engkemo, hope to help again

Don't forget to end the discussion by checking "Solved"

Mytå

Hi Mytå,

amazing ! its working exactly as expected !

thanks a million for your support its really appreciated

engkemo

Hi Forum

Welcome you as new membres.

Try with this macro.

Sub CompareAndCopy()
Dim dict As Object
Dim CellId As String
Dim Ws1 As Worksheet
Dim Ws2 As Worksheet
Dim Ws3 As Worksheet
Dim Cel As Range

Application.ScreenUpdating = False

Set dict = CreateObject("Scripting.Dictionary")
Set Ws1 = Sheets("Sheet1")
Set Ws2 = Sheets("135")
Set Ws3 = Sheets("target format")

Ws3.Range("A1").CurrentRegion.Offset(1).ClearContents

For Each Cel In Ws2.Range("H2:H" & Ws2.Cells(Rows.Count, 1).End(xlUp).Row)
  If Not dict.Exists(Cel) Then dict.Add CStr(Cel.Value), Cel.Row
Next Cel

For Each Cel In Ws1.Range("F2:F" & Ws1.Cells(Rows.Count, 1).End(xlUp).Row)
  CellId = CStr(Split(Cel, "=")(1))
    If dict.Exists(CellId) Then
        Ws1.Cells(Cel.Row, "A").Resize(, 7).Copy Ws3.Range("A" & Ws3.Cells(Rows.Count, "A").End(xlUp).Row + 1)
        Ws2.Cells(dict(CellId), "A").Resize(, 7).Copy Ws3.Range("H" & Ws3.Cells(Rows.Count, "H").End(xlUp).Row + 1)
    End If
Next Cel

Ws3.Columns("A:N").AutoFit

Application.ScreenUpdating = False

Set Ws1 = Nothing
Set Ws2 = Nothing
Set Ws2 = Nothing
Set dict = Nothing

End Sub

Have a nice day

Mytå

Hi All,

I'm new to VBA macros on Excel and need your help for an important macro that I'm about to do ...

the required macro must do the following :

compare one coulmn in the 1st sheet to another one in the second sheet and with are the same, it will copies all the line to a new sheet... example :

the macro will make a loop to compare sheet1.cellID to 135.Cell ID and they have the same value, the macro will copy the following cells " Subrack No. Slot No. Subsystem No. NodeB ID NodeB name Cell ID Cell name" from 135 sheet and :

"Start Time Period NE Name BSC6900UCell BSC6900UCell cellID DLSPEECH(none)"

from the sheet1 sheet for this cellid value to target format sheet ...

hope its clear for you now ;;;; your fast respose will be highly appreciated !

attached is the workbook in question .