If with Loop

[hashim217]

Hi all,

I need set the value of a cell based on the values of 2 other cells in the same row. Then I need to loop this to do it for all the rows with data. In my code I put ranges D2 and L2 but I need it to go down for each next cell in column A. I hope this makes sense. Any advice would be appreciated.

Regards,

Hash

Sub MM_Conv()
 
Dim RowCount As String
Dim Cell As Range
 
RowCount = WorksheetFunction.CountA(Columns("A:A"))
 
For Each Cell In Range("A2:A" & RowCount)
    If Range("D2").Value = "LONG" & Range("L2").Value = "C" Then
        Cell.Value = "B"
    End If
Next Cell
 
End Sub

 

[VoG]

Try

Sub MM_Conv()
 
Dim RowCount As Long
Dim Cell As Range
 
RowCount = WorksheetFunction.CountA(Columns("A:A"))
 
For Each Cell In Range("A2:A" & RowCount)
    If Cell.Offset(, 3).Value = "LONG" & Cell.Offset(, 11).Value = "C" Then
        Cell.Value = "B"
    End If
Next Cell
 
End Sub

 

[hashim217]

Hi,

When I ran the macro with your code it did not give me an error but the cells are all blank. I am not exactly sure why.

-Hash

 

[VoG]

Sorry, I didn't spot this error in your code

Sub MM_Conv()
 
Dim RowCount As Long
Dim Cell As Range
 
RowCount = WorksheetFunction.CountA(Columns("A:A"))
 
For Each Cell In Range("A2:A" & RowCount)
    If Cell.Offset(, 3).Value = "LONG" And Cell.Offset(, 11).Value = "C" Then
        Cell.Value = "B"
    End If
Next Cell
 
End Sub