Using VBA to set the value of a specific cell in a different sheet using relative reference to active cell in current sheet

0
votes

E3 is a dropdown list of company names and on the same sheet, I have a table where users will be inputting information for 5 attributes. I am trying to write a macro that will take the user inputs and copy them over to a different sheet with a matrix where column A is the company name and column B to column F are the input values for the 5 attributes. I wrote the below code for it but it's not running...

Private Sub PopulateValue()
    Dim x As Integer
    Dim y As Integer
    x = WorksheetFunction.Match(Sheets("New Client Report").Range("E3"), Sheets("Client Report Remarks").Range("A:A"), 0)
    y = WorksheetFunction.Match(ActiveCell.Offset(-1, -1).Address, Sheets("Client Report Remarks").Range("A1:G1"), 0)
    ActiveCell.Offset(-1, 0).Select
    Selection.Copy
    Sheets("Client Report Remarks").Cells(x, y).PasteSpecial xlPasteValues
End Sub

Here I am trying to identify the row and column in the Remarks sheet that correspond to the company (E3) and attribute of the cell with input information (ActiveCell.Offset(-1,-1).Address). The active cell is the cell below the one with the input info, and the attribute names are in the cell one column left of the input cell

What's wrong with this code? Sorry if my codes look ridiculous, I'm totally new to VBA programming

1
excelvba

1 Answers

1
votes
Private Sub PopulateValue()
    Dim x, y 'using Variant to allow for catching errors from Match()
    Dim sht as WorkSheet

    Set sht = Sheets("Client Report Remarks")
    x = Application.Match(Sheets("New Client Report").Range("E3"), sht.Range("A:A"), 0)
    y = Application.Match(ActiveCell.Offset(-1, -1).Address, sht.Range("A1:G1"), 0)
          '?? do you mean ActiveCell.Offset(-1, -1).Value here ??

    If Not IsError(x) And Not IsError(y) Then
        sht.Cells(x,y).Value = ActiveCell.Offset(-1, 0).Value
    Else
        MsgBox "Couldn't locate copy destination!"
    End If    

End Sub