VB in Excel Help please

[swoozie]

Okay, I am stuck again.

I am tring to write a loop that goes through a selected area of a spreadsheet looking for a specified value. then once the specified value is located, I want it to copy the record and insert it into a different spreadsheet. My lowly intelect has me stuck on ....

1) How to make it loop through the cells

2)how to get the darn Select case statement to work.


I did the case statememt first based on one cell, and it doesn't seem to be recognizing it. (LastCol was determined earlier in the code) I only used A2 to see if I could get the code to select that specified row.

Dim CellAge as Integer

Select Case CellAge
Case Is = 0
MsgBox ("Value of CellAge is" & CellAge)
ActiveSheet.Range("a2:" & LastCol).Address.Select
Case Is = 1
MsgBox ("Value of CellAge is" & CellAge)
End Select

Thanks,

Swoozie

[InvisibleDuncan]

Dim rngCell As Range
Dim rngRange As Range
 
For Each rngCell In rngRange
    
    Select Case rngCell.Value
        Case 1
            '# Do whatever
        Case 2
            '# Do whatever else
    End Select
        
Next rngCell

Obviously, you'll need to set rngRange to whatever your range needs to be.

[Si_the_geek]

it might help to use:
sheetname.usedrange.rows.count
and
sheetname.usedrange.columns.count

..which give you the used range in the sheet

[swoozie]

Okay, I did the above and altered the code for my stuff. but it isntworking, I had it working then it just like stopped and gets skipped over.

'Looks for the Age of a record and inserts it into the Template


Set RngCell = ThisWorkbook.Worksheets("RFC").Range("A2")

Set RngRange = ("a2:" & _
ActiveSheet.Cells(LastRow2, LastCol2).Address)

MsgBox ("Range=" & RngRange) 'Testing if this gets recognized

For Each RngCell In RngRange

Select Case RngCell.Value
Case 0
MsgBox ("Value is" & RngCell)
Case 1
MsgBox ("Value is" & RngCell)
Case 2
MsgBox ("Value is" & RngCell)
Case 3
MsgBox ("Value is" & RngCell)
Case 4
MsgBox ("Value is" & RngCell)
Case Is >= 5
MsgBox ("Value is" & RngCell)
End Select

Next RngCell

[swoozie]

you can also add this to the list of problems I need assistance with. (Excel stuff is just plain weird)

I need to send one email with 8 attachments to a specified mailing list immediately after running all of my previous "macros" to create the reports. (Of which everyone has been helpful with)

I do not know how to insert the attachments. Nor do I know how to fillout the subject line, the To: and the cc: and the body. All in code. I have several different examples but none deal with it from with in Excel.

help me please!!!I am drowning in a pool of my own stupidity.

[Si_the_geek]

That worked fine for me (except I used 'UsedRange' instead of RngRange), are you sure the LastRow2 and LastCol2 variables are set?

By the way, you don't need this line:
Set RngCell = ThisWorkbook.Worksheets("RFC").Range("A2")
(the 'for each' automatically creates it each time around the loop from the range)


Going back to your original question though, you dont need the select statement if you are just looking for a particular value, its only useful if you want to perform different actions depending on which range a variable fits into.

try this instead:

set RngRange = Usedrange '(or what you had before!)
search_value = 1     '(set this to what you are looking for)
 
For Each RngCell In RngRange    'for each cell...
  if RngCell.Value = search_value then  'look for value..
    '< do your copy bit here >              '..found it, so do something
    
    exit for             'to exit the loop (if you only want to find the first occurence)
  end if
Next RngCell

I can't remember how to do the mail from Excel (I know there's two methods of doing it, one which just sends the workbook, and one which does a proper mail). My users all have Outlook installed, which allows more functionality...