vba/macro to Find a line on another sheet and replace information in just 1 cell in that line

[woodyp]

Hi

I am pretty new to Macro's/Vba etc and need a little help with the following if possible please.

I have a sheet where a user enters simple information to 'Book out' a specific piece of equipment that has a unique 'Item No."
when the user clicks the 'Book Out' button, all the information (shown just above the blue bar below) is copied and pasted as values into the correct position on this sheet. . . All this works good so far..

Before this happens though I would like to updated part of a record that already exists on another sheet with the 'Item No.' (A)

on the image below, A is the Item No. (which is a unique number on another sheet, but not neccesarily on this sheet).
and B is the infomation which will be copied. . . . again, no probs so far..

This is where I Need the help . . .
In the 'Equipment' sheet, i need search and find the unique Item No. (A) and replace the information in (B) with the information I have copied to the buffer... i.e. change the 'Missing' to 'Disposed'.

Is there a simple piece of coding that could help me with this??

Thanks in advance

 

[Cyb3r_Ang3l]

If I understand your problem correctly, then this should help

Sub find_Unique_Item(ItemText): i = 1
Do Until i > 1000
i = i + 1
If Cells(i, 2) = ItemText Then                     'Change 2 to whichever column the Unique Ids are in
    Cells(i, 8) = BufferText: GoTo earlyOut  'Change 8 to whichever column you want the output in
End If
Loop
earlyOut:
End Sub

 

[mumps]

It is difficult to tell in which columns and rows your data is located. The macro assumes the following:
-the Item Number is in cell B6 and the Status is in cell H6
-the Item Numbers in the Equipment sheet are in column B
-the Status will be copied to column H in the Equipment sheet
Change the text in red to suit your needs.

Sub CopyStatus()
    Dim desWS As Worksheet, item As Range
    Set desWS = Sheets("Equipment")
    Set item = desWS.Range("B:B").Find(Range("B6").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not item Is Nothing Then
        desWS.Range("H" & item.Row) = Range("H6")
    End If
End Sub

The code should be placed in the macro that is executed when you click the Book Out button.

 

[woodyp]

Thank You *Cyb3r_Ang3l and mumps. . . I suppose I should have made it clearer by including the Columns and Row numbers eh ...

Anyway, I will try both solutions and see which one works best for me.

Thank you both again

​

 

[woodyp]

I tried mumps version first as my brain found it slightly easier to follow & understand what the code was doing

It worked perfectly so I have gone with that

Thanks again to you both

 

[mumps]

You are very welcome.