VBA lesson 2 for loops

Hey All, I'm a C#/C++ programmer, and through knowldge of them I can get by in excel/vba, but i'm definitely no Excel Pro yet. I am however willing to learn, which is why I signed up here and browse these forums all day trying to answer each question. Through doing this I learn a lot, by seeing how I'm right, or how I'm wrong and learning from other answers.

Anyways, another thread from someone else has posted for a request to check dates within 3 months of the current date and update another cell's status to "Due Soon"/"Not Due Yet" etc...

Ive written the following

Code:

Sub CheckDates()

    Dim r As String
    Dim TargetNumMonth As Integer
    
    r = Left(Range("A1").Value, 3)
    
    Select Case r
    
        Case "Jan"
            TargetNumMonth = 1
            
        Case "Feb"
            TargetNumMonth = 2
            
        Case "Mar"
            TargetNumMonth = 3
            
        Case "Apr"
            TargetNumMonth = 4
            
        Case "May"
            TargetNumMonth = 5
            
        Case "Jun"
            TargetNumMonth = 6
            
        Case "Jul"
            TargetNumMonth = 7
            
        Case "Aug"
            TargetNumMonth = 8
            
        Case "Sep"
            TargetNumMonth = 9
            
        Case "Oct"
            TargetNumMonth = 10
            
        Case "Nov"
            TargetNumMonth = 11
            
        Case "Dec"
            TargetNumMonth = 12
               
        
    End Select
        
        
    Dim curDate As Date
    curDate = Now()
    Dim res As String
    res = Left(curDate, 1)
    
    Dim CurMonth As Integer
    
    Select Case res
    
        Case 1
            CurMonth = 1
            
        Case 2
            CurMonth = 2
            
        Case 3
            CurMonth = 3
            
        Case 4
            CurMonth = 4
            
        Case 5
            CurMonth = 5
            
        Case 6
            CurMonth = 6
            
        Case 7
            CurMonth = 7
            
        Case 8
            CurMonth = 8
            
        Case 9
            CurMonth = 9
            
        Case 10
            CurMonth = 10
            
        Case 11
            CurMonth = 11
            
        Case 12
            CurMonth = 12

        
    End Select
    
    
    If TargetNumMonth - CurMonth >= 3 Then
        Range("B2").Offset(0, 1).Value = "Not Due Yet"
    ElseIf TargetNumMonth - CurMonth < 3 And TargetNumMonth - CurMonth >= 0 Then
        Range("B1").Offset(0, 1).Value = "Due Soon"
    Else
        Range("B1").Offset(0, 1).Value = "Overdue"
        
    End If
    
    
    

End Sub

This ONLY works for cells A1 (prints result in B1). I'm looking for help on making this work for a given range (such as A1:A10).

