Dear all,

Could anyone help how do I loop to check cells of column A if there is not
empty, then add 1 to cell of column D and stop until the cell of column A is
empty?

Thanks for your help.

tlee

for rw = 1 to range("A65500").End(xlUp).Row
if cells(rw,1)="" then cells(rw,"D")= cells(rw,"D") + 1
next

"tlee" <(E-Mail Removed)> wrote in message
news:#rBEgXJ#(E-Mail Removed)...
> Dear all,
>
> Could anyone help how do I loop to check cells of column A if there is not
> empty, then add 1 to cell of column D and stop until the cell of column A
> is empty?
>
> Thanks for your help.
>
> tlee
>
>

Hi Patrick,

Thanks for your message first.

But, it is not respond. Can help again?

tlee

> for rw = 1 to range("A65500").End(xlUp).Row
> if cells(rw,1)="" then cells(rw,"D")= cells(rw,"D") + 1
> next
>
> "tlee" <(E-Mail Removed)> wrote in message
> news:#rBEgXJ#(E-Mail Removed)...
>> Dear all,
>>
>> Could anyone help how do I loop to check cells of column A if there is
>> not empty, then add 1 to cell of column D and stop until the cell of
>> column A is empty?
>>
>> Thanks for your help.
>>
>> tlee
>>
>>

Maybe this

Sub Marine()
Dim MyRange As Range
Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & Lastrow)
For Each c In MyRange
If c.Value <> "" Then
c.Offset(, 3).Value = c.Offset(, 3).Value + 1
End If
Next
End Sub

Mike

"tlee" wrote:

> Dear all,
>
> Could anyone help how do I loop to check cells of column A if there is not
> empty, then add 1 to cell of column D and stop until the cell of column A is
> empty?
>
> Thanks for your help.
>
> tlee
>
>
>

1.go to the development environment - press and hold ALT then press F11
2. from the menu click "INSERT" then "MODULE"
3. type
SUB DEMO
4. paste into the sub the code i gave to you
5. EITHER
(a) press F5
OR
(b) go back to the work sheet. select Tools / Macro /Macros , select
Demo from the list and click "Run"




"tlee" <(E-Mail Removed)> wrote in message
news:u$j743J%(E-Mail Removed)...
> Hi Patrick,
>
> Thanks for your message first.
>
> But, it is not respond. Can help again?
>
> tlee
>
>> for rw = 1 to range("A65500").End(xlUp).Row
>> if cells(rw,1)="" then cells(rw,"D")= cells(rw,"D") + 1
>> next
>>
>> "tlee" <(E-Mail Removed)> wrote in message
>> news:#rBEgXJ#(E-Mail Removed)...
>>> Dear all,
>>>
>>> Could anyone help how do I loop to check cells of column A if there is
>>> not empty, then add 1 to cell of column D and stop until the cell of
>>> column A is empty?
>>>
>>> Thanks for your help.
>>>
>>> tlee
>>>
>>>

Hi Partick,

Thanks for you help again!!!

It is because it takes time for check the column A. As the result, I cannot
see the result immediately.
So, I changed End(xlup) to End(xldown).

Likes:
for rw = 1 to range("A65500").End(xldown).Row
if cells(rw,1)="" then cells(rw,"D")= cells(rw,"D") + 1
next

Then I can see the action promptly.

Thanks
tlee


> 1.go to the development environment - press and hold ALT then press F11
> 2. from the menu click "INSERT" then "MODULE"
> 3. type
> SUB DEMO
> 4. paste into the sub the code i gave to you
> 5. EITHER
> (a) press F5
> OR
> (b) go back to the work sheet. select Tools / Macro /Macros , select
> Demo from the list and click "Run"
>
>
>
>
> "tlee" <(E-Mail Removed)> wrote in message
> news:u$j743J%(E-Mail Removed)...
>> Hi Patrick,
>>
>> Thanks for your message first.
>>
>> But, it is not respond. Can help again?
>>
>> tlee
>>
>>> for rw = 1 to range("A65500").End(xlUp).Row
>>> if cells(rw,1)="" then cells(rw,"D")= cells(rw,"D") + 1
>>> next
>>>
>>> "tlee" <(E-Mail Removed)> wrote in message
>>> news:#rBEgXJ#(E-Mail Removed)...
>>>> Dear all,
>>>>
>>>> Could anyone help how do I loop to check cells of column A if there is
>>>> not empty, then add 1 to cell of column D and stop until the cell of
>>>> column A is empty?
>>>>
>>>> Thanks for your help.
>>>>
>>>> tlee
>>>>
>>>>
>

