Find data in Column D, add L in Column A
 

Hello All,
I have a sheet that in Column A the default value will be
L for those rows that contain information. I'm creating a
macro so would like to put a code in there that will put
an L in Column A if there is data in Column D.
Example
Column A ColumnD
L MICROTEST
L LECTRATEST
L SAMPLE
Since total rows will change on a monthly basis I can't
have a specific range.

Would appreciate the help.
Thanks,
Juan

Find data in Column D, add L in Column A
 

Hi
try the following macro:
Sub process_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
For row_index = 1 to lastrow
If Cells(row_index, 4).Value <"" and _
cells(row_index,1).value="" then
cells(row_index,1).value="L"
End If
Next
Application.ScreenUpdating = True
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany

"juan" schrieb im Newsbeitrag
...
Hello All,
I have a sheet that in Column A the default value will be
L for those rows that contain information. I'm creating a
macro so would like to put a code in there that will put
an L in Column A if there is data in Column D.
Example
Column A ColumnD
L MICROTEST
L LECTRATEST
L SAMPLE
Since total rows will change on a monthly basis I can't
have a specific range.

Would appreciate the help.
Thanks,
Juan

Find data in Column D, add L in Column A
 

Hello Frank,
this is great seems to be working as a charm. One last
thing. At end of last record entered, I put a summary(
Represents an S) and total of how many records it contains.
Example,
L TEST
L TEST2
S2
Is there w way I can put S in Column A after last record?
and in Column B the total count of records? I can do it
manually but since doing a macro trying to have less
manual input.
Would appreciate if this can be done. But THANK YOU So
Much for getting back to me this fast YOu have helped me
in the past and Really Appreciate it ALOT. THANKS,
JUAN
-----Original Message-----
Hi
try the following macro:
Sub process_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
For row_index = 1 to lastrow
If Cells(row_index, 4).Value <"" and _
cells(row_index,1).value="" then
cells(row_index,1).value="L"
End If
Next
Application.ScreenUpdating = True
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany

"juan" schrieb im Newsbeitrag
...
Hello All,
I have a sheet that in Column A the default value will
be
L for those rows that contain information. I'm creating
a
macro so would like to put a code in there that will put
an L in Column A if there is data in Column D.
Example
Column A ColumnD
L MICROTEST
L LECTRATEST
L SAMPLE
Since total rows will change on a monthly basis I can't
have a specific range.

Would appreciate the help.
Thanks,
Juan

.

Find data in Column D, add L in Column A
 

Hi
try:
Sub process_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
For row_index = 1 to lastrow
If Cells(row_index, 4).Value <"" and _
cells(row_index,1).value="" then
cells(row_index,1).value="L"
End If
Next
with cells(lastrow+1,1)
.value="S"
.offset(0,1).formulaR1C1="=COUNTIF(R1C1:R[-1]C1,""L"")"
end with
Application.ScreenUpdating = True
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

"Juan" schrieb im Newsbeitrag
...
Hello Frank,
this is great seems to be working as a charm. One last
thing. At end of last record entered, I put a summary(
Represents an S) and total of how many records it contains.
Example,
L TEST
L TEST2
S2
Is there w way I can put S in Column A after last record?
and in Column B the total count of records? I can do it
manually but since doing a macro trying to have less
manual input.
Would appreciate if this can be done. But THANK YOU So
Much for getting back to me this fast YOu have helped me
in the past and Really Appreciate it ALOT. THANKS,
JUAN
-----Original Message-----
Hi
try the following macro:
Sub process_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
For row_index = 1 to lastrow
If Cells(row_index, 4).Value <"" and _
cells(row_index,1).value="" then
cells(row_index,1).value="L"
End If
Next
Application.ScreenUpdating = True
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany

"juan" schrieb im Newsbeitrag
...
Hello All,
I have a sheet that in Column A the default value will
be
L for those rows that contain information. I'm creating
a
macro so would like to put a code in there that will put
an L in Column A if there is data in Column D.
Example
Column A ColumnD
L MICROTEST
L LECTRATEST
L SAMPLE
Since total rows will change on a monthly basis I can't
have a specific range.

Would appreciate the help.
Thanks,
Juan

.

Find data in Column D, add L in Column A
 

Hello Frank,
thanks for the extra help. FYI, the formula doesn't seem
to be working. It just puts in Column B
=COUNTIF(R1C1:R[-1]C1,"L")
I'll play around with this and see if I can make it to
work. If not I'll just have users manually put the total
records.

I really appreciate all your help.
thanks again,

juan
-----Original Message-----
Hi
try:
Sub process_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
For row_index = 1 to lastrow
If Cells(row_index, 4).Value <"" and _
cells(row_index,1).value="" then
cells(row_index,1).value="L"
End If
Next
with cells(lastrow+1,1)
.value="S"
.offset(0,1).formulaR1C1="=COUNTIF(R1C1:R[-1]
C1,""L"")"
end with
Application.ScreenUpdating = True
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

