Solved: ID number

[sooty8]

Hi Everybody,

I have 30 sheets in a workbook all with different names in columnA in all the sheets have approx 400 ID Numbers for example WM 10389 could be on any sheet and any row. Sheet1 in column C holds all the ID Numbers Columns D and E hold data for these ID Numbers. The code below puts the info held on sheet1 Columns D & E against the ID Number when it finds it. My problem is that when the info in Columns D & E changes I don't want it to overwrite what is already entered against a particular ID number I want it to go to the next column. Any help and idea's would be much appreciated.

Cheers

Sooty8


Sub Macro3()
RowCount = 1
With Sheets("Sheet1")
Do While .Range("C" & RowCount) <> ""
ID = .Range("C" & RowCount)
Val1 = .Range("D" & RowCount)
Val2 = .Range("E" & RowCount)
For Each sh In ThisWorkbook.Sheets
If sh.Name <> "Sheet1" Then
Set c = sh.Columns("A:A").Find(what:=ID, _
LookIn:=xlValues)
If Not c Is Nothing Then
c.Offset(0, 1) = Val1
c.Offset(0, 2) = Val2
End If
End If
Next sh
RowCount = RowCount + 1
Loop
End With
End Sub

[figment]

this is untested, but give it a try

[vba]Sub Macro3()
Dim a As Long
RowCount = 1
With Sheets("Sheet1")
Do While .Range("C" & RowCount) <> ""
ID = .Range("C" & RowCount)
Val1 = .Range("D" & RowCount)
Val2 = .Range("E" & RowCount)
For Each sh In ThisWorkbook.Sheets
If sh.Name <> "Sheet1" Then
Set c = sh.Columns("A:A").Find(what:=ID, _
LookIn:=xlValues)
If Not c Is Nothing Then
a = 1
While c.Offset(0, a) <> "" And a < 253
a = a + 2
Wend
c.Offset(0, a) = Val1
c.Offset(0, a + 1) = Val2
End If
End If
Next sh
RowCount = RowCount + 1
Loop
End With
End Sub[/vba]
once you run out of colunms the code will keep overwrighting the last data set.

[sooty8]

Hi Figment

Thanks very much worked brilliantly at the first time of asking

Cheers

Sooty8