A Macro to replace a matching row in a block of cells

[ravikr2678]

Hello Team,

I'm struck trying to write a macro, Need some help.

I have a row of excel cells ( Say target row )
I have a Block of excel cells ( say from A1 to D10 )

I'm trying to write a macro which picks my target row and searches in the block of cells for a matching row, if it finds: replace that row in block with target row.

Also, this macro should trigger by itself when even we open that excel sheet.
(Unlike manually hitting a macro button )
Can some one help me Please. ?

Thanks in advance.
Ravi

[mdmackillop]

Can you define "matching row"? From what you have written it sounds like you are replacing like with like. Maybe a sample would help.

[ravikr2678]

Yeah right, I'm replacing like with like, below is the sample.

ProjectName Startdate Enddate

'XYZ 12/10/97 04/10/07'
'ABC 09/01/01 05/06/07'
'PQR 06/02/04 06/04/09'
.
.
.
.
.

Target Row is 'ABC 09/01/01 05/06/07' (Second row in the block)
(Target row is some thing that i get from Xcelsius component output though )

Basically, It finds all column values matched, thus it should replace.

Let me know if you need more details.

Thanks
Ravi

[mdmackillop]

Still don't see a result in this. Can you post a workbook showing Before and After? Use Manage Attachments in the Go Advanced reply section.

[Aussiebear]

Hi Ravi, Can you clear up some of the confusion here please?

You have indicated that you have a Range A110 which contains data similar to the Project Name/ Start Date/ End Date format supplied and you want it matched with a "Target Row" set of data.

Are you trying to match just Column A values or a combination of Column values?

Basically, It finds all column values matched, thus it should replace.

Did you mean a Row value within a Column,rather than all Column Values?

Target Row is 'ABC 09/01/01 05/06/07' (Second row in the block)

Did you mean here that, in this case the output string data matches the data in Row 3, since the column headings would be row 1?

[ravikr2678]

Hello,

Thanks for a quick reply, Yeah I'm trying to match just Column A values,
If it matches one column value in data, then it's good.

In the above case, If the output string data matches the data in Row 3, first column, the target row (whole row) should be replaced with matched row with in the block.

So, basically matching one column is good for replacement.

[mdmackillop]

I'm trying to write a macro which picks my target row: What does this mean; how do we interpret this?
If the output string data matches the data in Row 3: What output string?
Target row is some thing that i get from Xcelsius component output though: What do you do with it? How can a worksheet open event access this data?

[ravikr2678]

I'm sorry, I must have explained in more sensible manner.
OK, I'm going to explain based on the excel print screen in this thread posted by "Aussiebear" ( Thanks Aussiebear )
Please scroll above.. ( I could not get that Manage attachment thing in Go advance filter)

That excel sheet has 4 rows and 3 columns,
first row contains headings, and 2nd, 3rd and 4th are data.

I have another row of data, say in 10th row of the same excel sheet that is of similar format 'i.e' Project name, StartDate and EndDate. ( I called this row as target row previously)

Now, I'm trying to work with a macro which will compare data in 10th row to all the rows in the data block(2nd, 3rd and 4th)

It's OK, even if it matches just the first column in data block, but it should replace the entire matching row in data block with the target row (10th row)

Please don't bother about that Xcelsisus stuff what i wrote previously, which will only confuse us more.

And please let me know if i need to explain it any further.

Thank you.

[mdmackillop]

[VBA]
Option Explicit
Private Sub Worksheet_Activate()
UpDates
End Sub
Private Sub UpDates()
Dim TgtRow As Long, i As Long
TgtRow = 10
For i = 2 To TgtRow - 1
If Cells(i, 1) = Cells(TgtRow, 1) Then
Cells(TgtRow, 1).EntireRow.Copy Cells(i, 1)
End If
Next
End Sub

[/VBA]

[ravikr2678]

Thank you so much for the code, Appreciate it.

In case if i need to invoke this code when a value with in a cell changes,
'i.e' I have a cell A1, when ever the value within cell A1 changes, this macro should trigger, Can we make this happen ?

Not sure, if i can ask this question in the same thread.

But i really appreciate your help.

Thanks a ton.

[mdmackillop]

[VBA]
Option Explicit

Private Sub Worksheet_Activate()
UpDates
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A9")) Is Nothing Then
UpDates
End If
End Sub

Private Sub UpDates()
Dim TgtRow As Long, i As Long
Application.EnableEvents = False
TgtRow = 10
For i = 2 To TgtRow - 1
If Cells(i, 1) = Cells(TgtRow, 1) Then
Cells(TgtRow, 1).EntireRow.Copy Cells(i, 1)
End If
Next
Application.EnableEvents = True
End Sub
[/VBA]

[ravikr2678]

thanks so much for the code.
It's working for me.
However, There is some other service which is overriding the data with in those cells which our macro uses as a result i don't get the correct data.

I was wondering if we can pause the macro for 3 seconds and run, so that any services which has to be executed will be completed beforehand, and our macro logic can be applied on the new data.

Can we pause the macro ( just the macro, not the complete excel) for some time and Run, Do we have any functionality as such ?

[mdmackillop]

You can use OnTime to run macros at set times or after intervals, or you can use DoEvents to allow outside procedures to run.

[ravikr2678]

Thanks so much for the support.

This forum is no less to a magic wand to produce wonderfull code.
Appreciate it.

Regards
Ravi