"Juan" schrieb im Newsbeitrag
...
Hello Frank,
this is great seems to be working as a charm. One last
thing. At end of last record entered, I put a summary(
Represents an S) and total of how many records it
contains.
Example,
L TEST
L TEST2
S2
Is there w way I can put S in Column A after last
record?
and in Column B the total count of records? I can do it
manually but since doing a macro trying to have less
manual input.
Would appreciate if this can be done. But THANK YOU So
Much for getting back to me this fast YOu have helped me
in the past and Really Appreciate it ALOT. THANKS,
JUAN
-----Original Message-----
Hi
try the following macro:
Sub process_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, 4).End
(xlUp).Row
For row_index = 1 to lastrow
If Cells(row_index, 4).Value <"" and _
cells(row_index,1).value="" then
cells(row_index,1).value="L"
End If
Next
Application.ScreenUpdating = True
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany

"juan" schrieb im Newsbeitrag
...
Hello All,
I have a sheet that in Column A the default value
will
be
L for those rows that contain information. I'm
creating
a
macro so would like to put a code in there that will
put
an L in Column A if there is data in Column D.
Example
Column A ColumnD
L MICROTEST
L LECTRATEST
L SAMPLE
Since total rows will change on a monthly basis I
can't
have a specific range.

Would appreciate the help.
Thanks,
Juan

.


.

Find data in Column D, add L in Column A
 

Hi
try changing the format of this column first to 'General'. Maybe the
cell is formated as 'Text'

--
Regards
Frank Kabel
Frankfurt, Germany

"juan" schrieb im Newsbeitrag
...
Hello Frank,
thanks for the extra help. FYI, the formula doesn't seem
to be working. It just puts in Column B
=COUNTIF(R1C1:R[-1]C1,"L")
I'll play around with this and see if I can make it to
work. If not I'll just have users manually put the total
records.

I really appreciate all your help.
thanks again,

juan
-----Original Message-----
Hi
try:
Sub process_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
For row_index = 1 to lastrow
If Cells(row_index, 4).Value <"" and _
cells(row_index,1).value="" then
cells(row_index,1).value="L"
End If
Next
with cells(lastrow+1,1)
.value="S"
.offset(0,1).formulaR1C1="=COUNTIF(R1C1:R[-1]
C1,""L"")"
end with
Application.ScreenUpdating = True
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

"Juan" schrieb im Newsbeitrag
...
Hello Frank,
this is great seems to be working as a charm. One last
thing. At end of last record entered, I put a summary(
Represents an S) and total of how many records it
contains.
Example,
L TEST
L TEST2
S2
Is there w way I can put S in Column A after last
record?
and in Column B the total count of records? I can do it
manually but since doing a macro trying to have less
manual input.
Would appreciate if this can be done. But THANK YOU So
Much for getting back to me this fast YOu have helped me
in the past and Really Appreciate it ALOT. THANKS,
JUAN
-----Original Message-----
Hi
try the following macro:
Sub process_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, 4).End
(xlUp).Row
For row_index = 1 to lastrow
If Cells(row_index, 4).Value <"" and _
cells(row_index,1).value="" then
cells(row_index,1).value="L"
End If
Next
Application.ScreenUpdating = True
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany

"juan" schrieb im Newsbeitrag
...
Hello All,
I have a sheet that in Column A the default value
will
be
L for those rows that contain information. I'm
creating
a
macro so would like to put a code in there that will
put
an L in Column A if there is data in Column D.
Example
Column A ColumnD
L MICROTEST
L LECTRATEST
L SAMPLE
Since total rows will change on a monthly basis I
can't
have a specific range.

Would appreciate the help.
Thanks,
Juan

.


.

Find data in Column D, add L in Column A
 

Hello Frank,
getting close LOL. I did change the column to General and
run the macro and it puts #NAME? but if I go into the cell
and put cursor at end of formula and then hit enter, it
gives me the right number. So there as to be something
minor wrong in the macro code? Here's what I have:
Sub ADDRECINFO()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
For row_index = 1 To lastrow
If Cells(row_index, 4).Value < "" And _
Cells(row_index, 1).Value = "" Then
Cells(row_index, 1).Value = "L"
Cells(row_index, 2).Value = "MNLA"
Cells(row_index, 7).Value = "PH"
Cells(row_index, 14).Value = "USD"
End If
Next
With Cells(lastrow + 1, 1)
.Value = "S"
.Offset(0, 1).FormulaR1C1 = "=COUNTIF(A:A,""L"")"
End With
Application.ScreenUpdating = True
End Sub

This is part of the complete macro I'm doing.
Thanks for the help
juan
-----Original Message-----
Hi
try changing the format of this column first
to 'General'. Maybe the
cell is formated as 'Text'

