Syntax for range (column) in vbs
Results 1 to 3 of 3

Thread: Syntax for range (column) in vbs
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2018
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Syntax for range (column) in vbs

    My code so far is:
    Code:
    Option Explicit
    
    Private Sub Worksheet_Calculate()
    
    Dim Rng1            As Range
    Dim Value           As Double
    Dim Prompt          As String
    Dim Title           As String
    
        'Put the range you want to test here
        Set Rng1 =Range("T14")
        
        'Put the target value here
        Value = "1"
    
        'Put the message (prompt) of the message box (pop up) here
        Prompt = "Sub group must be part of the Group"
    
        'Put the title of the message box (pop up) here
        Title = "Group/ Sub Group mismatch"
    
        If Rng1.Value = Value Then
                MsgBox Prompt, vbInformation, Title
        End If
    
    End Sub
    and it works well when the range is "T14", but I want to module to check any (all) cells in column T. I have tried "$T" and other variations, but they all fail. Need the syntax for column T as the range.


    Would appreciate any help.

    Peter.
    Last edited by RoryA; Apr 19th, 2018 at 06:36 AM. Reason: Code tags

  2. #2
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,082
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Syntax for range (column) in vbs

    Try (untested)

    Code:
    Set Rng1 =Range("T:T")

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,942
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Syntax for range (column) in vbs

    Hi & welcome to the board.
    How about
    Code:
    Private Sub Worksheet_Calculate()
    
    Dim Cl              As Range
    Dim Value           As Double
    Dim Prompt          As String
    Dim Title           As String
    
        
        'Put the target value here
        Value = "1"
    
        'Put the message (prompt) of the message box (pop up) here
        Prompt = "Sub group must be part of the Group"
    
        'Put the title of the message box (pop up) here
        Title = "Group/ Sub Group mismatch"
    
        For Each Cl In Range("T1", Range("T" & Rows.Count).End(xlUp))
          If Cl.Value = Value Then
             MsgBox Prompt, vbInformation, Title
             Exit For
          End If
       Next Cl
    
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •