VBA Scipt to Input Value Next To A Value In A Database

[timpickup]

Hi All,

I am trying to create a VBA code to do the following task:

1. Look up a value on active sheet.
2. With that value, look in coloum "B" of sheet "Database".
3. Change the Value of a Cell to the Time in the same row but a different column on that sheet.

I have this so far, but it goes to the other sheet and then just inputs the Time into the original sheet instead of the updating the cells in the Database.

Private Sub CommandButton2_Click()

ref_number = Range("B9")

Sheets("Database").Activate

Last = Cells(Rows.Count, "B").End(xlUp).Row
For i = Last To 1 Step -1
If (Cells(i, "A").Value) = ref_number Then
Cells(i, "G") = Time
End If
Next i

End Sub

Can anyone offer any guidance regarding where I am going wrong? Would be much appreciated.

Thankyou

 

[Norie]

Perhaps

Private Sub CommandButton2_Click()
Dim Res As Variant

    ref_number = Range("B9")
  
    With Sheets("Database")
        Res = Application.Match(ref_number, .Range("B:B"), 0)
        If Not IsError(Res) Then 
            Cells(Res, "G") = Time
        End If
    End With

End Sub

 

[timpickup]

It doesn't appear with an error message, but nothing happens...

 

[Norie]

Is it definitely column B on 'Database' you want to look in?

I ask because in the original post you said column B but in the code you were looping through column A.

Also, does the data type of ref_number match the data type of the values in column B (or is A.).

 

[timpickup]

Yeah it is B, I made an error in my original post. I set both data types as Number and this still has not worked

 

[My Aswer Is This]

Try this:

Sub New_One()
Dim ref_number As Integer
ref_number = Range("B9").Value
Sheets("Database").Activate
Last = Cells(Rows.Count, "A").End(xlUp).Row
 
    For i = 1 To Last
        If Cells(i, "A").Value = ref_number Then
            Cells(i, "G") = Time
        End If
    Next i

 End Sub

it's good to see your trying to learn Vb. You had several errors in your code.

 

[Norie]

There's a very small typo - a missing . in front of Cells(Res, "G").

Here's the fixed code with a bit of tidying up as well.

Private Sub CommandButton2_Click()
Dim Res As Variant

    ref_number = Range("B9").Value
  
    With Sheets("Database")
        Res = Application.Match(ref_number, .Range("B:B"), 0)
        If Not IsError(Res) Then
            .Cells(Res, "G").Value = Time
        End If
    End With

End Sub

 

[timpickup]

Thank You so much Norie!

Works perfectly, would it be possible to have the same formula for a different button to delete the whole row if it is a match... Or will I have to post a new thread?

 

[Norie]

This will delete the row if found.

Private Sub CommandButton3_Click()
Dim Res As Variant

    ref_number = Range("B9").Value
  
    With Sheets("Database")
        Res = Application.Match(ref_number, .Range("B:B"), 0)
        If Not IsError(Res) Then
            .Rows(Res).Delete
        End If
    End With

End Sub