Also, i realize that the second switch statement is probably useless (wasnt sure if it was ok to compare strings to ints later in code, so i converted in the switch statement instead.

I tried doing:

Code:

With activesheet.Range("A1:A10")
     for each c in .Cells
         r = Left(Range(ActiveCell).Value, 3)

Of course this wont work as ActiveCell isnt a variable/type, but it's pseudo of how i want to do it. Anyway someone can point me in the right direction on this?

Thanks

Edit:

BTW, The above Sub also only works if A1 consists of text such as "Aug-07". And it must be in format of regular Text, not a date (through Ctrl + 1). I could've just Left("A1", 1) probably instead of convering it to a Text cell then doing Left("A1", 3).

I'm trying to make a lesson planner with text and numbers. I want the text to stay the same in each cell but have the numbers increase by 1 each time. I cannot figure out the formula.

Example:

Cell 1: R&S 1; Cell 2: R&S 2; Cell 3: R&S 3

Thanks, Kelly

Hi I'm new to this forum and am asking for some advise for a spreadsheet for school attendance.

The students have the option to come to a class or not There are approx. 20 different subject classes a day There are 4 periods a day There are approx. 100 students There are about 20 different teachers Problem is a student comes into school goes to a class, skip 2 classes and attend the last. In the event of an emergency there is no way to know who is on site/class
At the end of the term there is no recall of how many lessons or classes attended.

I manage the school database and can extract the classes and class lists, but there is no lesson attendance monitor and this is what I'm after.

I tried to make something in Access, but got lost in the maze.

If anyone can help I would be eternally grateful.

Hi

I've agreed to do a presentation at my mate's school to a class of kids in year 6/7 about work - she's having lots of people come in and talk about their jobs.

I work in finance but wanted to do something involving Excel that will get the kids excited about spreadsheets and give them something that they can apply to stuff they are doing at school. I was thinking of giving them an example spreadsheet with a few formula and some logic based problems for them to tackle.

Anyone got any ideas / suggestions / material for this kind of a tutorial?

Thanks
Nick

Help File is not much help (for me anyway)

Using Excel 2007
I am trying to filter uniques to another location.

My data range is D19:H480, with Labels
Help says to have at least three blank rows above this, which I do and the Labels again at row 15

I go to Data > Advanced
Copy to another location
List Range: D19:H19 automatically appears

Criterior Range: I put in D19:D480
Copy to: I put in K15
Check - Unique records only

I get a message box saying: slect a range of cells that contains at least two rows of data.

What am I doing wrong?

I don't know if i am stating this correctly...with option explicit it forces you to define your variables...

I have heard that this makes your code run faster...how much faster?

Is there a cheat sheet that I can use to identify my variables appropriately...please explain as much as possible...

I have about 50-60 procedures that I run in 6 different modules...does this make my code run slower?

thanks for the help

Lino

Hi All:

How can I change

If Sheets("Input Sheet").CheckBox5 = False Then

To

If Sheets("Input Sheet").Cell W3 = False Then

I want to reference a cell and NOT a checkbox. I am sufre this is pretty simple but I am feeling tha way too...

THANKS,
Mark

Does anyone know where i can find lesson plans for powerpoint and excel? i'm starting private lessons with my first customer this weekend so really need something asap!

thanks!

x

In Lesson 4 in "Grouping Fields" part, in the following paragraph:

" At present, our Names heading is the inner most row field. Lets change that now by left clicking on our Date of Birth field (column heading in C4) and dragging it to the left and dropping it in A4 so that it becomes the inner most row field (left). Now select Group Selection under Group optionson the Options tab, scroll down and highlight the word Year ensuring the you unhighlight Months. "

When we want to change "Date of birth" field, you mention that it is in column heading in C4, but the cell C4 is empty there is no data. The same thing happens for Age field.
Therefore, we can't group by those fields, because we can't do them the inner most row fields.

Hello all,

So per my earlier thread, I'm trying to automate the classification of sales based on a matrix.

I want to make a UDF to go in column 'I' of the Sales sheet, that takes the cell values in columns 'A' to 'F' of each row, and compares to each row in the matrix:

Sales
A B C D E F G H I 1 SH Ctry Product PH 1 PH2 Pkg Customer Sales KG NS GBP ABC 2 GB Apples 10004 90002 PB 12753 900 600 3 GB Pears 10004 90004 PB 12437 450 375 4 IE Lemons 10003 90064 CB 12743 1800 1000

Matrix A B C D E F G 1 ABC SH Ctry Product PH 1 PH2 Pkg Customer 2 A 10004 90002 3 B 90004 4 B FR 5 A IE


I've created the code below which takes an input from the 'Sales' sheet and puts them into an array. It also loads each row from the 'Matrix' into an array.

Its supposed to compare the corresponding fields from 'Sales' with 'Matrix' and in the event that all fields defined in the 'Matrix' match the 'Sales' data, the respective ABC classification is put into a 'Results' array (rArray).

I hope that makes sense. The code is below, but I can't figure out why in the event of a match, the result isn't being put into the rArray...

Code:

Option Explicit
Sub Classify()
' Note: This will ultimately be a Function, not a Sub.
 
' Legend:
' i = Input; m = Matrix; r = Result
 
Dim iArray(), mArray(), rArray()
Dim iRange As Range, iMaxCol As Long, iColCount As Long, iTargetCell As Range
Dim mRange As Range, mMaxRow As Long, mMaxCol As Long, mRowCount As Long, mHitCount As Long, mColCount As Long, mTargetRow As Range, mTargetCell As Range
Dim rLoopCount As Long, rHitCount As Long
 
'Note: In the Function, these will be declared outside of the VBA (sheet refs use codename)
Set iRange = Range(Sales001.Cells(2, 1), Sales001.Cells(2, 6))
Set mRange = Range(Matrix001.Cells(2, 1), Matrix001.Cells(5, 7))
 
iMaxCol = iRange.Columns.Count
mMaxRow = mRange.Rows.Count
mMaxCol = mRange.Columns.Count
 
If Not iMaxCol + 1 = mMaxCol Then
    MsgBox "Incompatible input and matrix ranges.", vbCritical, "Error"
    GoTo Finish
End If
 
'Loads Input Array
ReDim iArray(iMaxCol)
iColCount = 0
 
For Each iTargetCell In iRange.Cells
    iColCount = iColCount + 1
    iArray(iColCount) = iTargetCell.Value
Next iTargetCell
 
'Loads Matrix Array
ReDim mArray(mMaxCol)
mRowCount = 0
 
For mRowCount = 1 To mMaxRow
    Set mTargetRow = mRange.Rows(mRowCount)
    mColCount = 0
    mHitCount = -1
    For Each mTargetCell In mTargetRow.Cells
        mColCount = mColCount + 1
        mArray(mColCount) = mTargetCell.Value
        If Not mArray(mColCount) = 0 Then
            mHitCount = mHitCount + 1
        End If
    Next mTargetCell
 
    'Evaluate Input & Matrix Arrays and Load Result Array
    ReDim rArray(mMaxRow)
    rLoopCount = 0
    rHitCount = 0
 
    For rLoopCount = 1 To iMaxCol
        If iArray(rLoopCount) = mArray(rLoopCount + 1) Then
            rHitCount = rHitCount + 1
        End If
    Next rLoopCount
 
    If rHitCount = mHitCount Then
        rArray(mRowCount - 1) = mArray(1)
    End If
 
Next mRowCount
 
MsgBox rArray(1)  '