Djblois
03-28-2008, 07:28 AM
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!
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!