Sleeper: Change Col diff into Col letter

[Djblois]

I have created a form that users use to do an automatic V-look up without having to open a file or to know how to use the formula. This only works for products and customers and warehouses. I am trying to make it even easier to use. First, I test to find what column has the code then I put the column difference in a label that the user can change using a scrollbar.

ex: If I am in column m and the code is in column l then the label would say -1. This is easy when it is only 1 or two columns away but if it was fifteen columns away then that would be a headache. You would need to count the column differnce and then make sure the col diff is correct. Is there any way I can get the label to say L for the column instead.

Here is the code that I am using:

First, test where the code is:

On Error GoTo nextstep1
    For intTestCol = -1 To -30 Step -1
        With ActiveCell.Offset(, intTestCol)
            If IsNumeric(.Value) Then
                If (Len(.Value) = 5 Or Len(.Value) = 6) And .Value = Int(.Value) Then
                    frmExtraAddTo.sbColDiff.Value = intTestCol
                    GoTo AddNextColumn
                End If
            End If
        End With
    Next intTestCol
nextstep1:
    For intTestCol = 1 To 30
        With ActiveCell.Offset(, intTestCol)
            If IsNumeric(.Value) Then
                If (Len(.Value) = 5 Or Len(.Value) = 6) And .Value = Int(.Value) Then
                    frmExtraAddTo.sbColDiff.Value = intTestCol
                    GoTo AddNextColumn
                End If
            End If
        End With
    Next intTestCol

Second, populate the label with the difference from the column you are in:

 With Me
        .obSelect1.Value = True
        .sbColDiff.Max = 40
        .sbColDiff.Min = -40
        .sbColDiff.Value = -1
        .lblColumnNumber.Caption = Me.sbColDiff.Value
       End With

Finally, I use the column difference in the v-lookup:

comAddProduct Selection, intColDiff
 
With rngAddto
        .FormulaR1C1 = "=VLOOKUP(RC[" & intColumnDiff & "],'[" & wbRef.Name & "]Products'!C1:C3,3,FALSE)"
        .Value = rngAddto.Value 'Changes Formula into Values
    End With

Thank you all for your assistance!

[Bob Phillips]

Daniel,

Quickly looking at the code, it looks all over the place to me.

You set the lbCoolumnNumber.Caption to the sbColDiff.Value, but in the line above you set sbColDiff.Value to -1.

In the third code snippet you use intColDiff, but in the fourth it is intColumnDiff.

I don't see what the first code snippet is doing, but where is IntCol(umn)Diff in there?

And why is subtarcting 12 from 21 any more difficult than subtracting 12 from 13?

[Djblois]

All the third code snippet is doing is passing that variable to the fourth code snipper:

Sub comAddProduct(ByVal rngAddto As Range, ByVal intColumnDiff As Integer)
'Adds Product name from Product Number from BusinessReportingReference.xls
    With rngAddto
        .FormulaR1C1 = "=VLOOKUP(RC[" & intColumnDiff & "],'[" & wbRef.Name & "]Products'!C1:C3,3,FALSE)"
        .Value = rngAddto.Value 'Changes Formula into Values
    End With
End Sub

The first code snipped is checking the columns before the active column and then the columns after the active column to see if it can find a column that might be a customer code. I added this so that the user most of the time does not need to click the scroll bar fifteen or so times. I just shortened that code a lil bit:

 On Error GoTo nextstep1
    For intTestCol = -1 To -30 Step -1
        With ActiveCell.Offset(, intTestCol)
            If IsNumeric(.Value) And Len(.Value) = 4 And .Value = Int(.Value) And .Value > 0 Then
                frmExtraAddTo.sbColDiff.Value = intTestCol
                GoTo AddNextColumn
            End If
        End With
    Next intTestCol
nextstep1:
    For intTestCol = 1 To 30
        With ActiveCell.Offset(, intTestCol)
            If IsNumeric(.Value) And Len(.Value) = 4 And .Value = Int(.Value) And .Value > 0 Then
                frmExtraAddTo.sbColDiff.Value = intTestCol
                GoTo AddNextColumn
            End If
        End With
    Next intTestCol

[Norie]

Daniel

I've got to agree with xld, that code is all over the place.

And the explanation of what you are actually trying to do isn't much clearer either.

[Djblois]

that is why I answered his questions. All I am trying to do is summed up in 6 steps:

1. Determine what column the user is in
2. Determine which column the code is in to look up
3. subtruact the result of step 1 from step 2 to give you the column Diff
4. Change that column difference to the letter the code is in (ex: -1 to A)
5. Allow the user to change A to B or any other column (in case the result of step 3 and 4 was incorrect)
6. Change the letter back into the Col Difference (In case the user changed the column letter)

Currenly, my code does steps 1-3. I need help with step 4 and 6. I know how to step 5. All I need help with is change the number to a letter and back.