Help with updating transaction entries

[haas786]

Hi all! I was wondering if someone can help me out with this problem:

I have a worksheet in Excel onto which I export Claims information for
our company. There are 10 columns (fields) on this sheet, with the
last column labeled as "Latest?" - the way things work, a person
updates a Claims transaction and exports it to this sheet. We use this
sheet to see any or all updates made to our Claims transactions,
however, we need to see which one is the latest. So, let's say Claim
transaction ARGENT-Claim07 gets updated (one or two fields are
changed); the user makes the changes on another sheet, presses a
Export macro button, and everything gets copied over to this sheet.
What I also need done is to have the "Latest?" column say "Yes" to
this updated transaction and change the "Yes" to a "No" for the
previous ARGENT-Claim07 transaction. Can someone help please?

Sorry if this was confusing - please let me know if you have any
questions and I'll do my best to answer. Thanks in advance!

 

[merjet]

Assume the following. "Sheet1" is the one with "Latest?" in column 10
and the data starts in row 2 (row1 is a header). "Sheet2" has the new
info in row 1. Both have the same kind of info in columns 1-9, with
column 1 being a claim ID.

Sub Update()
Dim iRow As Long
iRow = Sheets("Sheet1").Range("A1").End(xlDown).Row
For Each c In Range("A2:A" & iRow)
If Sheets("Sheet2").Range("A1") = c Then
For iCol = 2 To 9
c.Offset(0, iCol - 1) = Sheets("Sheet2").Cells(1, iCol)
Next iCol
c.Offset(0, 9) = "Yes"
Else
c.Offset(0, 9) = "No"
End If
Next c
End Sub

Hth,
Merjet

 

[merjet]

Of course you could use:
Sheets("Sheet2").Range("A1:I1"). Copy c
instead of the inner loop.

Hth,
Merjet

 

[haas786]

Assume the following. "Sheet1" is the one with "Latest?" in column 10
and the data starts in row 2 (row1 is a header). "Sheet2" has the new
info in row 1. Both have the same kind of info in columns 1-9, with
column 1 being a claim ID.

Sub Update()
Dim iRow As Long
iRow = Sheets("Sheet1").Range("A1").End(xlDown).Row
For Each c In Range("A2:A" & iRow)
If Sheets("Sheet2").Range("A1") = c Then
For iCol = 2 To 9
c.Offset(0, iCol - 1) = Sheets("Sheet2").Cells(1, iCol)
Next iCol
c.Offset(0, 9) = "Yes"
Else
c.Offset(0, 9) = "No"
End If
Next c
End Sub

Hth,
Merjet

Merjet,

Thanks for helping but this didn't work. It assigned a "No" to all my
prexisting deals. All I want a "No" assigned to is the previous deal
in Sheet1 which I updated and exported from Sheet 2.

In other words, let's say in Sheet1 I already have in column A "ARGENT-
Claim07"; in Sheet2, I update the 9 columns for ARGENT-Claim07. Then I
hit "Update" in Sheet2 - the macro copies the entire row (or rows)
into the next empty row in Sheet1 and makes sure to put a "Yes" in the
Latest? column. It then looks for the same transaction number (ARGENT-
Claim07) in the entire database above and puts a "No" for any or all
entries for ARGENT-Claim07.

Please let me know if you need further explanation. Thanks for your
help in advance.

 

[merjet]

It seems you want to add a new line with the updated info (with
Latest? column = Yes) and leave any old lines with the same claim ID
(with Latest? column = No).
Is that correct?

Hth,
Merjet