Results for "comparision copy macro"

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 .

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,

Hi ! Lisa Nguyen,

Here, your Excel File is back, with automatic conversion.

If you agree, you can display the corresponding color with your VBA Module3. Sub RGB_TO_HEX_COLOR()

See my Note in Sheet1.

Breaking News,

Excel File Version2 is coming Now.

Best Regards.

Re,

A Power Query proposal to study.

Regards.

Hello,

Understand you cannot attach your workbook ... for confidential reasons ...

However, the one thing you could do is insert the code you have created ...

It might be that ... just by looking at your macro ... a solution might pop up ....

Hello and welcome,

try this, select the data and execute this macro.

Sub createcolumnchartwithselection()

    Set ch = ActiveSheet.Shapes.AddChart2(366, xlColumnClustered)
    ActiveSheet.ChartObjects(ch.Name).Activate
    ActiveChart.ChartArea.Select
    ActiveChart.ChartStyle = 201
End Sub

Hi there, i am new to vb excel and i need help.

What would be the macro for making excel create time series chart if two columns are selected where the left column is dates and right column is data

Good evening Lisa,

I propose you here a fully automated version of the macro, meaning that :

When you modify the hexa color, it will both change the RGB values accordingly and change the color of the colored cell automatically too.

When you modify one of the RGB value, it will both change the Hexadecimal value accordingly and change the color of the colored cell automatically too.

No need for any button to apply the tranformations.

Wow really thanks for what you did! I saw the message you left on the excel file!

I will now try to analyze and understand what you have written in the code.

Thank you again and if you manage to switch from RGB to HEX it would be amazing !

Hello,

For my internship, I have to create an excel macro to convert a color value in hexa code to rgb and vice versa.

I have already done a lot of research on google and in general I find excel formulas (use of boxes in the formulas to convert) instead of a VBA code. In addition, it takes usually 2 tables, one from hex to rgb and another from rgb to hex.

But I'm looking for an automatic conversion, that is to say to make a modification of the hex value, the rgb value is updated and vice versa, all in the same table.

All these data are organized by columns and you need a third one to display the corresponding color.

For the moment I have fulfilled my specifications but it is not optimal, I have indeed two tables. I call for your help today to help me to optimize my code.

Tell me if it's not clear haha, I did the best I could. I put in attachment the excel file as is.

Thanks in advance !

Jean-Eric

I love you, I love you, I love you...

Thank very much.

Much thanks to you and your patience, working with me especially with my English language.

You should become my excel teacher.

Greatly appreciated

Thank you again. God Bless You.

Hello Jean-Eric,

Thank you for your swift response.

Please view the sample file below.

Please note: the raw data is on cell "A1" and

The extracted data example is on cell "N" and cell "O"

There are many excel workbook I need to extract data.

How can I create a universal code to extract each one.

Thanks in advance. Greatly appreciated.

Hello,

Please attach a small file at your request.

Regards

Hello Fans.

Currently I have Many Raw Data as an example below to section out.

What's beautiful, all the following data are similar:

Here is an example in row "1", in column "A" only :

0: {c: 9, close: 9, yield: null, referenceDate: null, date: "1997-03-01T00:00:00", …}

In this Case: How can I Keep the values of The following:

9 1997-03-01

I have multiple data similar like above. The comparison with the others are, values and dates.

What is the effective way to get the data?

Or what code can I use? How do you want me to proceed?

Thanks in advance