Macro running but populaing wrong cells

[Jamyhamy]

H, I have the macro below that populates column v with a "1" if the value in the corresponding cell in column J is above 1 based on using the select case. But when I run the macro it pppulates the results into the wrong row number, i.e 1 row down. So for example if j3 is greater than 1, it will populate the answer (1) in V4 instead of V3. Similar for all other rows.Anyone know why this is happening? It's really bugging me. Tried adding option base 0 at the top of the module but makes no difference. Hope someone can help.Thanks,JH

Sub populate()
lastrow = Range("A3").End(xlDown).Row
For i = 3 To lastrow
Source = Range("J" & i).Value
Cells(i, 22) = Deal
Select Case Source
Case Is >= 1
Deal = 1
Case Is < 1
Deal = ""
End Select
Next i
End Sub

[JKwan]

try this


Sub populate()
lastrow = Range("A3").End(xlDown).Row
For i = 3 To lastrow
Source = Range("J" & i).Value
Select Case Source
Case Is >= 1
Range("V" & i).Value = 1

Case Is < 1
Range("V" & i).Value = ""
End Select
Next i
End Sub

[david000]

Cells(i, 22) = Deal < The variable is empty on the first run, so your getting a result at V4 instead of V3.


Sub populate()
Dim i As Integer
Dim Lastrow As Long

Lastrow = Range("A3").End(xlDown).Row

For i = 3 To Lastrow

Select Case Cells(i, 10).Value
Case Is >= 1: Cells(i, 22) = 1
Case Is < 1: Cells(i, 22) = ""
End Select

Next i
End Sub

[david000]

I just took another look at this thread and noticed that if you just declared the variable Deal as a Range Object and Set it each iteration it works just the way it is.




Sub populate()
Dim Source
Dim Deal As Range 'Range object

Lastrow = Range("A3").End(xlDown).Row
For i = 3 To Lastrow
ReDim Source(1 To Lastrow)
Source = Range("J" & i).Value

Set Deal = Cells(i, 22) 'Use Set

Select Case Source
Case Is >= 1: Deal = 1
Case Is < 1: Deal = ""
End Select
Next i
'
End Sub

[Jamyhamy]

Thanks gents, I will test these out and let you know if there are any issues.

[snb]

This suffices:

Sub M_snb()
For i = 3 To Range("A3").End(xlDown).Row
if cells(i,10)>0 then cells(i,22)=1
next
End Sub