Hi Mike,

Thanks for your message and help !!!

tlee


> Maybe this
>
> Sub Marine()
> Dim MyRange As Range
> Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
> Set MyRange = Range("A1:A" & Lastrow)
> For Each c In MyRange
> If c.Value <> "" Then
> c.Offset(, 3).Value = c.Offset(, 3).Value + 1
> End If
> Next
> End Sub
>
> Mike
>
> "tlee" wrote:
>
>> Dear all,
>>
>> Could anyone help how do I loop to check cells of column A if there is
>> not
>> empty, then add 1 to cell of column D and stop until the cell of column A
>> is
>> empty?
>>
>> Thanks for your help.
>>
>> tlee
>>
>>
>>

but xlDown stops at the first blank cell in A. I deliberately used xlUp to
cover all blanks

"tlee" <(E-Mail Removed)> wrote in message
news:u35uwxN#(E-Mail Removed)...
> Hi Partick,
>
> Thanks for you help again!!!
>
> It is because it takes time for check the column A. As the result, I
> cannot see the result immediately.
> So, I changed End(xlup) to End(xldown).
>
> Likes:
> for rw = 1 to range("A65500").End(xldown).Row
> if cells(rw,1)="" then cells(rw,"D")= cells(rw,"D") + 1
> next
>
> Then I can see the action promptly.
>
> Thanks
> tlee
>
>
>> 1.go to the development environment - press and hold ALT then press F11
>> 2. from the menu click "INSERT" then "MODULE"
>> 3. type
>> SUB DEMO
>> 4. paste into the sub the code i gave to you
>> 5. EITHER
>> (a) press F5
>> OR
>> (b) go back to the work sheet. select Tools / Macro /Macros , select
>> Demo from the list and click "Run"
>>
>>
>>
>>
>> "tlee" <(E-Mail Removed)> wrote in message
>> news:u$j743J%(E-Mail Removed)...
>>> Hi Patrick,
>>>
>>> Thanks for your message first.
>>>
>>> But, it is not respond. Can help again?
>>>
>>> tlee
>>>
>>>> for rw = 1 to range("A65500").End(xlUp).Row
>>>> if cells(rw,1)="" then cells(rw,"D")= cells(rw,"D") + 1
>>>> next
>>>>
>>>> "tlee" <(E-Mail Removed)> wrote in message
>>>> news:#rBEgXJ#(E-Mail Removed)...
>>>>> Dear all,
>>>>>
>>>>> Could anyone help how do I loop to check cells of column A if there is
>>>>> not empty, then add 1 to cell of column D and stop until the cell of
>>>>> column A is empty?
>>>>>
>>>>> Thanks for your help.
>>>>>
>>>>> tlee
>>>>>
>>>>>
>>
>
>

Hi Partick,

Thanks for your recommendation.

Besides, how do it check the cell of column A when it is blank, it will
delete the row at that cell?
I think as you said it is better to use xlUP.

tlee