--
Regards
Frank Kabel
Frankfurt, Germany

"juan" schrieb im Newsbeitrag
...
Hello Frank,
thanks for the extra help. FYI, the formula doesn't seem
to be working. It just puts in Column B
=COUNTIF(R1C1:R[-1]C1,"L")
I'll play around with this and see if I can make it to
work. If not I'll just have users manually put the total
records.

I really appreciate all your help.
thanks again,

juan
-----Original Message-----
Hi
try:
Sub process_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, 4).End
(xlUp).Row
For row_index = 1 to lastrow
If Cells(row_index, 4).Value <"" and _
cells(row_index,1).value="" then
cells(row_index,1).value="L"
End If
Next
with cells(lastrow+1,1)
.value="S"
.offset(0,1).formulaR1C1="=COUNTIF(R1C1:R[-1]
C1,""L"")"
end with
Application.ScreenUpdating = True
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

"Juan" schrieb im Newsbeitrag
...
Hello Frank,
this is great seems to be working as a charm. One
last
thing. At end of last record entered, I put a summary
(
Represents an S) and total of how many records it
contains.
Example,
L TEST
L TEST2
S2
Is there w way I can put S in Column A after last
record?
and in Column B the total count of records? I can do
it
manually but since doing a macro trying to have less
manual input.
Would appreciate if this can be done. But THANK YOU
So
Much for getting back to me this fast YOu have
helped me
in the past and Really Appreciate it ALOT. THANKS,
JUAN
-----Original Message-----
Hi
try the following macro:
Sub process_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, 4).End
(xlUp).Row
For row_index = 1 to lastrow
If Cells(row_index, 4).Value <"" and _
cells(row_index,1).value="" then
cells(row_index,1).value="L"
End If
Next
Application.ScreenUpdating = True
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany

"juan" schrieb im
Newsbeitrag
...
Hello All,
I have a sheet that in Column A the default value
will
be
L for those rows that contain information. I'm
creating
a
macro so would like to put a code in there that
will
put
an L in Column A if there is data in Column D.
Example
Column A ColumnD
L MICROTEST
L LECTRATEST
L SAMPLE
Since total rows will change on a monthly basis I
can't
have a specific range.

Would appreciate the help.
Thanks,
Juan

.


.


.

Find data in Column D, add L in Column A
 

Hello Frank,
just wanted to let you know, I figured it out and now it
seems to work. So thank you once again for all your great
help.

Have a good one.

Juan
-----Original Message-----
Hi
try changing the format of this column first
to 'General'. Maybe the
cell is formated as 'Text'

--
Regards
Frank Kabel
Frankfurt, Germany

"juan" schrieb im Newsbeitrag
...
Hello Frank,
thanks for the extra help. FYI, the formula doesn't seem
to be working. It just puts in Column B
=COUNTIF(R1C1:R[-1]C1,"L")
I'll play around with this and see if I can make it to
work. If not I'll just have users manually put the total
records.

I really appreciate all your help.
thanks again,

juan
-----Original Message-----
Hi
try:
Sub process_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, 4).End
(xlUp).Row
For row_index = 1 to lastrow
If Cells(row_index, 4).Value <"" and _
cells(row_index,1).value="" then
cells(row_index,1).value="L"
End If
Next
with cells(lastrow+1,1)
.value="S"
.offset(0,1).formulaR1C1="=COUNTIF(R1C1:R[-1]
C1,""L"")"
end with
Application.ScreenUpdating = True
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

"Juan" schrieb im Newsbeitrag
...
Hello Frank,
this is great seems to be working as a charm. One
last
thing. At end of last record entered, I put a summary
(
Represents an S) and total of how many records it
contains.
Example,
L TEST
L TEST2
S2
Is there w way I can put S in Column A after last
record?
and in Column B the total count of records? I can do
it
manually but since doing a macro trying to have less
manual input.
Would appreciate if this can be done. But THANK YOU
So
Much for getting back to me this fast YOu have
helped me
in the past and Really Appreciate it ALOT. THANKS,
JUAN
-----Original Message-----
Hi
try the following macro:
Sub process_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, 4).End
(xlUp).Row
For row_index = 1 to lastrow
If Cells(row_index, 4).Value <"" and _
cells(row_index,1).value="" then
cells(row_index,1).value="L"
End If
Next
Application.ScreenUpdating = True
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany

"juan" schrieb im
Newsbeitrag
...
Hello All,
I have a sheet that in Column A the default value
will
be
L for those rows that contain information. I'm
creating
a
macro so would like to put a code in there that
will
put
an L in Column A if there is data in Column D.
Example
Column A ColumnD
L MICROTEST
L LECTRATEST
L SAMPLE
Since total rows will change on a monthly basis I
can't
have a specific range.

Would appreciate the help.
Thanks,
Juan

.


.


.