VLookUp issue

That shouldn't be difficult. We can't really tell you what might be wrong with your formula without seeing it, and, if the formul looks OK, we'd need to see the data.

So, best bet, upload a sample workbook.

Regards, TMS

I got the VLookup function to work in one module, but doesn't work in another module of the same workbook :S

This works:

Sub Test()
    Dim testdate As Long
    
    Worksheets("Main").Activate
    testdate = CLng(Worksheets("Main").Range("D78").Value)
    
    ColumnSelection (testdate)
    
End Sub

Sub ColumnSelection(Sundate As Long)
    
    Dim weekNum As Variant
    
    Worksheets("Calendar 2013").Activate
    weekNum = WorksheetFunction.VLookup(Sundate, Range("A:B"), 2, False)
    
    MsgBox weekNum
       
    Worksheets("Main").Activate
    
    Dim weekNumRow As Variant
    Dim ColSelect As Range
    Dim i As Integer
    
    weekNumRow = Range("M4:BM4").Value
    
    'MsgBox weekNumRow(1, 8)
    
    For i = 1 To 53
        If weekNumRow(1, i) = weekNum Then
            Range("M4:BM4").Cells(1, i).Activate
            Set ColSelect = Range(ActiveCell.Offset(1, 0), ActiveCell.End(xlDown).Offset(-1, 0))
            MsgBox "Value is in column " & ColSelect.Address
            Exit For
        End If
    Next i
    
End Sub

But this doesn't:

Public Sub BossScript()

    Dim Sundate, fabDate, truncValue As Long
    Dim fabRange, whoRange, fracRange, truncRange As Range
    Dim whoValue, fracValue As Variant
    Dim w, f, t As Integer
    
    Worksheets("Main").Activate
    Sundate = CLng(Worksheets("Main").Range("A1").Value)
    
    Set fabRange = Range(Cells(5, 5), Cells(5, 5).End(xlDown))
    Set whoRange = Range(Cells(5, 11), Cells(5, 11).End(xlDown))
    Set truncRange = Range(Cells(5, 7), Cells(5, 7).End(xlDown))
    
    For w = 1 To whoRange.Rows.Count
        fabDate = CLng(fabRange.Cells(w, 1).Value)
        whoValue = whoRange.Cells(w, 1).Value
        truncValue = CLng(Int(truncRange.Cells(w, 1).Value))
        If whoValue > 0 Then
            If fabDate < Sundate Then
            ColumnSelection (fabDate)
            Else
            ColumnSelection (Sundate)
            End If
        End If
                    
        'MsgBox whoRange.Address
        'MsgBox whoValue
    Next w
    
    'whoRange = Range(K5, K5.End(xlDown)).Value
    
End Sub

Sub ColumnSelection(selectDate As Long)

    Dim weekNum As Variant

    Worksheets("Calendar 2013").Activate
    weekNum = WorksheetFunction.VLookup(selectDate, Range("A:B"), 2, False)
    
    MsgBox weekNum
       
    Worksheets("Main").Activate
    
    Dim weekNumRow As Variant
    Dim ColSelect As Long
    Dim i As Integer
    
    weekNumRow = Range("M4:BM4").Value
    
    'MsgBox weekNumRow(1, 8)
    
    For i = 1 To 53
        If weekNumRow(1, i) = weekNum Then
            Range("M4:BM4").Cells(1, i).Activate
            ColSelect = Range(ActiveCell.Offset(1, 0), ActiveCell.End(xlDown).Offset(-1, 0)).Column
            'MsgBox "Value is in column " & ColSelect.Address
            Exit For
        End If
    Next i
    
    If truncValue > 0 Then
        For t = 0 To truncValue - 1
            Range(Cells(w, 11)).Copy Range(Cells(w, ColSelect + t))
        Next t
    End If
End Sub

Help, please!

Thanks for letting us know. Glad you have a solution.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.