Forum Discussion

Excel's avatar
Excel
Iron Contributor
Aug 16, 2022
Solved

Query related to VBA code..

Hello Everyone,    I am trying to use For Each to get the range dynamically but getting a run time error . I am trying to get the Age row dynamically selected. is highlighted in yellow .   ...
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Aug 25, 2022

    Excel 

    Public Sub FunWithLogic()
    ' IF logic to determine age
    If ActiveCell.Value >= 90 Then
        MsgBox ActiveCell.Offset(0, -1).Value & " is 90 or older"
    ElseIf ActiveCell.Value >= 21 Then
        MsgBox ActiveCell.Offset(0, -1).Value & " is 21 or older"
    Else
        MsgBox ActiveCell.Offset(0, -1).Value & " is not allowed!"
    End If
End Sub
HansVogelaar's avatar
HansVogelaar
MVP
Aug 16, 2022

Excel 

lastCell is a row number converted to a string, for example "37".

Range("B2:" & lastCell) is equivalent to Range("B2:37")

That is not a valid range. Perhaps you wanted

Range("B2:B" & lastCell)

  • Excel's avatar
    Excel
    Iron Contributor
    Aug 25, 2022

    HansVogelaar 

    Hello Sir, 

    I have another question :

     

    In FunWithLoops in that when 1 press the play or run button the MsgBox was created and i can see that step by step like that " User is 21 or older " ETC . So My question is instead of user i want to see the username like " Jenny is 21 or older " or "Harry Not Allowed! " or "Charlie is 90 or older"

    etc

     

    Please help

     

    Here is a attached file

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Aug 25, 2022

      Excel 

      Public Sub FunWithLogic()
    ' IF logic to determine age
    If ActiveCell.Value >= 90 Then
        MsgBox ActiveCell.Offset(0, -1).Value & " is 90 or older"
    ElseIf ActiveCell.Value >= 21 Then
        MsgBox ActiveCell.Offset(0, -1).Value & " is 21 or older"
    Else
        MsgBox ActiveCell.Offset(0, -1).Value & " is not allowed!"
    End If
End Sub

Resources