| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| FInd common data in one column then add number in adjacent column | Excel Worksheet Functions | |||
| compare data in column A with column B to find duplicates | Excel Discussion (Misc queries) | |||
| find the data of the corresponding column | New Users to Excel | |||
| Find something in column a then find if column B matches criteria | Excel Discussion (Misc queries) | |||
| Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
Hybrid Mode