> but xlDown stops at the first blank cell in A. I deliberately used xlUp to
> cover all blanks
>
> "tlee" <(E-Mail Removed)> wrote in message
> news:u35uwxN#(E-Mail Removed)...
>> Hi Partick,
>>
>> Thanks for you help again!!!
>>
>> It is because it takes time for check the column A. As the result, I
>> cannot see the result immediately.
>> So, I changed End(xlup) to End(xldown).
>>
>> Likes:
>> for rw = 1 to range("A65500").End(xldown).Row
>> if cells(rw,1)="" then cells(rw,"D")= cells(rw,"D") + 1
>> next
>>
>> Then I can see the action promptly.
>>
>> Thanks
>> tlee
>>
>>
>>> 1.go to the development environment - press and hold ALT then press F11
>>> 2. from the menu click "INSERT" then "MODULE"
>>> 3. type
>>> SUB DEMO
>>> 4. paste into the sub the code i gave to you
>>> 5. EITHER
>>> (a) press F5
>>> OR
>>> (b) go back to the work sheet. select Tools / Macro /Macros , select
>>> Demo from the list and click "Run"
>>>
>>>
>>>
>>>
>>> "tlee" <(E-Mail Removed)> wrote in message
>>> news:u$j743J%(E-Mail Removed)...
>>>> Hi Patrick,
>>>>
>>>> Thanks for your message first.
>>>>
>>>> But, it is not respond. Can help again?
>>>>
>>>> tlee
>>>>
>>>>> for rw = 1 to range("A65500").End(xlUp).Row
>>>>> if cells(rw,1)="" then cells(rw,"D")= cells(rw,"D") + 1
>>>>> next
>>>>>
>>>>> "tlee" <(E-Mail Removed)> wrote in message
>>>>> news:#rBEgXJ#(E-Mail Removed)...
>>>>>> Dear all,
>>>>>>
>>>>>> Could anyone help how do I loop to check cells of column A if there
>>>>>> is not empty, then add 1 to cell of column D and stop until the cell
>>>>>> of column A is empty?
>>>>>>
>>>>>> Thanks for your help.
>>>>>>
>>>>>> tlee
>>>>>>
>>>>>>
>>>
>>
>>

-----------------------------------------------------------------------------
Less Spam Better enjoyable experience
Visit : news://spacesst.com

the object was to check all cells in column A and put 1 into D for any row
where there was nothing in A.

my code did that.

alternative method:-

dim nn as long
nn = range("A65000").End(xlUp).Row
WITH Range("D2" & nn )
.FormulaR1C1="=IF(RC1="""",1,"""")"
.Calculate
.Value = .Value
End With




"tlee" <(E-Mail Removed)> wrote in message
news:#T2OoqS#(E-Mail Removed)...
> Hi Partick,
>
> Thanks for your recommendation.
>
> Besides, how do it check the cell of column A when it is blank, it will
> delete the row at that cell?
> I think as you said it is better to use xlUP.
>
> tlee
>
>
>> but xlDown stops at the first blank cell in A. I deliberately used xlUp
>> to cover all blanks
>>
>> "tlee" <(E-Mail Removed)> wrote in message
>> news:u35uwxN#(E-Mail Removed)...
>>> Hi Partick,
>>>
>>> Thanks for you help again!!!
>>>
>>> It is because it takes time for check the column A. As the result, I
>>> cannot see the result immediately.
>>> So, I changed End(xlup) to End(xldown).
>>>
>>> Likes:
>>> for rw = 1 to range("A65500").End(xldown).Row
>>> if cells(rw,1)="" then cells(rw,"D")= cells(rw,"D") + 1
>>> next
>>>
>>> Then I can see the action promptly.
>>>
>>> Thanks
>>> tlee
>>>
>>>
>>>> 1.go to the development environment - press and hold ALT then press F11
>>>> 2. from the menu click "INSERT" then "MODULE"
>>>> 3. type
>>>> SUB DEMO
>>>> 4. paste into the sub the code i gave to you
>>>> 5. EITHER
>>>> (a) press F5
>>>> OR
>>>> (b) go back to the work sheet. select Tools / Macro /Macros , select
>>>> Demo from the list and click "Run"
>>>>
>>>>
>>>>
>>>>
>>>> "tlee" <(E-Mail Removed)> wrote in message
>>>> news:u$j743J%(E-Mail Removed)...
>>>>> Hi Patrick,
>>>>>
>>>>> Thanks for your message first.
>>>>>
>>>>> But, it is not respond. Can help again?
>>>>>
>>>>> tlee
>>>>>
>>>>>> for rw = 1 to range("A65500").End(xlUp).Row
>>>>>> if cells(rw,1)="" then cells(rw,"D")= cells(rw,"D") + 1
>>>>>> next
>>>>>>
>>>>>> "tlee" <(E-Mail Removed)> wrote in message
>>>>>> news:#rBEgXJ#(E-Mail Removed)...
>>>>>>> Dear all,
>>>>>>>
>>>>>>> Could anyone help how do I loop to check cells of column A if there
>>>>>>> is not empty, then add 1 to cell of column D and stop until the cell
>>>>>>> of column A is empty?
>>>>>>>
>>>>>>> Thanks for your help.
>>>>>>>
>>>>>>> tlee
>>>>>>>
>>>>>>>
>>>>
>>>
>>>