CODEWITHSUNDEEP
excelvbaoffset
user3386075
user3386075

Reputation: 1

Referencing cell in another worksheet

I can write the correct formula in Excel, but because I have so many IF statements, I have to put it into VBA. The formula in Excel is supposed to return a value from another worksheet ("Sheet2") based on a value (F5) selected on "Sheet1". Here is part of the Excel formula that was created (there are many more IF Statements):

IF($F$5="AOM",
   OFFSET('Sheet2'!B3,'Sheet2'!$B$1,1,1,1),
   IF($F$5 = "Mid Adj",
      OFFSET('Sheet2'!B3,'Sheet2'!$B$1,6,1,1),
      ""
     )
  )

Here is the If Statement part of the VBA I've created: 

Private Sub Worksheet_Change(ByVal Target As Range)

Dim dummyVar As String

If Target = Range("F5") Then

    If Range("F5").Text = "AOM" Then
        dummyVar = ProcAOM()

    ElseIf Range("F5").Text = "Mid Adj" Then
        dummyVar = ProcML()

For the Proc, I'm not sure how to set up the Offset formula that references another worksheet.

Upvotes: 0

Views: 9404

Answers (1)

PatricK
PatricK

Reputation: 6433

From what I can understand you are trying to achieve, this will be just for 1 cell? If so, you don't have to care about Worksheet_Change.

You can create a User Defined Function (UDF) for this purpose. Say BigIF with assumptions:

  • The Reference Cell does not change (B3 of Sheet2)
  • Row Offset is defined in a fixed cell (B1 of Sheet2)
  • Column Offset is determined in the code
  • Returns the value of the range offset from Reference Cell

Paste below code into a module and use it in worksheet just like a formula, but referencing the F5 of Sheet1 (doing this forces Excel to recalculate when F5 changes). ie. =BigIf('Sheet1'!$F$5)

Function BigIF(oRng As Range) As Variant
    Dim oWS As Worksheet, oRngRef As Range
    Dim lRowOffset As Long, lColOffset As Long, sID As String

    Set oWS = ThisWorkbook.Worksheets("Sheet2")
    Set oRngRef = oWS.Range("B3") ' Offset Reference
    sID = oRng.Text ' Sheet1!F5 value
    ' ROW OFFSET: Sheet2!$B$1 value
    lRowOffset = CLng(oWS.Range("B1").Value)
    ' COLUMN OFFSET: based on sID
    Select Case sID
        Case "AOM":         lColOffset = 1
        Case "Mid Adj":     lColOffset = 6
        '... Other Cases
        Case Else:          lColOffset = 0
    End Select
    BigIF = oRngRef.Offset(lRowOffset, lColOffset)
    Set oRngRef = Nothing
    Set oWS = Nothing
End Function

Of cause you can also use the Worksheet_Change event method, but more code.

Upvotes: 1

Related Questions