PDA

View Full Version : Change Col diff into Col letter



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!

xld
03-28-2008, 08:07 AM
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
03-28-2008, 08:31 AM
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
03-28-2008, 08:36 AM
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
03-28-2008, 08:51 AM
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.