column number in VB

[dark11984]

Hi,
I am trying to find the column number of cell(1,j) if j is equal to a month/year selected in a combo box.

Thanks,

 

[machopicho]

Try:

Private Sub ComboBox1_Click()
j = ComboBox1.ListIndex
Cells(1, j + 1).Select
End Sub

 

[dark11984]

Sorry not quite what i was looking for, i probably should've explained it a bit better. We currently run a macro each month but it has only been setup to run this FY and will run out next month.

So i am trying to write a new code that we will be able to use going forward without having to amend each FY.

This is the current code:

Sub ProcessData()
'
    Dim Cl08 As Integer
    Dim Msb As Integer
    Dim Month As Variant
 
    CopyFailuresToCalculations
 
    Sheets("Instructions").Select
 
    Month = Cells(26, 3)
 
    Cl08 = 87 'Starting column number for Year 2008
 
    Select Case Month
 
        'Starts from Jul 09 to Jun 10 ONLY !!!
 
        Case "Jul 09"
            CopyCalculationsToLRAudits (Cl08 + 2)
 
        Case Is = "Aug 09"
            CopyCalculationsToLRAudits (Cl08 + 4)
 
        Case "Sep 09"
            CopyCalculationsToLRAudits (Cl08 + 6)
 
        Case "Oct 09"
            CopyCalculationsToLRAudits (Cl08 + 8)
 
        Case "Nov 09"
            CopyCalculationsToLRAudits (Cl08 + 10)
 
        Case "Dec 09"
            CopyCalculationsToLRAudits (Cl08 + 12)
 
        Case "Jan 10"
            CopyCalculationsToLRAudits (Cl08 + 14)
 
        Case "Feb 10"
            CopyCalculationsToLRAudits (Cl08 + 16)
 
        Case "Mar 10"
            CopyCalculationsToLRAudits (Cl08 + 18)
 
        Case "Apr 10"
            CopyCalculationsToLRAudits (Cl08 + 20)
 
        Case "May 10"
            CopyCalculationsToLRAudits (Cl08 + 22)
 
        Case "Jun 10"
            CopyCalculationsToLRAudits (Cl08 + 24)
 
    End Select
    Application.CutCopyMode = False
    Sheets("Instructions").Select
    Cells(30, 1).Select
 
    Msb = MsgBox("LR audits have been successfully processed !", vbOKOnly)
End Sub

This is the new code i am workign on and i can't get it to work.

Sub ProcessData_New()
    
    Dim Msb As Integer
    Dim Month As Variant
    
    CopyFailuresToCalculations
    
    Sheets("Instructions").Select
    
    Month = Cells(26, 3)
    
    Dim j As Integer
    Dim ColumnCount As Integer
    
    j = 1
    ColumnCount = Cells(1, j).Value
    
    Do While Cells(2, j).Value <> ""
        If Cells(2, j).Value = Month Then
            CopyCalculationsToLRAudits (ColumnCount + 1)
               
            
        Else
            Msb = MsgBox("failed !", vbOKOnly)
        End If
    j = j + 1
    Loop
            
Msb = MsgBox("LR audits have been successfully processed !", vbOKOnly)
End Sub

Any help would be much appreciated.

Thanks.

 

[Rastaman]

Isn't j your column number?

CopyCalculationsToLRAudits (j + 1)


Rick