Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: VBA get value of offset column based on Target Column

  1. #1
    Join Date
    13th September 2005
    Posts
    66

    VBA get value of offset column based on Target Column

    Below is a code that I have altered from an OzGrid July newsletter. I am trying to alter it to my needs. My hang up is in the .Offset (0,8)
    My Target is on Sheet1 my table is on worksheet "Standards"
    I want the .Offset to be =Data,Column()+6 of my Target.
    If my Target is Sheet1 C3 then (after all criteria are met) go to the C Column on the worksheet "Standards" and offset 6 columns give me the result.

    VB:
    Private Sub Worksheet_Change(ByVal Target As Range) 
        Dim wSht As Worksheet 
        Dim vResult 
         
        Set wSht = Worksheets("Standards") 
         
        If Not WorksheetFunction.CountIf(wSht.Range("Data"), Target) > 0 Then 
             
            MsgBox "Why you stupid TOAD. I outta beat you. That is not a valid mold name" 
            Exit Sub 
        Else 
             
            If WorksheetFunction.CountIf(wSht.Range("Data"), Target) > 0 Then 
                With wSht.Range("Data") 
                     
                    vResult = .Find(What:=Target, After:=.Cells(1, 1), _ 
                    LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ 
                    SearchDirection:=xlNext, MatchCase:=False).Offset(0, 8) 
                End With 
                If vResult = "Y" Then 
                    MsgBox "You are so darn good" 
                    Exit Sub 
                Else 
                    If vResult <> "Y" Then 
                        MsgBox "You Suck" 
                        Exit Sub 
                    End If 
                End If 
                 
            End If 
             
        End If 
         
         
    End Sub 
    
    
    Last edited by Brians12; February 6th, 2006 at 02:24.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    8th September 2004
    Location
    Northampton, England
    Posts
    2,868

    Re: VBA get value of offset column based on Target Column

    Brians12,

    At the moment your code is trying to find the value of Target within the range Data and then, when it finds it, it's setting the value of vResult to whatever value is 8 columns to the right. You seem to want to return the value 6 columns to the right, so is it as simple as changing the Offset arguments to (0, 6) or am I missing something?

    By the way, there is no need to repeat the (opposite) conditions of an IF statement within the ELSE part of the conditional coding as this is automatically assumed. Hence (without any other changes) your code
    VB:
    If Not WorksheetFunction.CountIf(wSht.Range("Data"), Target) > 0 Then 
         
        MsgBox "Why you stupid TOAD. I outta beat you. That is not a valid mold name" 
        Exit Sub 
    Else 
         
        If WorksheetFunction.CountIf(wSht.Range("Data"), Target) > 0 Then 
            With wSht.Range("Data") 
                 
                vResult = .Find(What:=Target, After:=.Cells(1, 1), _ 
                LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ 
                SearchDirection:=xlNext, MatchCase:=False).Offset(0, 8) 
            End With 
            If vResult = "Y" Then 
                MsgBox "You are so darn good" 
                Exit Sub 
            Else 
                If vResult <> "Y" Then 
                    MsgBox "You Suck" 
                    Exit Sub 
                End If 
            End If 
             
        End If 
         
    End If 
    
    
    could become, without any change of process
    VB:
    If Not WorksheetFunction.CountIf(wSht.Range("Data"), Target) > 0 Then 
        MsgBox "Why you stupid TOAD. I outta beat you. That is not a valid mold name" 
        Exit Sub 
    Else 
        With wSht.Range("Data") 
            vResult = .Find(What:=Target, After:=.Cells(1, 1), _ 
            LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ 
            SearchDirection:=xlNext, MatchCase:=False).Offset(0, 8) 
        End With 
        If vResult = "Y" Then 
            MsgBox "You are so darn good" 
            Exit Sub 
        Else 
            MsgBox "You Suck" 
            Exit Sub 
        End If 
    End If 
    
    
    Regards,
    Batman.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    13th September 2005
    Posts
    66

    Re: VBA get value of offset column based on Target Column

    Batman,

    Thank you as you can tell I am fairly new to the VBA world but loving the power of it. I understand how the .Offset is working within the "With" statement, but I am wanting a dynamic offset based on the input of the Target.

    I am using the message boxes as test fields for the IF statements eventually the test boxes are going to be replaced with other functions.

    And Thank You VERY much on simplifying the code.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    8th September 2004
    Location
    Northampton, England
    Posts
    2,868

    Re: VBA get value of offset column based on Target Column

    Brians12,

    Could you perhaps give me a bit more information about what you mean by "I am wanting a dynamic offset based on the input of the Target"? If it is possible for you to upload a small sample of your spreadsheet - that would help as well.

    I can't determine from your code whether the worksheet range "Data" contains just 1 column or multiple columns. At the moment, your code will find whatever the value is in Target within the range Data, and from the point at which it finds it, it will move 8 columns to the right and check whether the value in that location = Y. If Data contains more than 1 column this may be what you mean by "dynamic".

    Or is it that the number of columns to move to the right is determined by the column in which Target (i.e. the cell that was changed) was found? If this is the case, use the column number property of Target, i.e.
    VB:
    .Offset(0, Target.Column) 
    
    
    or, if the number of columns to move is based on the value in the cell changed
    VB:
    .Offset(0, Target.Value) 
    
    
    Regards,
    Batman.
    Last edited by Batman; February 7th, 2006 at 04:45.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    13th September 2005
    Posts
    66

    Re: VBA get value of offset column based on Target Column

    Batman,

    You are so very close to what I need. I am wanting the .offset in my Data Table to be based off of the Column that the Target is in.

    Here is what is happening when I enter in the line you gave me. If my Target is in Column C on Sheet1 This is returning the value of Column D in my Data Range. If my Target is in Column D of Sheet1 then this is returning the value of ColumnE in my Data Range. (NOTE: My Target and My Data range are on two different worksheets so they don't intersect)

    VB:
    With wSht.Range("Data") 
        vResult = .Find(What:=Target, After:=.Cells(1, 1), _ 
        LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ 
        SearchDirection:=xlNext, MatchCase:=False).Offset(0, Target.Column) 
    End With 
    
    
    Is there anyway to control the offset? I have tried:

    VB:
    .Offset(0, Target.Column(0,3)) 
     'and
    .Offset(0, Target.Column(3)) 
    
    
    But niether of them worked

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    8th September 2004
    Location
    Northampton, England
    Posts
    2,868

    Re: VBA get value of offset column based on Target Column

    If you change a value in column C of Sheet1 then the _Calculate event's Target variable will have a column property (accessed using Target.Column) of 3 (column C = 3, D = 4, etc). If you want to use that column number you can replace a hard-coded number with Target.Column.

    You can also perform calculations on top of that, so if you wanted to take Target's column (e.g. the change was in column C so Target's column number is 3) and add 3 to it you can use Target.Column + 3, e.g.
    VB:
    .Offset(0, Target.Column + 3) 
    
    
    I'm not quite sure what the correlation is between your values changed in Sheet1 (i.e. picked up by the value in Target) and the column number in the Data range in your other sheet. Are you able to upload a small sample?

    Regards,
    Batman.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    13th September 2005
    Posts
    66

    Re: VBA get value of offset column based on Target Column

    Batman,

    YOU ROCK!!!!

    That last one did it! I really thought that I had tried that line before I posted but I must have done something wrong. This time it gave me the desired result.

    I can not thank you enough.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    8th September 2004
    Location
    Northampton, England
    Posts
    2,868

    Re: VBA get value of offset column based on Target Column

    You're welcome!

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 1
    Last Post: June 1st, 2007, 23:05
  2. Ref Target Range Column To Sum
    By stevekirk in forum EXCEL HELP
    Replies: 20
    Last Post: September 24th, 2006, 23:16
  3. Returning Row/column From Target Value
    By heil002 in forum EXCEL HELP
    Replies: 2
    Last Post: September 24th, 2006, 16:59
  4. Replies: 12
    Last Post: August 5th, 2006, 00:24
  5. Replies: 5
    Last Post: August 7th, 2003, 22:39

Bookmarks

Posting Permissions

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