Accessing a sheet with different vars

[lippa]

Hello,

Im using Excel 2003 with XP.

I need to write some VBA code to update specific cells within a worksheet and am not sure if it should be a sub or a function (I think function and will refer to it as such, but could well be wrong)

The function will be called from a sub where all the variables are declared.

The function needs to update a specific worksheet which will be defined by a string var P_Name.

It will then need to find the location of 1-4 specific attributes in the range a1:g15 (defined by the constant string variables Name, Age, Location and Join) and update the cell to the right with the variables New_Name, New_Age, New_Location and New_Join)

All the variables will have been declared and their values updated in the various subs that call the function.

I dont know if this helps but I imagine it would look something like this :-

Function Update_Sheet(P_Name,Attribute,NewValue)

Dim Search_Range as Range
Dim Attribute_Location as Range

Sheets(P_Name).Select

Search_Range = Range("A1:G15")

     'Attribute_Location = Find Attribute in Search_Range
     'Range(Attribute_Location).offset(0,1).value = NewValue

End Function

Where Attribute would be 1 of the 4 possible atrributes to update and NewValue its new value.

I realise doing it this way would mean having to call the function 1-4 times depending on how many attributes need updating and not sure if i should do it that way.

Thanks in advance for any help

 

[AlphaFrog]

Link:Subroutine versus Function

Cannot use Attribute as a variable name. It is a reserved name in VBA.

Here's a simple example of using the .Find method to find a match within a range. You will, no doubt, have to modify the code to suit your needs.

Sub Test()
    If Update_Sheet("Sheet2", "Jim", "Bob") Then
        MsgBox "Update successful."
    Else
        MsgBox "Update failed."
    End If
End Sub


Function Update_Sheet(P_Name As String, Attrib, NewValue) As Boolean

    Dim Found As Range
    
    On Error GoTo Failed
    
    Set Found = Sheets(P_Name).Range("A1:G15").Find(What:=Attrib, _
                                                    LookIn:=xlValues, _
                                                    LookAt:=xlWhole, _
                                                    SearchOrder:=xlByRows, _
                                                    SearchDirection:=xlNext, _
                                                    MatchCase:=False)
    If Not Found Is Nothing Then
        Found.Offset(, 1) = NewValue
        Update_Sheet = True
    End If
    Exit Function
    
Failed:
    Update_Sheet = False
    
End Function

 

[lippa]

Thanks very much for your help