VBA get value of offset column based on Target Column

[Brians12]

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.

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 

[Batman]

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

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

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.

[Brians12]

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.

[Batman]

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.

.Offset(0, Target.Column) 

or, if the number of columns to move is based on the value in the cell changed

.Offset(0, Target.Value) 

Regards,
Batman.

[Brians12]

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)

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:

.Offset(0, Target.Column(0,3)) 
 'and
.Offset(0, Target.Column(3)) 

But niether of them worked

[Batman]

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.

.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.

[Brians12]

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.

[Batman]

You're welcome!