PDA

View Full Version : Problems with foreach loop in a modul



progfrog
07-18-2010, 11:59 AM
Hi guys!!

I do not understand what is wrong with this loop - the debugr doesn't

umsg- get's a simple string and look gor it in column e

help,,,

Sub test_match(umsg)


Dim value1 As String
Dim secondChoise As String

Dim cell As Range
Dim c As Range
Dim LastRow As Long
Dim LastCell As String
Dim LastRange As Range



'gets user selection after clicking the button but should take it from the loop on the list



With ActiveSheet

LastRow = .Rows(.Rows.Count).Row 'gets last row in the sheet

LastCell = "E" & LastRow 'gets last cell


For Each c In Worksheets("Sheet2").Range("E2 : E17").Cells
rowvalue = c.Value
If rowvalue = umsg Then
secondChoise = .Cells(c.Row, "B").Value
Worksheets("Settings").Columns(1).Cells(c.Row) = secondChoise

End If
Next c




End With

End Sub

The problem is that there are some cells values in column E that are equel to the string in variable umsg and you can see it throught the loop iteration
So why does the condition doesn't work and print out the results in setting sheet?

thanx!!

mikerickson
07-18-2010, 12:03 PM
Have you tried
Worksheets("Settings").Cells(c.Row,1) = secondChoise

p45cal
07-18-2010, 04:39 PM
perhaps because Sheet2 is not the ActiveSheet?

You look for the last cell in Column E of the Activesheet, run through cells in column E of Sheet2, and then assign secondChoise from a cell in the Active sheet (secondChoise = .Cells(c.Row, "B").Value) then put the results in the Settings Sheet.
I feel fairly sure that you're mixing up your sheets.